Re: Returning RECORD from PGSQL without custom type?

On Sat, 10 May 2008 02:36:50 -0500
"D. Dante Lorenso" <dante(at)lorenso(dot)com> wrote:

> Instead of doing this:
>
> CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint)
> RETURNS SETOF record AS
> $body$
> ...
> $body$
> LANGUAGE 'plpgsql' VOLATILE;

What's the problem with the above?
You don't like to specify the returned type in each "caller"?

then

CREATE OR REPLACE FUNCTION "my_custom_func" (in_value bigint
out ret1 int, out ret2 text, out ret3 float
)
RETURNS SETOF record AS
$body$
declare
row record;
begin
for ...

ret1:=row.col1;
ret2:=row.col2;
if(row.col3)<7 then
ret3:=row.col3;
else
ret3:=0;
end if;
...
$body$
LANGUAGE 'plpgsql' VOLATILE;

then you can call
select ret2 from my_custom_func(100) where ret1<12;

> 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;
>

it looks similar to the above...

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

I'm not sure if you can...

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

yeah it could be a nice shortcut to define types "locally".

Once you call "OUT" the type, you could avoid the ret1:=row.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it