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@cuppett.com>:
> 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
>
>
