postgresql - How to write a function that returns text or integer values?

Question

I'm using PostgreSQL 9.2.4.

postgres=# select version();

                           version
-------------------------------------------------------------
 PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
(1 row)

sqlfiddle link

My Query executes the insertion safely. What i need is that my function should return something except the void datatype. Something like text("inserted into table") or integer(0-false,1-true) , it will be useful for me to validate whether it is inserted or not?

I need a syntax for a function that returns an integer or a text when an insertion is done. For validation purpose. Is there any way to solve this?

Answer 1

What you probably need

Most likely you need one function to return text and another one to return integer or a function that returns boolean to indicate success. All of this is trivial and I'll refer you to the excellent manual on CREATE FUNCTION or code examples in similar questions on SO.

What you actually asked

How to write a function that returns text or integer values?

... in the sense that we have one return type being either text or integer. Not as trivial, but also not impossible as has been suggested. The key word is: polymorphic types.

Building on this simple table:

CREATE TABLE tbl(
  tbl_id int,
  txt    text,
  nr     int
);

This function returns either integer or text (or any other type if you allow it), depending on the input type.

CREATE FUNCTION f_insert_data(_id int, _data anyelement, OUT _result anyelement)
  RETURNS anyelement AS
$func$
BEGIN

CASE pg_typeof(_data) 
WHEN 'text'::regtype THEN
    INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data)
    RETURNING txt
    INTO _result;

WHEN 'integer'::regtype THEN
    INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data)
    RETURNING nr
    INTO _result;

ELSE
    RAISE EXCEPTION 'Unexpected data type: %', pg_typeof(_data)::text;
END CASE;

END
$func$
LANGUAGE plpgsql;

Call:

SELECT f_insert_data(1, 'foo'::text);  -- explicit cast needed.
SELECT f_insert_data(1, 7);

Simple case

One function that returns TRUE / FALSE to indicate whether a row has been inserted, only one input parameter of varying type:

CREATE FUNCTION f_insert_data2(_id int, _data anyelement)
  RETURNS boolean AS
$func$
BEGIN

CASE pg_typeof(_data)
WHEN 'text'::regtype THEN
   INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data);

WHEN 'integer'::regtype THEN
   INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data);

ELSE
   RAISE EXCEPTION 'Unexpected data type: >>%<<', pg_typeof(_data)::text;
END CASE;

IF FOUND THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF;

END
$func$
LANGUAGE plpgsql;

The input type can be replaced with a text parameter for most purposes, which can be cast to and from any other type.