Returning multiple values in a function

Question

I've only recently begun dabbling with PostgreSQL and I've been building functions to handle the necessary tasks.

There is a table called queue, into which new tasks are added with status of "New". The function below should update these new tasks and return their queueid column values, which are bigint serials. Since there may be none, one or more tasks, I need to be able to return none, one or more values too.

Returning these values have me up against a wall. I've been googling for answers, but haven't had success yet. In the latest experiment I attempted to use an OUT variable and assign results to it, but apparently INTO cannot assign the values properly into an array and I haven't figured out how to make it work.

Please help me out. :) Much appreciated.

CREATE OR REPLACE FUNCTION assign_task(
  IN worker text,
  OUT id bigint[])
 RETURNS bigint[] AS
$BODY$BEGIN
 EXECUTE 'UPDATE queue
  SET status = ''In progress'', worker = $1
  WHERE status = ''New''
  RETURNING queueid'
 INTO id
 using worker;
END;$BODY$
LANGUAGE plpgsql VOLATILE

Did you check these -> 1. wiki.postgresql.org/wiki/… 2. postgresql.org/message-id/[email protected] 3. stackoverflow.com/questions/4547672/… — Techidiot
– Techidiot, Commented Sep 17, 2015 at 11:47
Yes I did. 1. Quite a wall of text that is difficult to read, but nevertheless could not find an applicable example I could work with. 2. Despite the subject containing multiple values, it is a different problem. 3. Again, different problem despite similarities in subject. — KeeperB5
– KeeperB5, Commented Sep 17, 2015 at 12:00

user330315 · Accepted Answer · 2015-09-17 12:02:20Z

6

You are over complicating things. As you want to return multiple values you need to define the function as returns table or returns setof. I prefer returns table because it lets you also define the column names of the result.

You also don't need PL/pgSQL for this, a plain SQL function with an UPDATE statement is enough:

CREATE OR REPLACE FUNCTION assign_task(IN worker text)
  returns table (id bigint)
as
$BODY$
 UPDATE queue
  SET status = 'In progress', worker = $1
  WHERE status = 'New'
  RETURNING queueid;
$BODY$
LANGUAGE sql VOLATILE;

answered Sep 17, 2015 at 12:02

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

KeeperB5 Over a year ago

Thank you, I was indeed over complicating things. :) I used setof because I could not find any way to create a function in pgAdmin that returns table. But setof is just fine for my purposes. Thanks again. :)

user330315 Over a year ago

@KeeperB5: creating a function that returns a table is not different in pgAdmin than in any other client: just run the appropriate SQL statement

Collectives™ on Stack Overflow

Returning multiple values in a function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related