1

I have this PostgreSQL PL/pgSQL function:

CREATE OR REPLACE FUNCTION get_people()
RETURNS SETOF people AS $$
BEGIN
RETURN QUERY SELECT * FROM people;
END;
$$ LANGUAGE plpgsql;

Then I try to read the data in an application using SOCI, with this code:

session sql {"postgresql://dbname=postgres"};
row person {};
procedure proc = (sql.prepare << "get_people()", into(person));
proc.execute(true);

I would expect that person have the data of the first person, but it contains only one column with the name of the stored procedure (i.e., "get_people").

So I don't know what I am doing wrong here, or not doing. Is it the PL/pgSQL code or the SOCI code? Maybe SOCI does not support dynamic binding for stored procedures. Also, this method would allow me to read the first row only, but what about the rest of rows? I know SOCI comes with the rowset class for reading result sets, but the documentation says it only works with queries. Please help.

eXe
  • 186
  • 2
  • 9

1 Answers1

1

SELECT get_people() will return a single column, of type people, named after the procedure.

SELECT * FROM get_people() will give you the expected behaviour, decomposing the people records into their constituent fields.

Judging from the source, it looks like the SOCI procedure class (or at least, its Postgres implementation) is hard-wired to run procedures as SELECT ... rather than SELECT * FROM ....

I guess this means you'll need to write your own query, i.e.:

statement stmt = (sql.prepare << "SELECT * FROM get_people()", into(person));
Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • Yes, now I get it! For functions that return nothing you should use the `procedure` class, but for functions that return query results you must use the `statement` class and write a query for calling the function (just as you do it in SQL). Once you realize this, you know that you can get multiple rows with the `rowset` class using the same method. Thanks man. – eXe Sep 28 '16 at 15:21