pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: rob <rob@dsvr.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql grief
Date: Mon, 12 Feb 2001 11:45:18 -0500
Message-ID: <18828.981996318@sss.pgh.pa.us> (raw)
In-Reply-To: <web-1189338@davinci.ethosmedia.com>
References: <web-1189338@davinci.ethosmedia.com>

"Josh Berkus" <josh@agliodbs.com> writes:
> 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.  

That's not the only way; you can also use FOR ... EXECUTE, as Jan
pointed out awhile back in the other thread.

What does not work at the moment is to EXECUTE a 'SELECT INTO',
because EXECUTE just hands the string off to the main SQL parser
which knows nothing about plpgsql variables.  We'll try to improve
this for 7.2, but it's far too late to get it done for 7.1.

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

Hm, good point; coping with NULLs in this context will require some
explicit programming.  Yech.  I'd recommend using quote_string for
the TEXT parameters, but that doesn't help any for the NULL case.
(I wonder if quote_string should be defined to return 'NULL' for
a NULL input?  Again, too late for 7.1, but seems like a good future
improvement.)

> (and keep in mind
> that Postgres functions currently have trouble with NULLS as
> input parameters).

Not in 7.1 they don't ...

			regards, tom lane



view thread (14+ messages)  latest in thread

Message-ID: <18828.981996318@sss.pgh.pa.us>
Permalink:  ../18828.981996318@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18828.981996318@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, josh@agliodbs.com, rob@dsvr.net
  Subject: Re: plpgsql grief
  In-Reply-To: <18828.981996318@sss.pgh.pa.us>
  References: <web-1189338@davinci.ethosmedia.com> <18828.981996318@sss.pgh.pa.us>

* 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