/** * Creates trigger and trigger function required for versioning the table. * * @param p_schema The table schema * @param p_table The table name * @param p_key_col The unique non-compostite integer column key. * @return If creating the functions was successful. * @throws RAISE_EXCEPTION If the table is not versioned */ CREATE OR REPLACE FUNCTION ver_create_version_trigger( p_schema NAME, p_table NAME, p_key_col NAME ) RETURNS BOOLEAN AS $$ DECLARE v_revision_table TEXT; v_sql TEXT; v_trigger_name VARCHAR; v_column_name NAME; v_column_update TEXT; BEGIN IF NOT @extschema@._ver_is_table_versioned(p_schema, p_table) THEN RAISE EXCEPTION 'Table %.% is not versioned', quote_ident(p_schema), quote_ident(p_table); END IF; v_revision_table := @extschema@.ver_get_version_table_full(p_schema, p_table); v_column_update := ''; FOR v_column_name IN SELECT att_name AS column_name FROM unnest(@extschema@._ver_get_table_columns(p_schema || '.' || p_table)) LOOP IF v_column_name = p_key_col THEN CONTINUE; END IF; IF v_column_update != '' THEN v_column_update := v_column_update || E',\n '; END IF; v_column_update := v_column_update || quote_ident(v_column_name) || ' = NEW.' || quote_ident(v_column_name); END LOOP; v_sql := $template$ CREATE OR REPLACE FUNCTION %revision_table%() RETURNS trigger AS $TRIGGER$ DECLARE v_revision @extschema@.revision.id%TYPE; v_last_revision @extschema@.revision.id%TYPE; v_table_id @extschema@.versioned_tables.id%TYPE; BEGIN BEGIN SELECT max(VER.revision) INTO v_revision FROM _changeset_revision VER; IF v_revision IS NULL THEN RAISE EXCEPTION 'Versioning system information is missing'; END IF; EXCEPTION WHEN undefined_table THEN RAISE EXCEPTION 'To begin editing %full_table_name% you need to create a revision'; END; SELECT VTB.id INTO v_table_id FROM @extschema@.versioned_tables VTB WHERE VTB.table_name = %table_name% AND VTB.schema_name = %schema_name%; IF v_table_id IS NULL THEN RAISE EXCEPTION 'Table versioning system information is missing for %full_table_name%'; END IF; IF NOT EXISTS ( SELECT TRUE FROM @extschema@.tables_changed WHERE table_id = v_table_id AND revision = v_revision ) THEN INSERT INTO @extschema@.tables_changed(revision, table_id) VALUES (v_revision, v_table_id); END IF; IF (TG_OP <> 'INSERT') THEN SELECT _revision_created INTO v_last_revision FROM %revision_table% WHERE %key_col% = OLD.%key_col% AND _revision_expired IS NULL; IF v_last_revision = v_revision THEN IF TG_OP = 'UPDATE' AND OLD.%key_col% = NEW.%key_col% THEN UPDATE %revision_table% SET %revision_update_cols% WHERE %key_col% = NEW.%key_col% AND _revision_created = v_revision AND _revision_expired IS NULL; RETURN NEW; ELSE DELETE FROM %revision_table% WHERE %key_col% = OLD.%key_col% AND _revision_created = v_last_revision; END IF; ELSE UPDATE %revision_table% SET _revision_expired = v_revision WHERE %key_col% = OLD.%key_col% AND _revision_created = v_last_revision; END IF; END IF; IF( TG_OP <> 'DELETE') THEN INSERT INTO %revision_table% SELECT v_revision, NULL, NEW.*; RETURN NEW; END IF; RETURN NULL; END; $TRIGGER$ LANGUAGE plpgsql SECURITY DEFINER; $template$; v_sql := REPLACE(v_sql, '%schema_name%', quote_literal(p_schema)); v_sql := REPLACE(v_sql, '%table_name%', quote_literal(p_table)); v_sql := REPLACE(v_sql, '%full_table_name%', quote_ident(p_schema) || '.' || quote_ident(p_table)); v_sql := REPLACE(v_sql, '%key_col%', quote_ident(p_key_col)); v_sql := REPLACE(v_sql, '%revision_table%', v_revision_table); v_sql := REPLACE(v_sql, '%revision_update_cols%', v_column_update); EXECUTE v_sql; SELECT @extschema@._ver_get_version_trigger(p_schema, p_table) INTO v_trigger_name; EXECUTE 'DROP TRIGGER IF EXISTS ' || v_trigger_name || ' ON ' || quote_ident(p_schema) || '.' || quote_ident(p_table); EXECUTE 'CREATE TRIGGER ' || v_trigger_name || ' AFTER INSERT OR UPDATE OR DELETE ON ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || ' FOR EACH ROW EXECUTE PROCEDURE ' || v_revision_table || '()'; EXECUTE 'ALTER FUNCTION ' || v_revision_table || '() ' || 'OWNER TO ' || @extschema@._ver_get_table_owner((p_schema || '.' || p_table)::REGCLASS); RETURN TRUE; END; $$ LANGUAGE plpgsql;