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
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 |