Обсуждение: Pgsql dynamic statements and null values

We use a dynamic statement in a pgsql stored function to insert rows
into a table determined at run time.  After much debugging, I've
discovered that a null incoming argument will cause the dynamic
statement to evaluate to null.  The error message emitted is "unable to
execute null statement."  I provide a full example at the end of this
message.  To see the problem, remove the check_null function from the
insert_t1 stored proc, and execute the proc like this: select
insert_t1(null);

This is such a common usage pattern that I'm pretty sure I'm missing
something basic.  Pgsql provides quote_literal to aid with inserting a
literal string into a dynamically prepared statement.  My opinion is
that quote_literal should handle nulls as well, but if quote_literal
can't be changed for historical reasons, then providing another function
like check_null below would be very useful.  Basically, such a function
should supply the value NULL if the incoming value is null, or the
incoming value otherwise.

CREATE TABLE T1
   (
   f1 smallint
   );

CREATE OR REPLACE FUNCTION check_null
   (
   inval anyelement
   ) RETURNS varchar AS $$

DECLARE

   retval varchar := 'NULL';

BEGIN

   if inval is not null then
      retval := quote_literal(inval);
   end if;
   return retval;

END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION insert_t1
   (
   inval integer
   ) RETURNS VOID AS $$

DECLARE
   sqlstmt varchar;

BEGIN

   sqlstmt :=  '  INSERT INTO T1 '     ||
               '     (           '     ||
               '     F1          '     ||
               '     )           '     ||
               '  VALUES         '     ||
               '     (           '     ||
                     check_null(inval) ||
               '     )           '        ;

   execute sqlstmt;
   return;
END;
$$ LANGUAGE plpgsql;

--
Guy Rouillier

On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote:
> We use a dynamic statement in a pgsql stored function to insert rows
> into a table determined at run time.  After much debugging, I've
> discovered that a null incoming argument will cause the dynamic
> statement to evaluate to null.  The error message emitted is "unable to
> execute null statement."

can't you use COALESCE() ?

gnari

On Tue, Mar 08, 2005 at 04:30:54PM -0600, Guy Rouillier wrote:
> This is such a common usage pattern that I'm pretty sure I'm missing
> something basic.  Pgsql provides quote_literal to aid with inserting a
> literal string into a dynamically prepared statement.  My opinion is
> that quote_literal should handle nulls as well, but if quote_literal
> can't be changed for historical reasons, then providing another function
> like check_null below would be very useful.  Basically, such a function
> should supply the value NULL if the incoming value is null, or the
> incoming value otherwise.

Lookup the COALESCE and NULLIF functions.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Ragnar Hafstað wrote:
> On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote:
>> We use a dynamic statement in a pgsql stored function to insert rows
>> into a table determined at run time.  After much debugging, I've
>> discovered that a null incoming argument will cause the dynamic
>> statement to evaluate to null.  The error message emitted is "unable
>> to execute null statement."
>
> can't you use COALESCE() ?

Thanks Ragnar and Martijn for the replies.  NULLIF doesn't seem applicable here as I already have a null value coming
in,so I'm not comparing it to anything.  I had tried COALESCE before my original post and it produced the same result:
cannotexecute null statement.  However, your prompting motivated me to try a couple more alternatives.  Of the many I
tried,here is one that works: 

coalesce(quote_literal(inval), 'NULL')

Nice to know, but given the verbosity, I think I'll stick with my check_null(inval).  A worthwhile exercise, though,
sinceI can now reduce that function to this one line. 

--
Guy Rouillier