Re: plpgsql grief

Rob,

> I figured moving some 'simple' db code from my
> application to it's more
> natural home in the db would work out. Bummer. Not only
> do i have to run
> 7.1 (beta 4) to be able to dynamically generate queries,
> I'm finding it
> *extrememly* difficult to get to get my simple functions
> to work (plus
> for the 'widest used open source db' i'm finding examples
> very hard to
> come by)
<rant>
Keep in mind that Open Source usually means DIY as well, or
it wouldn't be free. If you have mission-critical problems,
pay-for support is available from two companies.

As for the PL/pgSQL documentation, everyone acknowledges
it's skimpy at best. Several of us PL/pgSQL users plan to
write up more extensive docs *when we have time*. The doc
writers will be volunteers, so don't hold your breath.

And, as another developer pointed out, the EXECUTE
functionality already goes beyond the scope of Microsoft's
Transact SQL, a $1000=$10,000 + product.
</rant>

In the meantime:

> Example 1 :
>
> create function testfunc (text) returns int4 as '
> declare
> sql varchar;
> res int4;
> begin
> sql=''SELECT INTO res2 id FROM ''||$1 ;
> execute sql ;
> return res;
> end;
> ' language 'plpgsql' ;

SELECT INTO functionality is being dropped from EXECUTE. If
you're interested in the reasons why, we've been discussing
it on the list for the last 2 weeks; leaf throud the
archives.

The main restriction is this: EXECUTE passes the query to a
seperate sub-process, and as such you may not pass *any*
unexpanded variables into the EXECUTE statement. Within
EXECUTE, those variables are out of scope.

Thus your only way to get stuff back from EXECUTE is to save
the results you want to a temporary table (using CREATE
TABLE AS ...), and read them back using a query. Not
high-performance, but it gets the job done.

Tom Lane and Jan Wieck have suggested that we might have
more flexible dynamic query generation for 7.2, but that's a
ways off.

Thus, your second function should be:

> create function update_trans (text, integer, text, text,
> text, text,
> text) returns boolean as '
> declare
> tbl alias for $1 ;
> begin
> execute ''insert into tbl (objid, objtbl, et, event,
> time, reason,
> owner) values ('' || $2 || '', '' || $3 || '', '' || $4
|| '', '' || $5 || '', current_timestamp, '' || $6 || '',
'' || $7 || '')'';
> return TRUE;
> end;
> ' language 'plpgsql' ;

With adjustments made to the syntax for data type delimiters
and replacing any nulls with the work NULL (and keep in mind
that Postgres functions currently have trouble with NULLS as
input parameters).

The rest is up to you ... or hire an expert.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco