Hi,
i found a strange behavior and hope it’s not a pilot error.
PostgreSQL Version:
PostgreSQL 14.8, compiled by Visual C++ build 1914, 64-bit
Reproducer:
CREATE TABLE IF NOT EXISTS public.test
(
num integer NOT NULL,
prev integer
);
INSERT INTO public.test
VALUES (1, null),
(2, 1),
(3, 2);
CREATE OR REPLACE FUNCTION public.steps()
RETURNS SETOF test
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
nextVal public.test;
BEGIN
SELECT * INTO nextVal FROM public.test
WHERE prev = 2;
RAISE NOTICE 'nextVal IS NULL? % or nextVal IS NOT NULL? %', nextVal IS NULL, nextVal IS NOT NULL;
IF nextVal IS NOT NULL THEN
RETURN NEXT nextVal;
END IF;
END;
$BODY$;
SELECT * FROM steps();
-- returns: 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? t
ALTER TABLE public.test ADD COLUMN x xml;
SELECT * FROM steps();
-- returns: nothing ; expected 3, 2
-- NOTICE: nextVal IS NULL? f or nextVal IS NOT NULL? f
I reduced this from a rather complicate function until I stumbled upon the freshly added xml column causing the wrong results.
Sure it could be further condensed, but I hope it’s small enough for a report now.
Best wishes and keep up the great work
Wilm Hoyer