Re: Trouble using TG_TABLE_NAME in BEFORE INSERT OR UPDATE trigger

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(at)cuppett(dot)com> 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
>
>