row_is_null.sql

SELECT ROW(NULL) IS NULL;
SELECT ROW(ROW(NULL)) IS NULL;
SELECT ROW(ROW(ROW(NULL))) IS NULL;
SELECT RECORD(NULL) IS NULL;
SELECT RECORD(RECORD(NULL)) IS NULL;
SELECT RECORD(RECORD(RECORD(NULL))) IS NULL;

DROP TABLE IF EXISTS tt;
CREATE TABLE tt (x INT);
INSERT INTO tt VALUES(NULL);
SELECT ROW(x) IS NULL FROM tt;
SELECT ROW(ROW(x)) IS NULL FROM tt;
SELECT ROW(ROW(ROW(x))) IS NULL FROM tt;

SELECT coalesce(ROW(NULL,NULL), ROW('no', 'bueno'));
SELECT CASE WHEN ROW(NULL,NULL) IS NULL THEN ROW('no', 'bueno') END;

DROP TABLE IF EXISTS test3;
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (x INT);

CREATE TABLE test3(x test2 NOT NULL);
INSERT INTO test3 VALUES (null);
INSERT INTO test3 VALUES (row(null));

DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN

DROP TABLE IF EXISTS test;
CREATE TABLE test (x INT, y INT);

INSERT INTO test VALUES (1, NULL), (NULL, 1), (NULL, NULL);
FOR r IN SELECT * FROM test
LOOP
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END LOOP;
END;
$$;

DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN

SELECT NULL INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;

SELECT ROW(NULL) INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;

END;
$$;