On Tue, Feb 25, 2025 at 8:47 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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
