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
