Forum OpenACS Development: Patch to allow PL/pgSQL functions to take %TYPE arguments!

I just saw this in the pgsql-hackers list. I am psyched! I wish it had come one month ago ;) Hopefully it'll make it into 7.1.1, which is dues one or two weeks from now. It'd make porting 4.x packages a lot easier.

From pgsql-hackers-owner+M8010@postgresql.org  Fri Apr 27 21:48:57
2001
Date: Fri, 27 Apr 2001 20:45:25 -0700
From: Ian Lance Taylor 
Subject: [HACKERS] Support for %TYPE in CREATE FUNCTION
 
This patch adds support for %TYPE in CREATE FUNCTION argument and
return types.
 
%TYPE is already supported by PL/pgSQL when declaring variables.
However, that does not help with the argument and return types in
CREATE FUNCTION.
 
Using %TYPE makes it easier to write a function which is independent
of the definition of a table.  That is, minor changes to the types
used in the table may not require changes to the function.
 
For example, this trivial function will work whenever `table' which
has columns named `name' and `value', no matter what the types of the
columns are.
 
CREATE FUNCTION lookup (table.name%TYPE)
   RETURNS table.value%TYPE
   AS 'select value from table where name = $1'
   LANGUAGE 'sql';
 
This patch includes changes to the testsuite and the documentation.
 
This work was sponsored by Zembu.
 
Ian

Argh!!!! Just received this on the pgsql-hackers list. *sighs* Apparently 7.2 will see huge improvements in procedural languages, since Jan Wieck has said that he'll improve them to support returning rowsets among other things.

From: Bruce Momjian 
Subject: Re: [PATCHES] Re: [HACKERS] Support for %TYPE in CREATE FUNCTION
To: Roberto Mello 
Cc: pgsql-hackers@postgresql.org, pgsql-patches@postgresql.org
X-VMS-To: IN%"rmello@cc.usu.edu"  "Roberto Mello"
X-VMS-Cc: IN%"pgsql-hackers@postgresql.org", IN%"pgsql-patches@postgresql.org"
 
> > Using %TYPE makes it easier to write a function which is independent
> > of the definition of a table.  That is, minor changes to the types
> > used in the table may not require changes to the function.
>
>       Wow! This would be _very_ useful! It's something I wish PostgreSQL
> had and I miss it everytime I write functions and remember PL/SQL.
>
>       Thanks a lot Ian, I hope this one makes it in (hopefully for 7.1.1)
 
Sorry, only in 7.2.  No new features in minor releases unless they are
very safe.

Returning rowsets?  If that means what I think it means, it would be
VERY cool.  I've often wished I could return rows from a function like
that in Oracle.

Don, would it be a feasible to change the postgres driver to take advantage of this ability to return rows from functions? It seems that the sybase driver does something of the sort, but the Oracle one does not. It might not be important to the database-independent architecture you guys are currently implementing, but down the road it would open up some cool possibilities. Actually, I wish the Oracle driver could do this...

This from the tcl api reference:

ns_db sp_exec executes a stored procedure that has been initialized with ns_db sp_start and ns_db sp_setparam. It returns "NS_DML" if the command was succsesfully executed but did not return rows, or it returns "NS_ROWS" if the command was successfully executed and did return rows (which can then be fetched with ns_db bindrow and ns_db getrow). It throws an error if the command failed. This function is implemented only for the Sybase database driver.

It looks like the PG team (mostly Tom) is not liking this patch at all. Bruce wants to put it on hold. If more knowledgeable folks could chime in, we may be able prevent this from being thrown out the window (this seems to be the route it's taking).

You can read the thread (and Tom's comments) at http://fts.postgresql.org/db/mw/msg.html?mid=116520.