1

I need to create a function that will create a twin-table for every already existing table in the database. Inside the twin-table there has to be the same columns and their data types with 3 more added for auditing (when was the table modified (time and date), who modified it (user), modification typu (insert, update, delete)). Also for each source table, add a trigger that, after inserting, deleting, or modifying data, adds the corresponding record with new values ​​to the twin table. Iterating over tables requires the use of cursors.

I made the function and it seems to work fine, but the trigger cannot be inserted into it, it complains about incorrect syntax:

CREATE OR REPLACE FUNCTION copy_table() RETURNS boolean language plpgsql as $$

DECLARE
cursor1 CURSOR FOR SELECT table_name from information_schema.tables WHERE table_schema = 'public';
cursor2 CURSOR (key text) FOR SELECT column_name, data_type from information_schema.columns WHERE
    table_schema = 'public' and table_name = key ORDER BY ordinal_position;
tablename text;
tablename_new text;
columnname text;
data_type text;

BEGIN
OPEN cursor1;
    LOOP
    FETCH cursor1 INTO tablename;
    tablename_new = tablename || '_copy';
    IF NOT FOUND THEN EXIT;
    END IF;
    EXECUTE 'CREATE TABLE '||tablename_new||' ()';
OPEN cursor2(tablename);
    LOOP
    FETCH cursor2 INTO columnname, data_type;
    IF NOT FOUND THEN EXIT;
    END IF;
    EXECUTE 'ALTER TABLE '||tablename_new||' ADD '||columnname||' '||data_type||'';
    END LOOP;
CLOSE cursor2;

EXECUTE 'ALTER TABLE '||tablename_new||' ADD COLUMN operation text NOT NULL';
EXECUTE 'ALTER TABLE '||tablename_new||' ADD COLUMN stamp timestamp';
EXECUTE 'ALTER TABLE '||tablename_new||' ADD COLUMN userid text NOT NULL';
EXECUTE '
CREATE OR REPLACE FUNCTION audit() RETURNS TRIGGER as 
$body1$
BEGIN
IF (TG_OP = 'DELETE') THEN
    INSERT INTO public.'||tablename_new||' VALUES (OLD.*, TG_OP, now(), user);
ELSEIF (TG_OP = 'UPDATE') THEN
    INSERT INTO public.'||tablename_new||' VALUES (NEW.*, TG_OP, now(), user);
ELSEIF (TG_OP = 'INSERT') THEN
    INSERT INTO public.'||tablename_new||' VALUES (NEW.*, TG_OP, now(), user);
END IF;
END;
$body1$
language plpgsql;

CREATE TRIGGER audit_mod_table
    AFTER INSERT OR UPDATE OR DELETE ON public.'||tablename||'
    FOR EACH ROW EXECUTE FUNCTION audit();';

END LOOP;
CLOSE cursor1;
RETURN TRUE;
END; $$

Complains about the syntax here (specifically ' ' in 'DELETE', 'UPDATE', 'INSERT'), but without them the trigger doesn't work and thinks they are columns:

IF (TG_OP = 'DELETE') THEN
        INSERT INTO public.'||tablename_new||' VALUES (OLD.*, TG_OP, now(), user);
    ELSEIF (TG_OP = 'UPDATE') THEN
        INSERT INTO public.'||tablename_new||' VALUES (NEW.*, TG_OP, now(), user);
    ELSEIF (TG_OP = 'INSERT') THEN
        INSERT INTO public.'||tablename_new||' VALUES (NEW.*, TG_OP, now(), user);
    END IF;
Emzee
  • 11
  • 1
  • Try escaping the single quotes that are inside of other single quotes, by using double-single quotes https://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql – ELinda Jun 27 '23 at 15:53

0 Answers0