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;