Re: triggers and execute... - Mailing list pgsql-general

From Jasen Betts
Subject Re: triggers and execute...
Date
Msg-id gt9cqm$rqd$1@reversiblemaps.ath.cx
Whole thread Raw
In response to triggers and execute...  (Scott Marlowe <scott.marlowe@gmail.com>)
Responses Re: triggers and execute...
List pgsql-general
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');



pgsql-general by date:

Previous
From: Michele Petrazzo - Unipex
Date:
Subject: pg_dump and pg_restore problem
Next
From: Erik Jones
Date:
Subject: Re: triggers and execute...