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

That worked great Josh. Woohoo!

Thanks
------------------------------------------------

Ken LaCrosse | Enterprise Architect
500 West Capitol Avenue
West Sacramento, California 95605-2696
P: 916.373.6571 | F: 916.373.6553
E: klacross <klacross(at)raleys(dot)com>@raleys.com <rjacobsen(at)raleys(dot)com>

*IT'S A MOM'S WORLD*
We're just here to help.™ Visit www.raleys.com for blogs, recipes and
savings.

On Fri, Jun 15, 2012 at 4:18 PM, Josh Kupershmidt <schmiddy(at)gmail(dot)com>wrote:

> 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
>

Warning: this e-mail may contain information proprietary to Raley's
and is intended only for the use of the intended recipient(s).
If the read of this message is not an intended recipient,
you are hereby notified that you have received this message in
error and that any review, dissemination, distribution, or copying
of this message is strictly prohibited. If you have received this message
in error, please notify the sender immediately.