-- 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