Обсуждение: Broken handling of NULLs in TG_ARGV

plpgsql's handling of NULLs in TG_ARGV turns actual nulls into text 
'null'. Hopefully we can all agree that's broken. I'd like to fix it, 
but wonder how to handle existing user code.

My suspicion is that most users will never notice this and I can just 
fix it.

I could also add a plpgsql option to provide the old behavior.

What I'd prefer not to do is keep defaulting to the current behavior. 
Users are unlikely to notice that, keep using the broken behavior, and 
still complain when we change the default.

decibel@decina.local=# create temp table t(t text);
CREATE TABLE
decibel@decina.local=# create function tg() returns trigger language 
plpgsql as $$BEGIN RAISE '"%" is null? %', tg_argv[0], tg_argv[0] is 
null; end$$;
CREATE FUNCTION
decibel@decina.local=# create trigger t before insert on t for each row 
execute procedure tg(NULL);
CREATE TRIGGER
decibel@decina.local=# insert into t values('a');
ERROR:  "null" is null? f
decibel@decina.local=# drop trigger t on t;
DROP TRIGGER
decibel@decina.local=# create trigger t before insert on t for each row 
execute procedure tg();
CREATE TRIGGER
decibel@decina.local=# insert into t values('a');
ERROR:  "<NULL>" is null? t
decibel@decina.local=# drop trigger t on t;
DROP TRIGGER
decibel@decina.local=# create trigger t before insert on t for each row 
execute procedure tg('null');
CREATE TRIGGER
decibel@decina.local=# insert into t values('a');
ERROR:  "null" is null? f
decibel@decina.local=#

-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> plpgsql's handling of NULLs in TG_ARGV turns actual nulls into text 
> 'null'. Hopefully we can all agree that's broken.

You apparently have not read the CREATE TRIGGER reference page very
carefully:
arguments
    An optional comma-separated list of arguments to be    provided to the function when the trigger is executed. The
arguments are literal string constants. Simple names and    numeric constants can be written here, too, but they will
all be converted to strings.
 

There isn't any such thing as a genuine SQL NULL argument; the examples
you provided are just text strings, not SQL NULLs.  In order to make them
be actual nulls, we would have to redefine the arguments as being
expressions of some sort, which is problematic for backwards-compatibility
reasons.  It also seems like rather a lot of new mechanism to add for
something with (evidently) near-zero user demand.
        regards, tom lane

On 4/30/15 6:44 PM, Tom Lane wrote:
> There isn't any such thing as a genuine SQL NULL argument; the examples
> you provided are just text strings, not SQL NULLs.  In order to make them
> be actual nulls, we would have to redefine the arguments as being
> expressions of some sort, which is problematic for backwards-compatibility
> reasons.  It also seems like rather a lot of new mechanism to add for
> something with (evidently) near-zero user demand.

Ahh, I thought the array started life as an actual array, not char **. 
So yeah, not nearly as easy to fix. :(
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com