No data output from stored procedure (Postgresql)

Question

I have a basic stored procedure for a select statement. The select statement by itself works and shows me the data output, but when I try calling it from a stored procedure, it says 'CALL Query returned successfully in 107 msec', but there's no data output. Is there something that I'm missing from my stored procedure? (I'm also connecting a database on AWS with the basic tier, not sure if that makes a difference. Every other CRUD operation works except for select.)

CREATE PROCEDURE 
    experiment1()
LANGUAGE SQL
AS $$
    SELECT * FROM assignment    
$$

GMB · Accepted Answer · 2020-06-24 23:04:53Z

4

A Postgres procedure does not return anything. You can use a function instead, with the return query syntax. This requires enumerating the columns that the query returns. Assuming that your table has two columns, id and val, that would be:

create function experiment() 
returns table (id int, val text)
as $$
begin
    return query select * from assignment;
end;
$$ language plpgsql;

You can then invoke this set-returning function like so:

select * from experiment();

Demo on DB Fiddle:

create table assignment (id int, val text);
insert into assignment values(1, 'foo'), (2, 'bar');
-- 2 rows affected

create function experiment() 
returns table (id int, val text)
as $$
begin
    return query select * from assignment;
end;
$$ language plpgsql;

select * from experiment();

id | val
-: | :--
 1 | foo
 2 | bar

answered Jun 24, 2020 at 23:04

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

TheRealChx101 Over a year ago

A SELECT statement inside a stored procedure works but without any issues but I'm wondering why all of a sudden they just stopped for me.

Collectives™ on Stack Overflow

No data output from stored procedure (Postgresql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related