Forum OpenACS Development: SQL functions returning rowsets (of one type)
test=# select * from foo;
name | age
roberto | 24
romulo | 24
ronald | 24
renata | 24
test=# create function names() returns setof varchar as '
test'# select name from foo
test'# ' language 'sql';
test=# select names() as names;
<p>I just saw this on the pgsql-sql mailing list. I had never read
about this on the documentation, both current and development, so I
thought I'd post this here in case we can use it in our porting
You can also pass in table names as parameters, and then address them
like table_name.function_name, but I've not played with this.
You can also pass in table names to PL/pgSQL but I've not succeeded in
figuring out exactly how to do anything useful afterwards. I just
spent a few minutes at it a few weeks ago and need to spend some more
time to see exactly what's going on. It would
be good information to have as it eliminates the need for "dynamic
SQL" in some cases.
The ACS doesn't define functions that return rowsets, and in fact I'm
not sure that PL/SQL supports it. Anyway, I asked last month if ACS
4.1 used any functions returning rowsets and the answer was "no".
PL/pgSQL will get to return rowsets at some point and that will be
very cool. I've been frustrated by that lack in some of my own
personal work with PG.
Are you talking about another mechanism besides the EXECUTE statements that are used to build dynamic queries in PL/PgSql? If so, is this feature documented anywhere?
I can pass in table names to PL/pgSQL but haven't figured out exactly what I'm allowed to do afterwards.
I can probably refer to them by declaring them to be of type RECORD?
That's how NEW and OLD are passed to triggers. The semantics once you get the parameters passed in and declared correctly are there, obviously, since triggers work. The question is whether or not I can define such magic at the user level.
I should play with this, maybe I will this afternoon. Reducing the use of EXECUTE was what I had in mind, indeed. I have some things I have to do today, so I won't be playing with this for awhile ...
I've trying to create a function with a table name as an argument, but i cannot use the "SELECT INTO..." statement, it raises an error.
Could someone figure out why this error appears? Could anybody post an example of a function receiving table names as arguments?
create table zz1 (col1 integer); insert into zz1 values(1); insert into zz1 values(2); create function zztest(text, text) returns integer as ' declare table_name alias for $1; column_name alias for $2; dyn_query varchar(4000); return_value integer; begin dyn_query := ''select into return_value sum(''|| quote_ident(column_name) ||'') from ''|| quote_ident(table_name); execute dyn_query; return return_value; end; ' language 'plpgsql'; -------- test71=# select zztest('zz2', 'col1'); ERROR: parser: parse error at or near "into"
Thanks in advance
Sorry, the actual test is:
test71=# select zztest('zz1', 'col1'); ERROR: parser: parse error at or near "into"
create function zztest(text, text) returns integer as ' declare table_name alias for $1; column_name alias for $2; dyn_query varchar(4000); v_rec record; begin dyn_query := ''select sum('' || quote_ident(column_name) || '') as return_value from '' || quote_ident(table_name); FOR v_rec in EXECUTE dyn_query LOOP return v_rec.return_value; END LOOP; END;
For the record, the reason why SELECT INTO does not work with EXECUTE is because when the query is passed to the executor, it knows nothing about the PL/PgSQL function. It's just another query, so it can't return the value to a variable by itself (unless you do something like Dan suggests).
What you're saying about the reason for select into not working in an EXECUTE statement is true, but there are plans to make it work for 7.2. In fact, I think they explicitly disabled it from working in 7.1 to prevent some type of backwards compatiblity problems. I'm not sure of the exact details as to why it would cause a compatiblity problem, but they decided to disable it based on the fact that a work-around existed.
In other words, in the PG query language:
select * into foo from bar;
copies the relation bar into the temporary relation foo.
As far as passing a table in, something like this works:
create function foo(relation_name) returns integer as ' declare r alias for $1; begin return r.column_name; end;' language 'plpgsql'; select foo(relation_name) from relation_name where key = key_value;The problem I was alluding in my earlier post is that this doesn't work for children of relation_name, only for relation_name itself. It appears to be an oversight but won't be fixed for 7.1, that's certain. If you pass in a relation which inherits from relation_name, the function works but gets an error on its return claiming it is returning a relation_name of the wrong size. This is because the relationship is passed by reference, with the size check on the return done on the declared relation type rather than the actual type of the relation passed in. Oops.
Thus my meandering thoughts that one could use an untyped RECORD ala new/old in triggers to write functions that work on mulitple types that happen to have column names in column (as is true in the inheritance example).
Can't do that, though, the trigger parameters are passed by special magic internal to Postgres. You can't declare a parameter to be of type RECORD.
because pl/sql will let you have numerous out variables (including cursors), its actually fairly easy to use an out cursor to return arbitrary result sets. how well an app can utilize this depends on they're bindings to oracle.