Re: quick q re execute & scope of new

On April 2, 2015, Scott Ribe wrote:
> On Apr 2, 2015, at 10:14 PM, Adrian Klaver <adrian.klaver@aklaver.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