On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote: > On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma > <richard.broersma@xxxxxxxxx> wrote: > > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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: > > q = 'insert into '||schem||'.page_access_'||part||' values ( > '||new.paid||', > '''||new.timestamp||''', > '||new.total_time||', > '''||new.http_host||''', > '''||new.php_self||''', > '''||new.query_string||''', > '''||new.remote_addr||''', > '''||new.logged_in||''', > '||new.uid||', > '''||new.http_user_agent||''', > '''||new.server_addr||''', > '''||new.notes||''' > )'; > execute q; > > But if any of the fields referenced are null, the whole query string > is now null. So the next step is to use coalesce to build a query > string? That get insane very quickly. There's got to be some > quoting trick or something to let me use new.*, please someone see > this and know what that trick is. Well, you can add in piles of COALESCE, but that way madness lies. Instead, use dollar quoting, the appropriate quote_*() functions, and this: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
