> 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
