Обсуждение: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

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

Sorry, found my answer wrt "dynamic queries", etc.  Restructured trigger to look like this:

CREATE OR REPLACE FUNCTION pdf_active_check() RETURNS trigger AS $BODY$
    DECLARE
        var_curs1 refcursor;
        var_active BOOLEAN;
    BEGIN
        open var_curs1 FOR EXECUTE 'SELECT p.active FROM '||TG_TABLE_NAME||' pv INNER JOIN pdfs p ON p.id = pv.pdf_id WHERE p.active = true';
        FETCH var_curs1 INTO var_active;
        IF FOUND THEN
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END;
$BODY$ LANGUAGE 'plpgsql';

Sorry for the noise.

Stephen Cuppett
steve at cuppett dot com

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

On Sun, Aug 30, 2009 at 03:22:16PM +0200, Pavel Stehule wrote:
> 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.

You know, it would be really nice to have a way of dealing with this. I
was thinking, just like we have the OPERATOR() keyword for tokens that
wouldn't normally be considered operators, we could use TABLE() to
introduce table name where they wouldn't normally be recognised. Like:

SELECT a FROM TABLE($1);

TABLE is a reserved word, so there doesn't appear to be a backward
compatability problem. The argument would have to be a text constant,
or a bind parameter. Sure, it would disable query caching, but the
benefits in clarity seem worth it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

2009/8/30 Martijn van Oosterhout <kleptog@svana.org>:
> On Sun, Aug 30, 2009 at 03:22:16PM +0200, Pavel Stehule wrote:
>> 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.
>
> You know, it would be really nice to have a way of dealing with this. I
> was thinking, just like we have the OPERATOR() keyword for tokens that
> wouldn't normally be considered operators, we could use TABLE() to
> introduce table name where they wouldn't normally be recognised. Like:
>
> SELECT a FROM TABLE($1);
>
> TABLE is a reserved word, so there doesn't appear to be a backward
> compatability problem. The argument would have to be a text constant,
> or a bind parameter. Sure, it would disable query caching, but the
> benefits in clarity seem worth it.
>
> Have a nice day,

The core of this topic is plan and syntax query analysis - different
tables has different columns and indexes. So plan should be different
and some queries should be correct (or not) for different tables.

Theoretically we can define some "operator" table for plpgsql. This
operator ensure so query will be dynamic. But I am against. It add to
plpgsql some new alchemy. Now the plpgsql block schema is very clear
and protect young programmers against some basic error. Dynamic
queries should be slower - or faster. It is best when any developer
have to understand to differences between query and dynamic query
early - lot of magical things in plpgsql will be clean.

I disliked dynamic sql too. But with USING clause the working with it
is comfortable and clean:

sample:
EXECUTE 'SELECT * FROM ' || tabname::regclass || WHERE col=$1' USING var;

older variant
EXECUTE 'SELECT * FROM ' || quote_iden(tabname) || 'WHERE col='||
quote_literal(var) ...

regards
Pavel Stehule

> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
>> Please line up in a tree and maintain the heap invariant while
>> boarding. Thank you for flying nlogn airlines.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFKmoChIB7bNG8LQkwRAhd1AJ9HevaBdodmpJ1sKSOjyr+70d25cACfRRpl
> KDDTg2K8xopGGBIh8A1pBtg=
> =/dQN
> -----END PGP SIGNATURE-----
>
>