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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
