Postgresql assign mutliple row query result to variable and continue execution

postgresql

I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them:

CREATE OR REPLACE FUNCTION script(
vtoken character varying) 
  RETURNS void AS
$BODY$
DECLARE
  aux character varying;
BEGIN

PERFORM (SELECT acquire_table(vtoken));

-- This query gets the dynamically generated token for the client in that codebar
aux := (SELECT token 
                FROM clients c JOIN tables t ON c.tablekey = t.tablekey 
                WHERE codebar = vtoken);

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));

--EXECUTE (SELECT issue_ticket(aux));

PERFORM (SELECT order_drinks(aux, '{1}', '{1}'));


END;$BODY$
  LANGUAGE plpgsql VOLATILE

The issue_ticket() function returns a multiple row result, I'd like to print it directly and continue execution of next function (order_drinks()). If I do PERFORM it does not show the results, and if I just try to execute the SELECT query as it is, it asks to assign return value to variable.

Best Answer

The usual approach is to return a rowset using returns table. The client calling the function can then print it. The function itself continues to run after the return. For example:

create or replace function script() 
RETURNS TABLE (col1 int)
language plpgsql
as $_$ begin
RETURN QUERY (VALUES (6), (7), (6*7));
RAISE NOTICE 'continuing after return';
end;
$_$;
select * from script();

This prints:

NOTICE:  continuing after return
 col1 
------
    6
    7
   42
(3 rows)

You can also store the result of the function in a (temporary) table:

create or replace function script2() 
returns table (col1 int)
language plpgsql
as $_$ 
begin
CREATE TEMPORARY TABLE temp_table (id int) ON COMMIT DROP;
INSERT INTO temp_table SELECT * FROM script();
return query (select * from temp_table);
end;
$_$;
select * from script2();

This prints:

NOTICE:  continuing after return
CONTEXT:  SQL statement "insert into temp_table select * from script() s where s.col1 < 42"
PL/pgSQL function script2() line 4 at SQL statement
 col1 
------
    6
    7
(2 rows)

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).

PostgreSQL – Assign Result of regexp_split_to_array() to Pl/pgSQL Array Variable

array is a reserved word. You cannot use it as variable name in plpgsql. This works:

DO
$do$
DECLARE
   _arr text[];
BEGIN
SELECT regexp_split_to_array('thiiis splits into array with spaces', '\s+')
INTO _arr;

RAISE NOTICE '%', _arr[1];
END
$do$;

SQL Fiddle

Also simplified E'\\s+' -> '\s+'.
standard_conforming_strings = ON is the default since Postgres 9.1 and should be the universal setting by now.

Best Answer

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

PostgreSQL – Assign Result of regexp_split_to_array() to Pl/pgSQL Array Variable

Related Question