Skip site navigation (1) Skip section navigation (2)

Re: Table name as a variable and/or EXECUTE and NEW.*

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Ken LaCrosse <klacross(at)raleys(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Table name as a variable and/or EXECUTE and NEW.*
Date: 2012-06-15 23:18:52
Message-ID: CAK3UJRGMr=1HRDMVSSC27tQ8aNsqQwLBcCbfb7xgt+-q3d1zmQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, Jun 14, 2012 at 5:29 PM, Ken LaCrosse <klacross(at)raleys(dot)com> wrote:

> I'm trying to build a trigger which will dynamically create SQL statements in pgplsql that reference different table names (for child tables of a partitions parent table).
> Can I do this without performing an EXECUTE?

If the table your trigger is inserting into must be computed
dynamically, you probably need to use EXECUTE.

> If not how do I need to reference NEW.* in the SQL command?  Basically if I don't use an EXECUTE then postgres doesn't like the variable used where a table named is expected and if I do use an EXECUTE then it doesn't like the reference to NEW.*.
>
> Here's the code:
> CREATE OR REPLACE FUNCTION fp_eitem_price_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE
>     sql varchar;
>     child varchar := get_TblName(NEW.store_number, NEW.effective_date);
> BEGIN
> --    sql := 'INSERT INTO ' || child || ' VALUES (NEW.*);';
> --    EXECUTE(sql);
> --    INSERT INTO fp_ep_113_20120530 VALUES (NEW.*);

[snip]

Try the EXECUTE ... USING construct, available in Postgres 8.4+, like so:

  EXECUTE 'INSERT INTO ' || quote_ident(child) || ' SELECT $1.*' USING NEW;

You might want to specify the schema name of the child table
explicitly as well. See also this similar question on stackoverflow:
  http://stackoverflow.com/questions/1997337/inserting-new-from-a-generic-trigger-using-execute-in-pl-pgsql

Josh

In response to

Responses

pgsql-novice by date

Next:From: Jeff DavisDate: 2012-06-15 23:36:08
Subject: Re: create table from regular expressions applied to rows of multiple tables
Previous:From: Henry RamseyDate: 2012-06-15 23:08:41
Subject: Query Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group