RETURN QUERY-Record in PostgreSQL

Question

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 and tasks are both tables and v_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 and RETURN QUERY (...) and therefore expect the result to look like a SELECT from v_task (same columns with same data types). However, what really happens is (output from 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 column the output is forced into one, separated by commas.
As I am already using a special record type I can't use the AS keyword to specify the fields of my output.

Erwin Brandstetter · Accepted Answer · 2013-03-23 02:42:43Z

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;

Collectives™ on Stack Overflow

RETURN QUERY-Record in PostgreSQL

1 Answer 1

Calling a table function

Alternative here: plain SQL

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Calling a table function

Alternative here: plain SQL

Comments

Your Answer

Sign up or log in

Post as a guest

Related