triggers and execute...

Поиск
Список
Период
Сортировка
От Scott Marlowe
Тема triggers and execute...
Дата
Msg-id dcc563d10904271332q5d1f2274w26eef4d70a5626fd@mail.gmail.com
обсуждение исходный текст
Ответы Re: triggers and execute...  (Scott Marlowe <scott.marlowe@gmail.com>)
Re: triggers and execute...  (Richard Broersma <richard.broersma@gmail.com>)
Re: triggers and execute...  (Dimitri Fontaine <dfontaine@hi-media.com>)
Список pgsql-general
OK, I'm hitting a wall here.  I've written this trigger for partitioning:

create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
    part text;
    q text;
BEGIN
    part = to_char(new."timestamp",'YYYYMMDD');
    q = 'insert into page_access_'||part||' values (new.*)';
    execute q;
    return null;
END;
$$ language plpgsql;
drop trigger page_access_insert_trigger on page_access cascade;
create trigger page_access_insert_trigger before insert or update on page_access
    for each row execute procedure page_access_insert_trigger();


When I create it and try to use it I get this error:
ERROR:  NEW used in query that is not in a rule
CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
PL/pgSQL function "page_access_insert_trigger" line 7 at EXECUTE statement

If I rewrite it to just write to that table:

create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
    part text;
    q text;
BEGIN
--    part = to_char(new."timestamp",'YYYYMMDD');
--    q = 'insert into page_access_'||part||' values (new.*)';
--    execute q;
    insert into page_access_20090427 values (new.*);
    return null;
END;
$$ language plpgsql;

It works.  So, how am I supposed to run it with dynamic table names?

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

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: PostgreSQL Object-Oriented Database?
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: triggers and execute...