I've already read this http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended but I still can't get it clear. I thought I could write something like create or replace testA(out setof record) as... but it seems I can't. Or am I missing something? then I wrote something like create or replace testA() returns setof record as $$ declare _row record; _cursor refcursor; begin open _cursor for select colA, colB from table; loop fetch _cursor into_row; exit when not found; return next _row; end loop; close _cursor; return; end; $$ language plpgsql; but then I've to specify the type of column in select * from testA() as (...); Shouldn't be the type of column known since they come from a select? Does the problem comes from the fact I could change the cursor definition at each call of the function? What if I'd like to avoid code duplication (defining returned types in more than one place)? In the example the returned type are still declared in 2 places (the table where they come from and the function signature): create or replace testA(out col1 int, out col2 text...) returns setof record as $$ ... Yeah I know there is no difference from any other function (even C functions) but when the column you're returning back start to be more than 3 it is a bit of a pain (read error prone). Declaring a composite type looks even more painful just to encapsulate simple queries [1]. myrow tablename%ROWTYPE; could be an option. Does it works on views too? Any other way to return recordset from functions? Just to know the options... [1] I'm thinking to encapsulate them because I foresee they won't be simple forever... and if they are encapsulated now I won't have to change the client code later. I just would like to have an idea of the cost of doing it now. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
