On Wed, Apr 29, 2009 at 4:23 AM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote: > On Apr 29, 2009, at 4:20 AM, Scott Marlowe wrote: > >> 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. > > > I think you could do this if you'd be using a PL-language that supported > reflection (on the NEW objects' type in this case). I can't say I know which > one does though, I've only been using PL/pgsql so far, but I'd guess > PL/Python, PL/Perl or PL/Java should be able to do the trick. Or plain C. > > AFAIK there's no way to dynamically list column names from a table-type > variable like NEW in PL/pgsql, which is why the above probably can't be done > any easier using PL/pgsql. It would be nice to be able to LOOP over a > variable like that or some similar method (I guess a more relational > approach where the columns would be available as a result set would be > preferred), especially if it'd be similarly easy to inspect the name and > type of each column. I'm really close to using coalesce to make this work, since I can't get the referenced at http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers to work. If there was some simple quoting trick to get the original (NEW.*) stuff to work I'd be gold. Either that or just implement this all in rules with a simple cron job that creates the new table as needed a week or so in advnace. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
