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

[A.j. Langereis]

Dear all,

I have two questions: fist of all, is there any function in pg like oracle's rownum?

secondly, I wonder how it is possible to let a function return a dataset with different columns instead of a single, complex, one.

create table foo (a int, b int);

insert into foo (a,b) values (1,2);
insert into foo (a,b) values (2,3);

create or replace function get_a_foo(a int)
returns setof foo as
$$
select * from foo where a = $1;
$$
language sql volatile;

something like "select get_a_foo(1);" would return:
get_a_foo
-----------
(1,2)
(1 row)

whereas "select * from get_a_foo(1);" will retunr:
a | b
---+---
1 | 2
(1 row)

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.
The whole construction works fine if there would be only one column in the resultset of the query, something that is not the case here.

Anyone any suggestion?

Yours Aarjan

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[A.j. Langereis]

Post by 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;

Post by Tom Lane
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

[Jaime Casanova]

Post by A.j. Langereis
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
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 ;)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Volkan YAZICI]

Post by A.j. Langereis
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."

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[A.j. Langereis]

Post by Tom Lane
test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from

bar) b;

Post by Tom Lane
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;

Post by Tom Lane
Not amazingly elegant, but it works. Note that you need to beware

of

Post by Tom Lane
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

Post by Tom Lane
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

Post by Tom Lane
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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings