Forum OpenACS Development: Patch to allow PL/pgSQL functions to take %TYPE arguments!
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 <email@example.com> 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
If it gets into 7.1.1 let's use it, I have no problem requiring 7.1.1 for OpenACS 4.x because of the known pg_dump ordering problem in 7.1.0.
Last I heard 7.1.1 was due to be released on Monday.
From: Bruce Momjian <firstname.lastname@example.org> Subject: Re: [PATCHES] Re: [HACKERS] Support for %TYPE in CREATE FUNCTION To: Roberto Mello <email@example.com> Cc: firstname.lastname@example.org, email@example.com X-VMS-To: IN%"firstname.lastname@example.org" "Roberto Mello" X-VMS-Cc: IN%"email@example.com", IN%"firstname.lastname@example.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.
We can live without it but it sure would be nice to have.
VERY cool. I've often wished I could return rows from a function like
that in Oracle.
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.
You can read the thread (and Tom's comments) at http://fts.postgresql.org/db/mw/msg.html?mid=116520.