Re: PL/pgSQL: How to return two columns and multiple rows - Mailing list pgsql-general

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');
   myfunc
-------------
 (foo1,bar1)
 (foo2,bar2)
 (foo3,bar3)
 (foo4,bar4)
(4 rows)