Re: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

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
>
>