Thread: Returning RECORD from PGSQL without custom type?

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

Hello

there exist table returning functions patch, but newer been applied.
It's some what you need.

Why don't you use OUT variables?

try

CREATE OR REPLACE FUNCTION fce(IN value int, OUT a int, OUT b int)
RETURNS SETOF record AS $$
BEGIN
  FOR i IN 1..$1 LOOP
    a := i + 1; b := i + 2;
    RETURN NEXT;
  END LOOP;
END;
$$ LANGUAGE plpgsql IMMUTABLE:

SELECT * FROM fce(10);

http://www.postgresql.org/docs/8.3/interactive/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

Regards
Pavel Stehule

2008/5/10 D. Dante Lorenso <dante@lorenso.com>:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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