Thread: Table name as a variable and/or EXECUTE and NEW.*
Hi All,
Ken LaCrosse | Enterprise Architect
IT'S A MOM'S WORLD
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 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.*);
RAISE NOTICE 'child=%', child;
INSERT INTO child VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Thanks
------------------------------------------------
500 West Capitol Avenue
IT'S A MOM'S WORLD
We're just here to help.™ Visit www.raleys.com for blogs, recipes and savings.
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.
That worked great Josh. Woohoo!
Ken LaCrosse | Enterprise Architect
IT'S A MOM'S WORLD
Thanks
------------------------------------------------
500 West Capitol Avenue
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@gmail.com> wrote:
On Thu, Jun 14, 2012 at 5:29 PM, Ken LaCrosse <klacross@raleys.com> wrote:If the table your trigger is inserting into must be computed
> 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?
dynamically, you probably need to use EXECUTE.[snip]
> 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.*);
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.