0

Aim : To update the column tgt_tbl_count of recon_count1 table with count of each row of tgt_tbl Error : SQL Error [42601]: ERROR: syntax error at or near "$1".Where: SQL statement in PL/PgSQL function "sp_count_recon_refresh" near line 7

It is unable to resolve the f.tgt_tbl where count(*) is written. I tried by manually passing the f.tgt_tbl name and it is working. Can anyone please help!

create or replace procedure ca_adhoc_view.sp_count_recon_refresh()
    language plpgsql as
$$
declare 
    f record;
    res bigint;
BEGIN
    for f in select tgt_tbl from ca_adhoc_view.recon_count1
    LOOP
    update ca_adhoc_view.recon_count1 set tgt_tbl_count = (select count(*) from f.tgt_tbl);
    end loop;

END;
$$;
Sk2415
  • 41
  • 4
  • 2
    The error message is a bit confusing, but you cannot use a variable (= parameter) as table name. You need dynamic SQL. – Laurenz Albe Nov 03 '22 at 17:50
  • I tried using dynamic sql : execute 'update ca_adhoc_view.recon_count1 set tgt_tbl_count = (select count(*) from ' || f.tgt_tbl || ');'; and the query ran successfully. But in the output column records are not getting populated correctly. Its populating same for all the rows of tgt_tbl. – Sk2415 Nov 03 '22 at 19:10
  • That sounds like a different question. – Laurenz Albe Nov 04 '22 at 06:48
  • Of course it is setting all rows of the target table to the same value, and it has nothing to do with it being dynamic SQL. You do not have a `where` clause in the outer select. This will cause all rows to be updated to the same value. But like Laurenz says *that is another question* altogether. – Belayer Nov 04 '22 at 22:41

0 Answers0