patternsqlMinor
Postgresql assign mutliple row query result to variable and continue execution
Viewed 0 times
postgresqlresultcontinuequerymutlipleandrowvariableexecutionassign
Problem
I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them:
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.
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 VOLATILEThe 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.
Solution
The usual approach is to return a rowset using
This prints:
You can also store the result of the function in a (temporary) table:
This prints:
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)Code Snippets
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();NOTICE: continuing after return
col1
------
6
7
42
(3 rows)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();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)Context
StackExchange Database Administrators Q#99369, answer score: 5
Revisions (0)
No revisions yet.