> 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
