You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
254 lines
9.6 KiB
254 lines
9.6 KiB
-- allgemeine Trigger-Funtionen
|
|
-- [
|
|
create function set_history()
|
|
returns TRIGGER as '
|
|
DECLARE
|
|
usern text;
|
|
table_natts int4;
|
|
table_col_curs refcursor;
|
|
table_schema text;
|
|
table_schema_oid int4;
|
|
row_oid int4;
|
|
oid_bit int4;
|
|
oid_varbit int4;
|
|
col_name text;
|
|
col_names text := ''{'';
|
|
col_typ int4;
|
|
col_typ_name text;
|
|
col_types text := ''{'';
|
|
col_val text;
|
|
col_vals text := ''{'';
|
|
dummy_text RECORD;
|
|
dummy_bit RECORD;
|
|
query text;
|
|
BEGIN
|
|
usern := user;
|
|
|
|
select into table_natts relnatts from pg_catalog.pg_class where
|
|
relname=TG_RELNAME;
|
|
select into oid_bit oid from pg_catalog.pg_type where
|
|
typname=''bit'';
|
|
select into oid_varbit oid from pg_catalog.pg_type where
|
|
typname=''varbit'';
|
|
select into table_schema_oid relnamespace
|
|
from pg_catalog.pg_class where relname=TG_RELNAME;
|
|
select into table_schema nspname from pg_catalog.pg_namespace where
|
|
oid=table_schema_oid;
|
|
|
|
OPEN table_col_curs FOR select attname,atttypid from
|
|
pg_catalog.pg_attribute where attrelid=TG_RELID;
|
|
|
|
FOR i IN 1..7 LOOP
|
|
FETCH table_col_curs INTO col_name,col_typ;
|
|
END LOOP;
|
|
|
|
FOR i IN 1..table_natts LOOP
|
|
FETCH table_col_curs INTO col_name,col_typ;
|
|
col_names := col_names || quote_literal(col_name);
|
|
|
|
select into col_typ_name typname from pg_catalog.pg_type where
|
|
oid=col_typ;
|
|
|
|
col_types := col_types || quote_literal(col_typ_name);
|
|
|
|
IF col_typ = oid_bit OR
|
|
col_typ = oid_varbit THEN
|
|
query := ''select '' || col_name || '' as val from '' ||
|
|
table_schema || ''.'' || TG_RELNAME ||
|
|
'' where oid='';
|
|
ELSE
|
|
query := ''select '' || col_name || ''::text as val '' ||
|
|
''from '' || table_schema || ''.'' ||
|
|
TG_RELNAME || '' where oid='';
|
|
END IF;
|
|
|
|
IF TG_OP = ''INSERT'' OR
|
|
TG_OP = ''UPDATE'' THEN
|
|
query := query || NEW.oid;
|
|
row_oid := NEW.oid;
|
|
ELSE
|
|
query := query || OLD.oid;
|
|
row_oid := OLD.oid;
|
|
END IF;
|
|
|
|
IF col_typ = oid_bit OR
|
|
col_typ = oid_varbit THEN
|
|
FOR dummy_bit IN EXECUTE query LOOP
|
|
IF dummy_bit.val IS NOT NULL THEN
|
|
col_vals := col_vals ||
|
|
quote_literal(public.bit2text(dummy_bit.val));
|
|
END IF;
|
|
END LOOP;
|
|
ELSE
|
|
FOR dummy_text IN EXECUTE query LOOP
|
|
IF dummy_text.val IS NOT NULL THEN
|
|
col_vals := col_vals ||
|
|
quote_literal(dummy_text.val::text);
|
|
END IF;
|
|
END LOOP;
|
|
END IF;
|
|
|
|
IF i <> table_natts THEN
|
|
col_names := col_names || '','';
|
|
col_types := col_types || '','';
|
|
col_vals := col_vals || '','';
|
|
ELSE
|
|
col_names := col_names || ''}'';
|
|
col_types := col_types || ''}'';
|
|
col_vals := col_vals || ''}'';
|
|
END IF;
|
|
END LOOP;
|
|
|
|
-- IF query IS NOT NULL THEN
|
|
-- insert into public.history (username,time,tab,row_oid,at_time,
|
|
-- operation,col_names,col_types,
|
|
-- col_vals)
|
|
-- values (quote_literal(usern),
|
|
-- quote_literal(''now''),
|
|
-- quote_literal(TG_RELNAME),
|
|
-- quote_literal(row_oid),
|
|
-- quote_literal(TG_WHEN),
|
|
-- quote_literal(TG_OP),
|
|
-- quote_literal(col_names),
|
|
-- quote_literal(col_types),
|
|
-- quote_literal(col_vals));
|
|
-- END IF;
|
|
|
|
query := ''insert into public.history (username,time,tab,'' ||
|
|
''row_oid,at_time,operation,col_names,col_types,'' ||
|
|
''col_vals) values ('' ||
|
|
quote_literal(usern) || '','' ||
|
|
quote_literal(''now'') || '','' ||
|
|
quote_literal(TG_RELNAME) || '','' ||
|
|
quote_literal(row_oid) || '','' ||
|
|
quote_literal(TG_WHEN) || '','' ||
|
|
quote_literal(TG_OP) || '','' ||
|
|
quote_literal(col_names) || '','' ||
|
|
quote_literal(col_types) || '','' ||
|
|
quote_literal(col_vals) || '')'';
|
|
|
|
IF query IS NOT NULL THEN
|
|
EXECUTE query;
|
|
ELSE
|
|
RAISE NOTICE ''Found null query:'';
|
|
RAISE NOTICE ''query: %'', query;
|
|
RAISE NOTICE ''user: %'', usern;
|
|
RAISE NOTICE ''TG_RELNAME: %'', TG_RELNAME;
|
|
RAISE NOTICE ''row_oid: %'', row_oid;
|
|
RAISE NOTICE ''TG_WHEN: %'', TG_WHEN;
|
|
RAISE NOTICE ''TG_OP: %'', TG_OP;
|
|
RAISE NOTICE ''col_names: %'', col_names;
|
|
RAISE NOTICE ''col_vals: %'', col_vals;
|
|
RAISE NOTICE ''col_types: %'', col_types;
|
|
END IF;
|
|
|
|
IF TG_OP = ''INSERT'' OR
|
|
TG_OP = ''UPDATE'' THEN
|
|
RETURN NEW;
|
|
ELSE
|
|
RETURN OLD;
|
|
END IF;
|
|
END;
|
|
' language 'plpgsql';
|
|
|
|
create function create_valid_mask_before()
|
|
returns TRIGGER as '
|
|
/*
|
|
Aufgrund vin beschraenkungen in plpgsql muessen alle Bitmasken
|
|
mask heissen....zumindest in den Tabellen fuer die diese Triggerfunc
|
|
arbeiten soll....das gilt ebenso fuer jeden weiteren universellen
|
|
Trigger, sie koennen immer nur funktionieren wenn die Spalten
|
|
name wohldefiniert sind.
|
|
*/
|
|
DECLARE
|
|
err_msg text;
|
|
new_len int4;
|
|
old_len int4;
|
|
dummy RECORD;
|
|
sel text;
|
|
BEGIN
|
|
IF TG_OP <> ''UPDATE'' AND TG_OP <> ''INSERT'' THEN
|
|
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
|
|
RAISE EXCEPTION ''%'', err_msg;
|
|
END IF;
|
|
|
|
new_len := bit_length(NEW.mask);
|
|
IF TG_OP = ''UPDATE'' THEN
|
|
old_len := bit_length(OLD.mask);
|
|
ELSE
|
|
sel := ''select public.mask_len('' ||
|
|
quote_literal(TG_ARGV[0]) || '', '' ||
|
|
quote_literal(''mask'') || '',NULL) as len'';
|
|
FOR dummy IN EXECUTE sel LOOP
|
|
EXIT;
|
|
END LOOP;
|
|
IF NOT FOUND THEN
|
|
old_len := 0;
|
|
ELSE
|
|
old_len := dummy.len;
|
|
END IF;
|
|
END IF;
|
|
|
|
IF new_len < old_len THEN
|
|
NEW.mask := bit_set_len(NEW.mask, old_len);
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
' language 'plpgsql';
|
|
|
|
create function create_valid_mask_after()
|
|
returns TRIGGER as '
|
|
/*
|
|
Aufgrund von beschraenkungen in plpgsql muessen alle Bitmasken
|
|
mask heissen....zumindest in den Tabellen fuer die diese Triggerfunc
|
|
arbeiten soll....das gilt ebenso fuer jeden weiteren universellen
|
|
Trigger, sie koennen immer nur funktionieren wenn die Spalten
|
|
name wohldefiniert sind.
|
|
*/
|
|
DECLARE
|
|
err_msg text;
|
|
new_len int4;
|
|
old_len int4;
|
|
dummy RECORD;
|
|
sel text;
|
|
BEGIN
|
|
IF TG_OP <> ''UPDATE'' AND TG_OP <> ''INSERT'' THEN
|
|
err_msg := TG_NAME || '': called for wrong action '' || TG_OP;
|
|
RAISE EXCEPTION ''%'', err_msg;
|
|
END IF;
|
|
|
|
new_len := bit_length(NEW.mask);
|
|
IF TG_OP = ''UPDATE'' THEN
|
|
old_len := bit_length(OLD.mask);
|
|
ELSE
|
|
sel := ''select public.mask_len('' ||
|
|
quote_literal(TG_ARGV[0]) || '', '' ||
|
|
quote_literal(''mask'') || '',NULL) as len'';
|
|
FOR dummy IN EXECUTE sel LOOP
|
|
EXIT;
|
|
END LOOP;
|
|
IF NOT FOUND THEN
|
|
old_len := 0;
|
|
ELSE
|
|
old_len := dummy.len;
|
|
END IF;
|
|
END IF;
|
|
|
|
sel := ''select mask from '' || TG_ARGV[0] ||
|
|
'' where bit_length(mask)<>'' || new_len;
|
|
|
|
FOR dummy IN EXECUTE sel LOOP
|
|
EXECUTE ''update '' || TG_ARGV[0] || '' set mask='' ||
|
|
''mask::BIT('' || new_len || '')>>'' ||
|
|
new_len-old_len || '' where mask=B'' ||
|
|
quote_literal(public.bit2text(dummy.mask));
|
|
EXIT;
|
|
END LOOP;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
' language 'plpgsql';
|
|
|
|
-- ]
|
|
-- Ende allgemeine Triggerfunktionen
|