RETURN QUERY-Record in PostgreSQL
Calling a table function
To retrieve individual columns from a function returning multiple columns (effectively a composite type or row type), call it with:
SELECT * FROM func();
If you want to, you can also just SELECT
some columns and not others. Think of such a function (also called table function) like of a table:
SELECT objectid, projectid, title FROM func();
Alternative here: plain SQL
If you use PostgreSQL 9.1 or later you might be interested in this variant. I use a writable CTE to chain the INSERTs.
One might be tempted to add the final SELECT as another module to the CTE, but that does not work in this case, because the newly inserted values are not visible in the view within the same CTE. So I left that as a separate command - without brackets around the 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;
Marc Fischer
Updated on June 11, 2022Comments
-
Marc Fischer almost 2 years
I am trying to write a PostgreSQL function that inserts data in the database and then receives 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
andtasks
are both tables andv_task
is a view, which is a join of the two. The reason why I return data that I just inserted is that there are some triggers doing work on it.So far so good. I use
RETURNS SETOF api.v_task
as my return type andRETURN QUERY (...)
and therefore expect the result to look like aSELECT from v_task
(same columns with same data types). However, what really happens is (output from pgAdmin, same result from mynode.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 column the output is forced into one, separated by commas.
As I am already using a special record type I can't use theAS
keyword to specify the fields of my output.