Pgsql dynamic statements and null values

From: "Guy Rouillier" <guyr(at)masergy(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Pgsql dynamic statements and null values
Date: 2005-03-08 22:30:54
Message-ID: CC1CF380F4D70844B01D45982E671B2348E6BC@mtxexch01.add0.masergy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adam Tomjack 2005-03-08 23:09:09 Can't delete - Need cascading update instead
Previous Message Ladislav Linhart 2005-03-08 22:20:57 Re: LIMIT and his implementation