On 9/26/23 13:15, Tom Lane wrote:
Ron <ronljohnsonjr@xxxxxxxxx> writes:
On 9/26/23 12:46, Tom Lane wrote:
Ron<ronljohnsonjr@xxxxxxxxx> writes:
Is there a way to define the SETOF record on the fly, like you do with
RETURNS TABLE (f1 type1, f2 type2)?
Doesn't RETURNS TABLE meet the need already?
That rationale means that RETURN SETOF is not needed, and can be removed
from Pg, since "RETURNS TABLE meet the need already".
Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't. SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.
So... can ad hoc SETOF definitions be created in the function definition, or
is CREATE TYPE the only way to do it?
I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row.
There might be some other logic in the body of the FOR loop that is not
practical to embed in the body of the SELECT statement.
Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing. That makes
it clear that the type exists independently of the function.
If you're going to only use that type with the function, then an anonymous
record type is Good Enough.
Just like anonymous DO blocks are useful.
(Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)
It makes developers/DBAs lives just that much easier.
--
Born in Arizona, moved to Babylonia.
