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    
$$

Answer 1

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