variable table names in trigger functions

Поиск
Список
Период
Сортировка
От Hugo
Тема variable table names in trigger functions
Дата
Msg-id 47A21EA0.4030401@nieuwenhuys.com
обсуждение исходный текст
Список pgsql-general
Hi all,

I've written a simple trigger function to store the old row in another table to log the data:

CREATE FUNCTION logusers() RETURNS trigger AS $$
    BEGIN
    INSERT INTO log.users SELECT FROM data.users WHERE id=OLD.id;
    RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.users
    FOR EACH ROW EXECUTE PROCEDURE logusers();

This works but I whould like to make a function without the table names hardcoded in it so it's usable for other
tables.
I tried this:

CREATE FUNCTION log() RETURNS trigger AS $$
    DECLARE
    log_table name  := 'log.'||TG_TABLE_NAME;
    data_table name := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;
     BEGIN
    INSERT INTO log_table SELECT FROM data_table WHERE id=OLD.id;
    RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.users
    FOR EACH ROW EXECUTE PROCEDURE log();

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.posts
    FOR EACH ROW EXECUTE PROCEDURE log();

But this doesn't work.
Any ideas on how to do this?

Thanks,
Hugo



В списке pgsql-general по дате отправления:

Предыдущее
От: "Adam Rich"
Дата:
Сообщение: Re: Oracle Analytical Functions
Следующее
От: Erik Jones
Дата:
Сообщение: Re: warm standby examples.