Re: Returning RECORD from PGSQL without custom type?

On Sat, 10 May 2008 02:36:50 -0500
"D. Dante Lorenso" <dante@lorenso.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