Re: How to return seto records from seof record function?

On Tue, Feb 25, 2025 at 8:47 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

>
> I don't think there is a way to get a generic "record" as result.
> And even if you could, you would still have to specify a column list
> when you call autonomous().
>
> Attempts to write functions with polymorphic return type are usually
> futile.
>
> Perhaps you can return a "SETOF jsonb"...

There is only one non-jsonb method I'm aware of to convert string query to
result without specifying result structure, and that's via refcursors,
something like:
begin;
BEGIN;

CREATE FUNCTION f() RETURNS TEXT AS
$$
DECLARE
r REFCURSOR DEFAULT 'test';
BEGIN
OPEN r FOR EXECUTE $z$SELECT 'a' AS a, 1 AS b$z$;
RETURN r;
END;
$$ LANGUAGE PLPGSQL;

SELECT f();

FETCH test;
...

..I doubt it works in OP's case though as this only works to push all the
way back to the client app. but it's a neat artifact from yore.

In modern postgres, I think jsonb is the way to go. Function output syntax
is one of the clunkiest parts of the language, you are on a freight train
to deep dynamic SQL; it sure would be nice if we could somehow pass an
output definition somehow in a way the calling function or query could
use. This mostly comes up in my experience with analytics, where the
column needs are very dynamic and layered.

merlin