On Thu, Jun 14, 2012 at 5:29 PM, Ken LaCrosse <klacross@raleys.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'tlike the variable used where a table named is expected and if I do use an EXECUTE then it doesn't like the
referenceto 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