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

[Sven Geggus <lists@xxxxxxxxxxxxxxxxxxxxx>]

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)

Regards

Sven

-- 
Exploits and holes are a now a necessary protection against large
corporate interests. (Alan Cox)

/me is giggls@ircnet, http://sven.gegg.us/ on the Web


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general