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

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

CREATE OR REPLACE FUNCTION public.fx(i integer, OUT a text, OUT b text)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY SELECT 'foo'||id, 'bar'||id FROM generate_series(1,i) g(id);
  RETURN;
END;
$function$

postgres=# SELECT a,b FROM fx(4);
┌──────┬──────┐
│  a   │  b   │
╞══════╪══════╡
│ foo1 │ bar1 │
│ foo2 │ bar2 │
│ foo3 │ bar3 │
│ foo4 │ bar4 │
└──────┴──────┘
(4 rows)

Regards

Pavel

2015-06-18 14:36 GMT+02:00 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