From: Josh Berkus <josh@agliodbs.com>
To: rob <rob@dsvr.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql grief
Date: Mon, 12 Feb 2001 08:05:54 -0800
Message-ID: <web-1189338@davinci.ethosmedia.com> (raw)
In-Reply-To: <3A82CDB5.7C8653FC@dsvr.net>
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, rob@dsvr.net
Subject: Re: plpgsql grief
In-Reply-To: <web-1189338@davinci.ethosmedia.com>
References: <3A82CDB5.7C8653FC@dsvr.net> <web-1189338@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox