Getting error in EXECUTE statement when a null value is encountered

Question

I have created following stored procedure to update recon_dashboard table.

create or replace procedure ca_adhoc_view.sp_count_recon_refresh()
    language plpgsql as
$$
declare 
    f record;
BEGIN
    for f in select alvid from ca_adhoc_view.recon_dashboard
    loop
    raise notice '% alv',f.alv;
    execute 'update ca_adhoc_view.recon_dashboard set alv_count = 
            (select count(*) from ' || f.alv  || ')

     where id='|| f.id;
end loop;
END;
$$;

It works fine unless there is a null value in the alv, then it throws an error.
I tried to check for null like:

create or replace procedure ca_adhoc_view.sp_count_recon_refresh()
    language plpgsql as
$$
declare 
    f record;
BEGIN
    for f in select alv,id from ca_adhoc_view.recon_dashboard
    loop
    raise notice '% alv',f.alv;
    execute 'update ca_adhoc_view.recon_dashboard set alv_count = 
    (IF '||f.alv||' is not null
        then (select count(*) from ' || f.alv  || ')
    END IF;)
     where id='|| f.id;
end loop;
END;
$$;

But it throws error when I try to call the SP.

Error: SQL Error [42601]: ERROR: syntax error at or near "ca_view"
  Where: PL/pgSQL function "sp_count_recon_refresh" line 7 at execute statement

In error "ca_view" is a recod. alv column has values like 'ca_view.table1', 'ca_view.table2' and so on.

`alvid` or `alv, id`? The former looks like a typo. Please proofread your question. Postgres version? Core definition of `ca_adhoc_view.recon_dashboard` (`CREATE TABLE` statement)? — Erwin Brandstetter, Nov 05 '22 at 07:50

Erwin Brandstetter · Answer 1 · 2022-11-05T10:06:49.400

This probably fixes your problems:

CREATE OR REPLACE PROCEDURE ca_adhoc_view.sp_count_recon_refresh()
  LANGUAGE plpgsql AS
$func$
DECLARE
   f record;
BEGIN
   FOR f IN
      SELECT alv, id FROM ca_adhoc_view.recon_dashboard
   LOOP
      RAISE NOTICE '% alv', f.alv;
      IF f.alv IS NOT NULL THEN
         EXECUTE format(
            'UPDATE ca_adhoc_view.recon_dashboard
             SET    alv_count = (SELECT count(*) FROM %I)
             WHERE  id = $1', f.alv)
         USING   f.id;
      END IF;
   END LOOP;
END
$func$;

"Problems" (plural). Besides defending against a NULL value for the table name with proper syntax, this also prevents SQL injection. Your original is wide open there. Related:

That said, it would be more efficient to run a single UPDATE instead of updating one row per loop iteration. Consider building and executing a single statement.

Getting error in EXECUTE statement when a null value is encountered

1 Answers1