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.
В списке pgsql-novice по дате отправления:
Предыдущее
От: e-letterДата:
Сообщение: create table from regular expressions applied to rows of multiple tables