quick q re execute & scope of new

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

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

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

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

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

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

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;

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

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

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

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

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.* into a 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

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