Skip to content

Testing for an empty record variable in PostgreSQL

I recently wrote a simple anonymous function in PostgreSQL that would iterate over some table rows to get a column value, use that value to try to select a row from another table into a record variable, and then print that record variable if it was successfully assigned a row. That last part seemed pretty straightforward at first; simply check if that record variable IS NOT NULL:

DO $$
	DECLARE rmap record;
	 		rtarget record;
			symboltext varchar;
	BEGIN
		FOR rmap in SELECT * FROM rdemapping
		LOOP
			IF rmap.bbsymbolid IS NOT NULL THEN
				RAISE NOTICE 'bbsymbol: %', rmap.bbsymbol;
				symboltext := rmap.bbsymbol || 'xxx%';
				SELECT * INTO rtarget FROM catalog c where c.symbol like symboltext and c.source_id = 'RBN' LIMIT 1;
				RAISE NOTICE 'rtarget: %', rtarget;
				
				IF rtarget IS NOT NULL THEN
					RAISE NOTICE 'rtarget symbol: %', rtarget.symbol;
				END IF;
			END IF;
		END LOOP;
	END
$$;

However this doesn’t work because if the SELECT * INTO doesn’t produce a row, it assigns the rtarget record variable to an empty row. An empty row is not the same as null, and therefore the logic falls apart. The correct way to check if a record variable is assigned a valid row or not is to use the special variable FOUND like so:


				--IF rtarget IS NOT NULL THEN
				IF FOUND THEN
					RAISE NOTICE 'rtarget symbol: %', rtarget.symbol;
				END IF;

rtarget now gets printed only when assigned an actual row found in the catalog table. Nice!

Published inTips & Tricks

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *