The following bug has been logged on the website:
Bug reference: 18190
Logged by: Karl Pinc
Email address: kop@karlpinc.com
PostgreSQL version: 15.4
Operating system: Linux
Description:
Nothing is said about how to compare entire RECORD type variables, or ROW
type variables.
This leads to surprising behavior in the code below. The row in table "foo"
that has foo.col2 = NULL does not raise an error, but the row that has
foo.col2 = 1 does.
CREATE OR REPLACE FUNCTION checktime (tbl TEXT, col TEXT)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
qry TEXT;
problem RECORD := NULL;
BEGIN
qry := '
SELECT *
FROM ' || quote_ident(tbl) || '
WHERE ' || quote_ident(col) || '::TIME <> $tim$00:00:00$tim$';
EXECUTE qry INTO problem;
IF problem IS NOT NULL THEN
RAISE EXCEPTION data_exception USING
MESSAGE = tbl
|| ' has a time part in column '
|| col
|| ' that is not 00:00:00'
, DETAIL = 'The row is: ' || problem;
END IF;
END;
$$;
CREATE TABLE foo (stamp TIMESTAMP WITHOUT TIME ZONE, col2 INT NULL);
INSERT INTO foo (stamp, col2) VALUES ('1979-01-01 00:01:00', NULL);
SELECT checktime('foo', 'stamp');
TRUNCATE foo;
INSERT INTO foo (stamp, col2) VALUES ('1979-01-01 00:01:00', 1);
SELECT checktime('foo', 'stamp');
Probably the right way to write the code is with GET DIAGNOSTICS..., but
that's neither here nor there.
Or maybe an error should be raised when trying to use IS NULL with RECORD
type variables. Or the code needs changing so IS NULL/IS NOT NULL works
with RECORD type variables. Or something else needs doing.
Note that I've not tried anything with ROW type variables but it seems worth
mentioning them since it's not obvious how to tell if they've ever been
assigned a value, which is vaguely related to the intention of the code
above. Likewise, equality comparisons on ROWs and RECORDs (not mixed) seem
like they should work, but what of other comparisions?
If the issue is documentation, the PL/pgSQL docs should link to the
composite type docs, or the expression docs, or type conversion, or where
ever the current documentation is that I can't find or that needs to be
written.
