Special variable FOUND not set properly?

When assigning my variable _variable, I need to check whether the query returned any rows. "No row" is converted to null, but I want to keep the previous value instead in this case. While an actual null value should be assigned.

I use the special PL/pgSQL variable FOUND:

do $$
declare
    _variable bigint = 100;
    _tmp bigint;
begin
    drop table if exists tTemp;
    create temp table tTemp(id bigint, value bigint);

    insert into tTemp(id, value) values (1,1), (2, null);

    _tmp := _variable;  -- backup value

/*  -- first variant - doesn't work

    _variable := (select value from tTemp where id = -1);
    raise notice '%', FOUND;  -- FOUND is true (?!)
*/
    -- second variant
    select value
    into _variable
    from tTemp
    where id = -1;

    raise notice '%', FOUND;  -- FOUND is false

    -- roll back assignment if no row was found
    if not FOUND then
        _variable := _tmp;
    end if;
end;
$$;

Why is FOUND true in the first variant?

Is there a simpler way to avoid overwriting _variable with null?

COALESCE would also catch an actual null value, but I only want to catch "no row" - which is indistinguishable after being converted to null.

Solution

Explanation

Why is FOUND true in the first variant?

A plain assignment with := or = does not set the special variable FOUND.
The manual lists all events that set FOUND.

In your first variant FOUND still reflects the result from the preceding INSERT command.

GET DIAGNOSTICS catches more cases (incl. nested SELECT). But "no row" converted to a null value also counts as "1 row processed", so you get a row count of 1 regardless. See:

Dynamic SQL (EXECUTE) as condition for IF statement

There is a hybrid form for assignments, that sets the DIAGNOSTIC value as desired: 0 for "no row". (But still no effect on FOUND.)

_var := value FROM tbl WHERE id = -1;

See:

Store query result in a PL/pgSQL variable

You could work with that. But rather stick to the cleaner SELECT INTO. Or consider this ...

Solution

Is there a simpler way to avoid overwriting _variable with null?

Yes, you can use the UNION ALL "hack". It's almost, but not quite, the same as COALESCE. This variant distinguishes between a genuine null value and "no row":

DO
$do$
DECLARE
   _var int := 100;
BEGIN
   SELECT value FROM tbl WHERE id = -1
   UNION ALL
   SELECT _var
   INTO _var;
   
   RAISE NOTICE '%', _var;
END
$do$;

SELECT ... INTO (without STRICT) assigns values from the first returned row (discarding the rest). If the payload query returns no row, the second leg of the UNION ALL query kicks in: SELECT _var. Voilá.

Strictly speaking, there is no formal guarantee without ORDER BY. See:

Are results from UNION ALL clauses always appended in order?

So if you are going to bet your neck on this:

DO
$do$
DECLARE
   _var int := 100;
BEGIN
   SELECT value, 1 AS rnk FROM tbl WHERE id = -1
   UNION ALL
   SELECT _var, 2
   ORDER BY rnk
   INTO _var;
   
   RAISE NOTICE '%', _var;
END
$do$;

Additional columns are discarded in this assignment, just like additional rows.

fiddle