Re: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

[Stephen Cuppett <steve@xxxxxxxxxxx>]

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

On Sun, Aug 30, 2009 at 8:11 AM, Stephen Cuppett <steve@xxxxxxxxxxx> wrote:

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