From: | "Andrew J(dot) Kopciuch" <akopciuch(at)bddf(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Scott Ribe <scott_ribe(at)elevated-dev(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Subject: | Re: quick q re execute & scope of new |
Date: | 2015-04-03 06:00:54 |
Message-ID: | 201504030000.54849.akopciuch@bddf.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On April 2, 2015, Scott Ribe wrote:
> On Apr 2, 2015, at 10:14 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> > EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* ||
> > ')'
>
> Not that easy, strings are not quoted correctly, and null values are blank.
> Might be a function to translate new.* into a string as needed for this
> use, but I found another way based on Tom's suggestion:
>
> execute('insert into ' || tblnm || ' select $1.*') using new;
>
I've done similar in triggers for partition schemes, something like this :
EXECUTE 'INSERT INTO ' || partitionName || ' (SELECT ( masterTableName ' || quote_literal(NEW) || ').*)';
I can't remember the reference I found on line that helped me get there though.
The key is doing quote_literal on the "NEW", and casting it to a compatible type.
HTH,
Andy
From | Date | Subject | |
---|---|---|---|
Next Message | Octavi Fors | 2015-04-03 06:40:20 | Re: The case of PostgreSQL on NFS Server (II) |
Previous Message | Tom Lane | 2015-04-03 05:00:57 | Re: Error handling in C API function calls in a way that doesn't close client connection |