Обсуждение: revision of todo: NULL for ROW variables
Hello I am checking PLpgSQL ToDo topics, and I am not sure if this topic isn't done. And if not, then I would to get some detail. Now there is possible to test row's variable on NULL, now it is possible to assign NULL to row variable. What we can do more? a) There is small difference between returned value when we use a empty row or empty record variable CREATE OR REPLACE FUNCTION f2(int) RETURNS t2 AS $$ DECLARE rv t2; re record; BEGIN CASE $1 WHEN 0 THEN RETURN rv; WHEN 1 THEN RETURN re; ELSE RETURN null; END CASE; END; $$ LANGUAGE plpgsql; postgres=# SELECT f2(0);f2 ────() (1 row) Time: 0.759 ms postgres=# SELECT f2(1); f2 ────────[null] (1 row) Time: 0.570 ms postgres=# SELECT f2(2); f2 ────────[null] (1 row) () is equal to NULL for test IS NULL, but it isn't same - see: Time: 0.586 ms postgres=# SELECT f2(0) is null;?column? ──────────t (1 row) Time: 0.548 ms postgres=# SELECT f2(1) is null;?column? ──────────t (1 row) Time: 0.535 ms postgres=# SELECT f2(2) is null;?column? ──────────t (1 row) postgres=# SELECT 'Hello' || f2(0);?column? ──────────Hello() (1 row) Time: 51.546 ms postgres=# SELECT 'Hello' || f2(1);?column? ──────────[null] (1 row) so this is one known issue. Actually rowvar := NULL <-> reset all fields inside row. I think so this is perfect from perspective "IS [NOT] NULL" operator. But maybe it isn't practical. So we can distinct between assign some field to NULL and between assign row variable to NULL. This flag can be used just only for returning value. Some like DECLARE r rowtype; BEGIN IF a = 1 THEN RETURN r; -- result is NULL ELSIF a = 2 THEN r.x := NULL; RETURN r; -- result is () ELSIF a = 3THEN r.x := NULL; r := NULL; RETURN r; -- result is NULL; comments? Is this change some what we would? next question? I found one paradox. When some IS NULL, then any operation with this value should be NULL. But it isn't true for composite values! postgres=# CREATE TYPE t AS (a int, b int); CREATE TYPE Time: 66.605 ms postgres=# SELECT 'Hello' || (NULL, NULL)::t;?column? ──────────Hello(,) (1 row) postgres=# SELECT (NULL, NULL)::t is null;?column? ──────────t (1 row) does know somebody if this behave is good per ANSI SQL? Regards Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I am checking PLpgSQL ToDo topics, and I am not sure if this topic
> isn't done. And if not, then I would to get some detail.
I think that thread petered out because we didn't have consensus on
what the behavior ought to be. It goes back to whether there is
supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...)
regards, tom lane
On Thu, Oct 28, 2010 at 10:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> I am checking PLpgSQL ToDo topics, and I am not sure if this topic >> isn't done. And if not, then I would to get some detail. > > I think that thread petered out because we didn't have consensus on > what the behavior ought to be. It goes back to whether there is > supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...) I think somewhere along the line it was noticed that SQL says you are supposed to treat (null, null) as null and the behavior of 'is null' operator was changed to reflect this while other null influenced behaviors were left intact (for example, coalesce()). My take on this is that we are stuck with the status quo. If a change must be done, the 'is null' change should be reverted to un-standard behavior. The SQL standard position on this issue is, IMNSHO, on mars. merlin
On Oct 28, 2010, at 11:41 AM, Merlin Moncure wrote: > On Thu, Oct 28, 2010 at 10:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Pavel Stehule <pavel.stehule@gmail.com> writes: >>> I am checking PLpgSQL ToDo topics, and I am not sure if this topic >>> isn't done. And if not, then I would to get some detail. >> >> I think that thread petered out because we didn't have consensus on >> what the behavior ought to be. It goes back to whether there is >> supposed to be a difference between NULL and ROW(NULL,NULL,NULL,...) > > I think somewhere along the line it was noticed that SQL says you are > supposed to treat (null, null) as null and the behavior of 'is null' > operator was changed to reflect this while other null influenced > behaviors were left intact (for example, coalesce()). > > My take on this is that we are stuck with the status quo. If a change > must be done, the 'is null' change should be reverted to un-standard > behavior. The SQL standard position on this issue is, IMNSHO, on > mars. As someone who's wanted this... what if we had a dedicated function to tell you if a row variable had been defined? I definitelydon't like the though of creating something that effectively duplicates IS NULL, but I'd much rather that thancontinue not having the ability to tell if a row/record variable has been set or not. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
On Mon, 2010-11-01 at 09:44 -0500, Jim Nasby wrote: > > My take on this is that we are stuck with the status quo. If a > change > > must be done, the 'is null' change should be reverted to un-standard > > behavior. The SQL standard position on this issue is, IMNSHO, on > > mars. > > As someone who's wanted this... what if we had a dedicated function to > tell you if a row variable had been defined? I definitely don't like > the though of creating something that effectively duplicates IS NULL, > but I'd much rather that than continue not having the ability to tell > if a row/record variable has been set or not. If we just invent a couple more variants of NULL, it will solve all our problems ;) Seriously though, I think that we should stick as closely to the letter of the standard as possible here (or, if there is ambiguity, pick one reasonable interpretation). NULL semantics are confusing enough without everyone making their own subtle tweaks. Regards,Jeff Davis
Jeff Davis <pgsql@j-davis.com> wrote: > Seriously though, I think that we should stick as closely to the > letter of the standard as possible here (or, if there is > ambiguity, pick one reasonable interpretation). NULL semantics are > confusing enough without everyone making their own subtle tweaks. +1 If the standard behavior doesn't support all the functionality we need, we should be looking at PostgreSQL extensions which do not conflict with standard syntax. Supporting standard syntax with different semantics is evil. -Kevin
On Mon, Nov 1, 2010 at 2:29 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Jeff Davis <pgsql@j-davis.com> wrote: > >> Seriously though, I think that we should stick as closely to the >> letter of the standard as possible here (or, if there is >> ambiguity, pick one reasonable interpretation). NULL semantics are >> confusing enough without everyone making their own subtle tweaks. > > +1 > > If the standard behavior doesn't support all the functionality we > need, we should be looking at PostgreSQL extensions which do not > conflict with standard syntax. Supporting standard syntax with > different semantics is evil. I have basically two gripes with sql standard treatment of null row values. One is the backward compatibility problem (which extends all the way up to PQgetisnull, and would affect lots of my code) and the other is that you will lose the ability to ever usefully enforce table check constraints over rowtypes like we do for domains (you need to reserve rowtype := null to skirt the issue in plpgsql declarations). merlin