CODEWITHSUNDEEP
postgresql
KeeperB5
KeeperB5

Reputation: 333

Returning multiple values in a function

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

Upvotes: 2

Views: 8331

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 6

Related Questions