Scott Marlowe escribió:
> 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.
Agreed, it is ugly. I don't think there's a better way to do it though.
One thing you could try is getting the column names and types from the
catalogs to build the insert statement. That way you don't have to list
each column separately, and you don't need to fiddle with whether each
value needs quotes or not.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.