Postgresql For Loop Problems :(

Question

I wanted to make a table that sanity checked record integrity for any duplications among my db.

I have a table currently with object names (tables) and their primary keys:

I want to create a procedure that loops through those objects with their keys, and inserts into a separate table the count of duplicates:

below is my code, but I've never done anything like this before and am new to postgres. What I have is from hours of googling/researching but every time I get closer I get a new error and am quite stuck :( Any insights would be greatly appreciated.

My newest error is I believe from my quote_ident(object_names). I don't want to query the column as postgres is reading it, I'd want that to be a raw string:

code:

do $$
declare
object_names varchar;
keys varchar;
rec record;
begin 
    for rec in select object_name, key from mfr_incentives.public.t_jh_dup_check
        loop
        object_names = rec.object_name;
        keys = rec.key;
         execute 'insert into mfr_incentives.public.t_jh_dup_check_final_output
                    select * from 
                        (select ' || quote_ident(object_names) || ', ' || quote_ident(keys) || ', ' || ' count(*), current_date from 
                                ( select ' || keys || ', count(*)
                                  from ' || object_names ||
                                  ' group by ' || keys || ' having count(*) > 1
                                ) a
                         ) a';
        end loop;
end; 
$$;

Jonathan Hodge · Accepted Answer · 2022-09-16 03:08:40Z

Found out my problem!

Being unfamiliar with the topic I finally found that I wanted quote_literal() instead of quote_ident().

The below works:

create or replace procedure public.proc_jh_dup_check()
language plpgsql
--IT WORKS NOW
as $$
declare
rec record;
begin 
    for rec in select object_name, key from mfr_incentives.public.t_jh_dup_check
        loop
         execute 'insert into mfr_incentives.public.t_jh_dup_check_final_output
                    select * from 
                        (select ' || quote_literal(rec.object_name) || ', ' || quote_literal(rec.key) || ', ' || ' count(*), current_date from 
                                ( select ' || rec.key || ', count(*)
                                  from ' || rec.object_name ||
                                  ' group by ' || rec.key || ' having count(*) > 1
                                ) a
                         ) a';
        end loop;
end; 
$$;

Collectives™ on Stack Overflow

Postgresql For Loop Problems :(

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related