Re: Dynamic result sets from procedures

On 11/2/17 16:40, Daniel Verite wrote:
> But instead of having procedures not return anything,
> couldn't they return whatever resultset(s) they want to
> ("no resultset" being just a particular case of "anything"),
> so that we could leave out cursors and simply write:

We could in general design this any way we want. I'm just going by
what's in the SQL standard and in existing implementations.

> CREATE PROCEDURE test()
> LANGUAGE plpgsql
> AS $$
> RETURN QUERY EXECUTE 'SELECT 1 AS col1, 2 AS col2';
> END;
> $$;
>
> Or is that not possible or not desirable?

RETURN means the execution ends there, so how would you return multiple
result sets?

> Similarly, for the SQL language, I wonder if the above example
> could be simplified to:
>
> CREATE PROCEDURE pdrstest1()
> LANGUAGE SQL
> AS $$
> SELECT * FROM cp_test2;
> SELECT * FROM cp_test3;
> $$;
> by which the two result sets would go back to the client again
> without declaring explicit cursors.
> Currently, it does not error out and no result set is sent.

But maybe you don't want to return all those results, so you'd need a
way to designate which ones, e.g.,

AS $$
SELECT set_config('something', 'value');
SELECT * FROM interesting_table; -- return only this one
SELECT set_config('something', 'oldvalue');
$$;

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services