1 Answer
- Newest
- Most votes
- Most comments
4
Here's an example
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT db_nm, tbl_nm FROM Tbl_Lookup WHERE dupcheck_ind <> 'Y'
LOOP
-- Check for duplicates in the table
IF EXISTS (SELECT 1
FROM (SELECT COUNT(*)
FROM rec.db_nm || '.' || rec.tbl_nm
GROUP BY your_unique_key_columns
HAVING COUNT(*) > 1) AS duplicates)
THEN
-- DML1: Perform clean-up task 1, e.g., delete duplicates
EXECUTE format('DELETE FROM %I.%I WHERE ...;', rec.db_nm, rec.tbl_nm);
-- DML2: Perform clean-up task 2
EXECUTE format('UPDATE %I.%I SET ...;', rec.db_nm, rec.tbl_nm);
-- DML3: Perform clean-up task 3
EXECUTE format('INSERT INTO %I.%I (...) VALUES (...);', rec.db_nm, rec.tbl_nm);
END IF;
END LOOP;
END;
$$;
replace your_unique_key_columns with the appropriate column names used to identify duplicates Customize the DML1, DML2, and DML3 statements as needed for your specific clean-up tasks.
This stored procedure uses dynamic SQL with EXECUTE format() to perform the clean-up tasks on the tables specified in the lookup table. The IF EXISTS statement checks for duplicates based on the specified unique key columns. If duplicates are found, the procedure executes the clean-up tasks (DML1, DML2, and DML3) for that table.
Relevant content
- Accepted Answerasked 3 months ago
- Accepted Answerasked 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 3 years ago