Skip site navigation (1) Skip section navigation (2)

revision of todo: NULL for ROW variables

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: revision of todo: NULL for ROW variables
Date: 2010-10-28 12:55:40
Message-ID: AANLkTim1xatC=b=isjiNO9kDmRRz4NbpbuspRQqsqORy@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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 = 3 THEN
    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

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2010-10-28 13:13:22
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Kevin GrittnerDate: 2010-10-28 12:05:48
Subject: Re: max_wal_senders must die

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group