Re: triggers and execute...

On Apr 29, 2009, at 4:14 AM, Jasen Betts wrote:

> On 2009-04-29, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>> <richard(dot)broersma(at)gmail(dot)com> wrote:
>>> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
>>> > 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.*)';
>>>> ...
>>>>
>>>> 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.*)"
>>>
>>> At this point I don't think that there is a way for this function to
>>> know the correct table type of new.* since page_access_... is still
>>> only a concatenated string. There there a way to cast new.* to the
>>> correct table type as part of this insert statement?
>>
>> Oh man, it just gets worse. I really need a simple elegant solution
>> here, because if I try to build the query by hand null inputs make
>> life a nightmare. I had built something like this:
>
> sounds like you want quote_literal() and/or coalesce()
>
> EXECUTE 'insert into '|| quote_ident($1) || ' (data) values (' ||
> coalesce(quote_literal( $2 ),'NULL');

I'm fairly certain that quote_literal doesn't work with null values,
hence the inclusion of quote_nullable() in 8.4. I ran into this same
issue when working on a pet project called pg_partitioner (http://github.com/mage2k/pg_partitioner/tree/master
). Since 8.4 obviously wasn't available for that yet I ended writing
my own quote_nullable(), pretty simple.

Scott,

I also couldn't come up with a dynamic way to use new.* so I ended up
just writing out the attribute names in my partition triggers, as
well. In fact, you may want to take a look at pg_partitioner. It
needs some polish but most of the basic functionality is there.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k