From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME |
Date: | 2010-05-13 10:51:22 |
Message-ID: | hsglja$ofu$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2010-05-11, Torsten Zühlsdorff <foo(at)meisterderspiele(dot)de> wrote:
> Hello,
>
> i have a problem with a trigger written in pl/pgsql.
>
> It looks like this:
>
> CREATE OR REPLACE FUNCTION versionize()
> RETURNS TRIGGER
> AS $$
> BEGIN
>
> NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
>
> /* not working line, just a stub:
> EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW;
> */
>
> RETURN NULL;
>
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE;
>
> The function should be used at different tables and is invoked before
> UPDATEs. Everything what happens is the function call of
> addContentRevision. After this call all data (with the updated revision
> column) should be stored in the table as a new row.
What many people have missed is that you want to INSERT when the DML
comnabd UPDATE is used.
for things like that usually a rule is used instead, but I can see where
that may be unsuitable for your needs. I found the following
to work on a simple test case.
The problem is that INSERT in PLPGSQL needs a fixed table-name, and
that "EXECUTE" can't use variable-names, and further that quote_literal
doesn't convert ROW variables into something that can be used in a
VALUES clause.
so, Here's what I did.
CREATE OR REPLACE FUNCTION versionize()
RETURNS TRIGGER
AS $$
BEGIN
-- Not havign a definition for addContentRevision
-- I had this line commented out during testing.
NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
EXECUTE 'INSERT INTO '||TG_TABLE_NAME||' SELECT (' ||
QUOTE_LITERAL(NEW) || '::' || TG_TABLE_NAME ||').*' ;
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL VOLATILE;
I take NEW, convert it to a quoted literal so I can use it in EXECUTE, cast it
to the apreopreiate row type and split it into columns using SELECT
and .*. That gets inserted.
you should probably use QUOTE_IDENT on the TG_TABLE_NAME and possibly
also use similarly quoted TG_SCHEMA_NAME
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Tolley | 2010-05-13 12:37:56 | Re: Greetings folks, dumb question maybe? |
Previous Message | Jasen Betts | 2010-05-13 10:09:51 | Re: Greetings folks, dumb question maybe? |