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
