Search Postgresql Archives

Re: triggers and execute...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux