Re: triggers and execute...

On Monday 27 April 2009 22:32:22 Scott Marlowe wrote:
> 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.*)';

What you want looks like this (thanks RhodiumToad):

'INSERT INTO page_access_' || part ||
'SELECT (' || quote_literal(textin(record_out(NEW))) || '::page_access).*;'

That's supposing you have a parent table named page_access, of course. And
casting this way has drawbacks too (which I can't recall at this moment), but
I've been using this live for maybe more than a year now without any problem.

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

Hack your way around, partitioning is not yet there "for real"...
--
dim