Thread: Letting a function return multiple columns instead of a single complex one

"A.j. Langereis" <a.j.langereis@inter.nl.net> writes:
> The problem I am facing is that I will execute this function as part of =
> another query where the parameter will be one of the columns of another =
> table. Something like: "select bar.*, get_a_foo(c) from bar". I need the =
> result set to be like a table, because I'll have to use it later in =
> another query.

Try something like

test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b;
 c | a | b
---+---+---
 1 | 1 | 2
(1 row)

Not amazingly elegant, but it works.  Note that you need to beware of
the possibility that the subselect will get flattened, leading to
multiple evaluations of your function.  This doesn't happen in this
particular case because you declared the function as returning set,
but if you don't then you'll need additional countermeasures.

In general I'd suggest that this style of programming is forcing SQL to
do something SQL doesn't do very well, ie, emulate a functional
language.  It's likely to end up both notationally ugly and very
inefficient.  You should think hard about whether you can't express your
problem with views and joins instead.

            regards, tom lane

> test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b;
>  c | a | b
> ---+---+---
>  1 | 1 | 2
> (1 row)
>

Tanks! that works great! It managed to get it even a bit more simplified:
select bar.*, (get_a_foo(c)).* from bar;

> Not amazingly elegant, but it works.  Note that you need to beware of
> the possibility that the subselect will get flattened, leading to
> multiple evaluations of your function.  This doesn't happen in this
> particular case because you declared the function as returning set,
> but if you don't then you'll need additional countermeasures.
>
> In general I'd suggest that this style of programming is forcing SQL to
> do something SQL doesn't do very well, ie, emulate a functional
> language.  It's likely to end up both notationally ugly and very
> inefficient.  You should think hard about whether you can't express your
> problem with views and joins instead.
>

The reason that I need this is because of my other question (is there in pg
a function like oracle's rownum?). The function get_a_foo looks in reality a
bit more like the next:

create type foo_extended as (a int, b int, rowno int);

create or replace function get_a_foo_func(int)
 returns setof foo_extended as
'
declare
tmp_row foo_extended%rowtype;
i int;
begin
i := 1;

for tmp_row in (select * from foo where a = $1) loop
 tmp_row.rowno := i;
 return next tmp_row;
 i := i + 1;
end loop;

end;
'
language plpgsql volatile;

create or replace function get_a_foo(int)
 returns setof foo_extended as
'
select * from get_a_foo_func($1);
'
language sql volatile;

The function get_a_foo_func runs a query and adds to each row of the result
a rownum like number. The other, wrapper, function is to make it possible to
give a set as an imput parameter: unfortunately this is something that
doesn't seem to be supported by pl/pgsql.

Yours,

Aarjan Langereis

On Dec 07 12:34, A.j. Langereis wrote:
> I have two questions: fist of all, is there any function in pg like
> oracle's rownum?

[Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?]
http://archives.postgresql.org/pgsql-sql/2005-05/msg00123.php


--
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."

>
> The reason that I need this is because of my other question (is there in pg
> a function like oracle's rownum?). The function get_a_foo looks in reality a
> bit more like the next:
>
> create type foo_extended as (a int, b int, rowno int);
>
> create or replace function get_a_foo_func(int)
>  returns setof foo_extended as
> '
> declare
> tmp_row foo_extended%rowtype;
> i int;
> begin
> i := 1;
>
> for tmp_row in (select * from foo where a = $1) loop
>  tmp_row.rowno := i;
>  return next tmp_row;
>  i := i + 1;
> end loop;
>
> end;
> '
> language plpgsql volatile;
>
> create or replace function get_a_foo(int)
>  returns setof foo_extended as
> '
> select * from get_a_foo_func($1);
> '
> language sql volatile;
>
> The function get_a_foo_func runs a query and adds to each row of the result
> a rownum like number. The other, wrapper, function is to make it possible to
> give a set as an imput parameter: unfortunately this is something that
> doesn't seem to be supported by pl/pgsql.
>
> Yours,
>
> Aarjan Langereis
>
>

Maybe you can do something like:


 create type foo_extended as (a int, b int, rowno int);

create or replace function get_a_foo(int) returns setof foo_extended as '
 create temp sequence seq1;
 select *, nextval('seq1') from foo where a = $1;
' language sql volatile;


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

> test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from
bar) b;
> c | a | b
> ---+---+---
> 1 | 1 | 2
> (1 row)
>
>
Tanks! that works great! It managed to get it even a bit more
simplified:
select bar.*, (get_a_foo(c)).* from bar;

> Not amazingly elegant, but it works.  Note that you need to beware
of
> the possibility that the subselect will get flattened, leading to
> multiple evaluations of your function.  This doesn't happen in this
> particular case because you declared the function as returning set,
> but if you don't then you'll need additional countermeasures.
>
> In general I'd suggest that this style of programming is forcing SQL
to
> do something SQL doesn't do very well, ie, emulate a functional
> language.  It's likely to end up both notationally ugly and very
> inefficient.  You should think hard about whether you can't express
your
> problem with views and joins instead.
>
>
The reason that I need this is because of my other question (is there
in pg
a function like oracle's rownum?). The function get_a_foo looks in
reality a
bit more like the next:

create type foo_extended as (a int, b int, rowno int);

create or replace function get_a_foo_func(int)
 returns setof foo_extended as
'
declare
tmp_row foo_extended%rowtype;
i int;
begin
i := 1;

for tmp_row in (select * from foo where a = $1) loop
 tmp_row.rowno := i;
 return next tmp_row;
 i := i + 1;
end loop;

end;
'
language plpgsql volatile;

create or replace function get_a_foo(int)
 returns setof foo_extended as
'
select * from get_a_foo_func($1);
'
language sql volatile;

The function get_a_foo_func runs a query and adds to each row of the
result
a rownum like number. The other, wrapper, function is to make it
possible to
give a set as an imput parameter: unfortunately this is something that
doesn't seem to be supported by pl/pgsql.

Yours,

Aarjan Langereis



---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq