Re: triggers and execute...

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

> On 2009-04-29, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>> <richard.broersma@gmail.com> wrote:
>>> On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.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