Re: plpgsql grief

"Josh Berkus" <josh(at)agliodbs(dot)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