Pgsql dynamic statements and null values

Guy Rouillier · *nix forums addict Joined: 02 Mar 2005 Posts: 73

Ragnar Hafstað wrote:

Martijn van Oosterhout · *nix forums Guru Joined: 02 Mar 2005 Posts: 674

On Tue, Mar 08, 2005 at 04:30:54PM -0600, Guy Rouillier wrote:

Ragnar Hafstað · *nix forums beginner Joined: 01 Mar 2005 Posts: 37

On Tue, 2005-03-08 at 16:30 -0600, Guy Rouillier wrote:

Guy Rouillier · *nix forums addict Joined: 02 Mar 2005 Posts: 73

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@postgresql.org so that your
message can get through to the mailing list cleanly

Google