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.
Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49f82a8c129742043099112! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
