RETURN QUERY-Record in PostgreSQL

Question

I am trying to write a PostgreSQL function that inserts data into a database and then gets some data and returns it. Here is the code:

CREATE OR REPLACE FUNCTION newTask(projectid api.objects.projectid%TYPE, predecessortaskid api.objects.predecessortaskid%TYPE, creatoruserid api.objects.creatoruserid%TYPE, title api.objects.title%TYPE, description api.objects.description%TYPE, deadline api.objects.deadline%TYPE, creationdate api.objects.creationdate%TYPE, issingletask api.tasks.issingletask%TYPE) RETURNS SETOF api.v_task AS $$ DECLARE v_objectid api.objects.objectid%TYPE; BEGIN INSERT INTO api.objects(objectid, projectid, predecessortaskid, creatoruserid, title, description, deadline, creationdate) VALUES (DEFAULT, projectid, predecessortaskid, creatoruserid, title, description, deadline, creationdate) RETURNING objectid INTO v_objectid; INSERT INTO api.tasks(objectid, issingletask) VALUES (v_objectid, issingletask); RETURN QUERY (SELECT * FROM api.v_task WHERE objectid = v_objectid); END; $$ LANGUAGE plpgsql; 

objects and tasks are both tables, and v_task is a view that is a union of the two. The reason I am returning the data that I just inserted is because it has triggers on it.

So far so good. I use RETURNS SETOF api.v_task as my return type and RETURN QUERY (...) and therefore expect the result to look like SELECT from v_task (same columns with same data types). However, what really happens (exiting pgAdmin, same result from my node.js-application ):

 SELECT newTask( CAST(NULL AS integer), CAST(NULL AS integer), 1, varchar 'a',varchar 'a', cast(NOW() as timestamp(0) without time zone), cast(NOW() as timestamp(0) without time zone), true); newtask api.v_task -------- "(27,,,1,a,a,"2012-03-19 12:15:50","2012-03-19 12:15:50","2012-03-19 12:15:49.629997",,t)" 

Instead of several columns, the output is forced into one, separated by commas.
Since I already use a special record type, I cannot use the AS keyword to indicate the fields of my output.

Answer 1

Table function call

To get individual columns from a function that returns multiple columns (in fact, a composite type or a row type), call it with:

 SELECT * FROM func(); 

If you want, you can simply SELECT to specify several columns, and not others. Think of a function (also called a table function) like a table:

 SELECT objectid, projectid, title FROM func(); 

The alternative is here: plain SQL

If you are using PostgreSQL 9.1 or later , you might be interested in this option. I use a writable CTE for the INSERT chain.

You may be tempted to add the final SELECT as another module in the CTE, but in this case it does not work because recently inserted values ​​are not visible in the view within the same CTE. So I left this as a separate command - without parentheses around SELECT :

 CREATE OR REPLACE FUNCTION new_task ( _projectid api.objects.projectid%TYPE ,_predecessortaskid api.objects.predecessortaskid%TYPE ,_creatoruserid api.objects.creatoruserid%TYPE ,_title api.objects.title%TYPE ,_description api.objects.description%TYPE ,_deadline api.objects.deadline%TYPE ,_creationdate api.objects.creationdate%TYPE ,_issingletask api.tasks.issingletask%TYPE) RETURNS SETOF api.v_task AS $func$ DECLARE _objectid api.objects.objectid%TYPE; BEGIN RETURN QUERY WITH x AS ( INSERT INTO api.objects ( projectid, predecessortaskid, creatoruserid, title , description, deadline, creationdate) VALUES (_projectid, _predecessortaskid, _creatoruserid, _title , _description, _deadline, _creationdate) RETURNING objectid ) INSERT INTO api.tasks (objectid, issingletask) SELECT x.objectid, _issingletask FROM x RETURNING objectid INTO _objectid; RETURN QUERY SELECT * FROM api.v_task WHERE objectid = _objectid; END $func$ LANGUAGE plpgsql;