Forum OpenACS Development: SQL functions returning rowsets (of one type)

Collapse
1: SQL functions returning rowsets (of one type)
Posted by Roberto Mello on 02/15/01 12:06 AM
Did you guys know about this?
<pre>

test=# select * from foo;
  name  | age
---------+-----
roberto |  24
romulo  |  24
ronald  |  24
renata  |  24
(4 rows)

test=# create function names() returns setof varchar as '
test'# select name from foo
test'# ' language 'sql';
CREATE
test=# select names() as names;
  names
---------
roberto
romulo
ronald
renata
(4 rows)
</pre>

<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
efforts.

Collapse
2: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Don Baccus on 02/15/01 08:04 PM
Yes, I did, actually - only SQL functions can return rowsets at this
point, though.

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.

Collapse
3: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Dan Wickstrom on 02/15/01 08:17 PM
"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."

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?

Collapse
4: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Don Baccus on 02/15/01 10:09 PM
I was specifically speaking of stored SQL procedures.  This restricts you to a sequence of queries (no control structures).

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 ...

Collapse
5: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Gaizka Villate on 02/19/01 01:08 PM
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

Hi!

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

Collapse
6: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Gaizka Villate on 02/19/01 01:11 PM
Typo:

Sorry, the actual test is: 

test71=# select zztest('zz1', 'col1');
ERROR:  parser: parse error at or near "into"

Collapse
7: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Dan Wickstrom on 02/19/01 02:44 PM
Select into doesn't work in dynamic queries, but there is a work-around: 

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;
Collapse
8: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Roberto Mello on 02/19/01 03:47 PM
I knew I was forgetting something in the HOWTO. Can I use this example there?

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).

Collapse
9: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Dan Wickstrom on 02/19/01 04:07 PM
Feel free, I certainly don't mind.

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.

Collapse
10: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Don Baccus on 02/19/01 04:53 PM
Since the exec string is treated as a vanilla query, the "INTO" actually creates a temp table just as it does from PSQL, rather than into a local PL/pgSQL variable. If it's been disabled it was probably to avoid the confusion (I remember that the user triggering this discussion thought it really was going "INTO" their local variable).

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.

Collapse
11: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Alex Sokoloff on 02/20/01 04:42 AM
As a follow up to something Don wondered about earlier, Oracle PL/SQL does not return rowsets - at least not to AOLserver. I've heard that there's some workaround to do it, but it's very ugly. Also you can return rowsets to PL/SQL-savvy clients (Oracle clients, I guess) through package headers. I work in a shop that uses Oracle/AOLServer (but not ACS, for the most part). A lot of the other developers have SQL server experience, and they definitely miss having the abililty to write functions that return rows.
Collapse
12: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Kapil Thangavelu on 02/21/01 10:18 AM
follow up to alex's comments.

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.

Collapse
13: Response to SQL functions returning rowsets (of one type) (response to 1)
Posted by Don Baccus on 02/21/01 04:41 PM
From our POV, the key thing is that ACS 4.1 doesn't use out cursors or  functions returning rowsets so we're home free in this regard...