Returning RECORD from PGSQL without custom type?

From: "D(dot) Dante Lorenso" <dante(at)larkspark(dot)com>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Returning RECORD from PGSQL without custom type?
Date: 2008-05-10 05:41:20
Message-ID: 48253580.70608@larkspark.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Instead of doing this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF record AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

I'd like to be able to do this:

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
RETURNS SETOF (col1name BIGINT, col2name TEXT, ...) AS
$body$
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

Because this is the only function that will be returning that TYPE and I
don't want to have to create a separate type definition just for the
return results of this function.

Maybe even more cool would be if the OUT record was already defined so
that I could simply select into that record to send our new rows:

RETURN NEXT OUT;

OUT.col1name := 12345;
RETURN NEXT OUT;

SELECT 12345, 'sample'
INTO OUT.col1name, OUT.col2name;
RETURN NEXT OUT;

Just as you've allowed me to define the IN variable names without
needing the legacy 'ALIAS $1 ...' format, I'd like to name the returned
record column names and types in a simple declaration like I show above.

Does this feature request make sense to everyone? It would make
programming set returning record functions a lot easier.

-- Dante

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Sergio Borgonovo 2008-05-10 07:14:14 choiche of function language was: Re: dynamic procedure call
Previous Message Pavel Stehule 2008-05-10 05:35:36 Re: dynamic procedure call