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
$$
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();
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