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

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

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 (view raw or flat)
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.

Responses

pgsql-novice by date

Next:From: Alessandro GagliardiDate: 2012-06-15 18:11:17
Subject: Planning a Large RDBMS
Previous:From: e-letterDate: 2012-06-14 22:22:50
Subject: create table from regular expressions applied to rows of multiple tables

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