Thread: SETOF

SETOF

From
Fernando
Date:
Hi,
I am using Postgresql version 7.2.2
I made a small function...

CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'
SELECT names from mi_tabla WHERE city = $1; '
language ' SQL ';

  ejem1
------------
  Sergio
  Carlos
  Fernando

When wanting to obtain several columns I do this...

CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as'
SELECT * from mi_tabla WHERE city = $1;'
language ' SQL ';

  ejem2
---------------
  137956448
  137956448
  137956448

The number of registries that return is the correct, the question is, because
it does not return the fields of the table, and that is what in its place
this showing to me...
Greetings and thank you very much!

Re: SETOF

From
glenn
Date:
Hi Fernando
I believe that if your function is going to be used with in another
function, then you can access the members/columns in the rows,

e.g
CREATE OR REPLACE FUNCTION crunch_ejem( varchar ) RETURNS something AS '

    x record;
BEGIN
    for each x in select ejem1($1) loop
      perform do_something (name(x));
        -- or do_something(x.name);
    end loop;
    return your_result_here::something;
END;
' language 'plpgsql';

 but you can't ( or at least I can't) select them onto the screen in
psql in a way that makes sense.

This is what I found when I had this problem a week ago (since then I've
upgraded to 7.3), but I'd love to hear someone elses take on it.

Glenn
On Wed, 2003-03-05 at 03:48, Fernando wrote:
> Hi,
> I am using Postgresql version 7.2.2
> I made a small function...
>
> CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'
> SELECT names from mi_tabla WHERE city = $1; '
> language ' SQL ';
>
>   ejem1
> ------------
>   Sergio
>   Carlos
>   Fernando
>
> When wanting to obtain several columns I do this...
>
> CREATE FUNCTION ejem2(varchar) RETURNS SETOF mi_tabla as'
> SELECT * from mi_tabla WHERE city = $1;'
> language ' SQL ';
>
>   ejem2
> ---------------
>   137956448
>   137956448
>   137956448
>
> The number of registries that return is the correct, the question is, because
> it does not return the fields of the table, and that is what in its place
> this showing to me...
> Greetings and thank you very much!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>