Hello you cannot use variable as table or column name in direct query. look on execute statemen: http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN or don't use generic triggers. regards Pavel Stehule 2009/8/30 Stephen Cuppett <steve@xxxxxxxxxxx>: > Using PostgreSQL 8.4.0, I have the following generic trigger defined for use > by a collection of the same structured tables: > > CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$ > DECLARE > var_active BOOLEAN; > BEGIN > SELECT p.active INTO STRICT var_active FROM TG_TABLE_NAME pv INNER > JOIN pdfs p ON p.id = pv.pdf_id; > IF active THEN > RETURN NEW; > ELSE > RETURN NULL; > END IF; > END; > $BODY$ LANGUAGE 'plpgsql'; > > ERROR: syntax error at or near "$1" > LINE 1: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv.... > ^ > QUERY: SELECT p.active FROM $1 pv INNER JOIN pdfs p ON p.id = pv.pdf_id > CONTEXT: SQL statement in PL/PgSQL function "pdf_active_check" near line 4 > > When I try this kind of substitution with TEXT type variables, there isn't > any problem. I can imagine it has something to do with TG_TABLE_NAME being > of type NAME, but I can't find anything in the manual about the difference > or how to convert. Google wasn't much help either, but I figured this is > fairly trivial for those on this list... > > Stephen Cuppett > steve at cuppett dot com > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
