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