Search Postgresql Archives

Pgsql dynamic statements and null values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux