current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 1 down vote

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)
share|improve this answer
    
So it continues execution even after the return?, ok thank you! –  Urco May 3 at 15:11
    
Yeah, functions continue after return. You can even use multiple returns, as long as they have the same recordset defintion –  Andomar May 4 at 8:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.