Postgres Pro
Downloads
Home   >   mailing lists

Thread: quick q re execute & scope of new

quick q re execute & scope of new

From
Scott Ribe
Date:
Easier to give an example than describe the question, any chance of making something like this work?

execute('insert into ' || tblname || ' values(new.*)');

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

Re: quick q re execute & scope of new

From
Tom Lane
Date:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Easier to give an example than describe the question, any chance of making something like this work?
> execute('insert into ' || tblname || ' values(new.*)');

Not like that, for certain.  It might work to use EXECUTE ... USING new.*
or some variant of that.

            regards, tom lane

Re: quick q re execute & scope of new

From
Adrian Klaver
Date:
On 04/02/2015 08:30 PM, Scott Ribe wrote:
> Easier to give an example than describe the question, any chance of making something like this work?

You doing this in plpgsql trigger function I presume?

>
> execute('insert into ' || tblname || ' values(new.*)');
>

So
http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN:

EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* || ')'

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: quick q re execute & scope of new

From
Scott Ribe
Date:
On Apr 2, 2015, at 10:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Not like that, for certain.  It might work to use EXECUTE ... USING new.*
> or some variant of that.

Couldn't get a variant of that to work, but this did:

execute('insert into ' || tblnm || ' select $1.*') using new;

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

Re: quick q re execute & scope of new

From
Scott Ribe
Date:
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
astring as needed for this use, but I found another way based on Tom's suggestion: 

execute('insert into ' || tblnm || ' select $1.*') using new;

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice

Re: quick q re execute & scope of new

From
"Andrew J. Kopciuch"
Date:
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

Re: quick q re execute & scope of new

From
Adrian Klaver
Date:
On 04/02/2015 09:59 PM, 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.*
intoa string as needed for this use, but I found another way based on Tom's suggestion: 

My mistake for grabbing off the top of my head without testing my code.

>
> execute('insert into ' || tblnm || ' select $1.*') using new;
>


--
Adrian Klaver
adrian.klaver@aklaver.com