Обсуждение: 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