From: | Ken LaCrosse <klacross(at)raleys(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Table name as a variable and/or EXECUTE and NEW.* |
Date: | 2012-06-15 00:29:31 |
Message-ID: | CAMuvqVzePLJi=HsE0rMcZbW9i2d=c3fNBaG-r+Of=o7WBb-VAA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi All,
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
------------------------------------------------
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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alessandro Gagliardi | 2012-06-15 18:11:17 | Planning a Large RDBMS |
Previous Message | e-letter | 2012-06-14 22:22:50 | create table from regular expressions applied to rows of multiple tables |