Re: triggers and execute...

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');