Re: PL/pgSQL: How to return two columns and multiple rows

On 18/06/2015 13:36, Sven Geggus wrote:
> Hello,
>
> I supose this is simple, but I did not find a solution in the documentation.
>
> I would like to be able to do something like this:
>
> select myfunc('foo','bar');
> or
> select myfunc(foo, bar) from foobartable;
> or even
> select myfunc(foo, bar), 'baz' as baz from foobartable;
>
> Which should return something like this:
>  foo  | bar
> ------+------
>  foo1 | bar1
>  foo2 | bar2
>  foo3 | bar3
>  foo4 | bar4
> (4 rows)
>
> So the output should be at least two columns and (usually) more than one row.
>
> What I currently have is the following, which is mostly it.  Unfortunately
> it gives me only one column (I really need two) and I would have to create a
> custom type:
>
> CREATE TYPE t_foobar AS (foo text, bar text);
>
> CREATE or REPLACE FUNCTION myfunc(foo text, bar text)
> returns SETOF t_foobar as $$
> BEGIN
>   FOR i IN 1..4 LOOP
>     RETURN NEXT (foo || i::text, bar || i::text);
>   END LOOP;
>   RETURN;
> END;
> $$ language 'plpgsql';
>
> mydb=> select myfunc('foo','bar');

You need to do:

   select * from myfunc('foo','bar');

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie