pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[Trigger] Help needed with NEW.* and TG_TABLE_NAME
9+ messages / 6 participants
[nested] [flat]
* [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-10 09:35 =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
0 siblings, 1 reply; 9+ messages in thread
From: Torsten Zühlsdorff @ 2010-05-10 09:35 UTC (permalink / raw)
To: pgsql-sql
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 after
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.
My problem: the aim-table is not static. It's just always the table
which invoked the trigger. The trigger itself could be called at many
tables. I've tried some other ways of expressing the INSERT but nothing
worked:
- 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*'
- INSERT INTO TG_TABLE_NAME SELECT NEW.*
- EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW;
Do you have any hints?
Greetings from Germany,
Torsten
^ permalink raw reply [nested|flat] 9+ messages in thread
* [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-11 12:31 =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
0 siblings, 2 replies; 9+ messages in thread
From: Torsten Zühlsdorff @ 2010-05-11 12:31 UTC (permalink / raw)
To: pgsql-sql
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.
My problem: the aim-table is not static. It's just always the table
which invoked the trigger. The trigger itself could be called at many
tables. I've tried some other ways of expressing the INSERT but nothing
worked:
- 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*'
- INSERT INTO TG_TABLE_NAME SELECT NEW.*
- EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW;
Do you have any hints?
Greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.
^ permalink raw reply [nested|flat] 9+ messages in thread
* [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-11 12:36 =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
0 siblings, 0 replies; 9+ messages in thread
From: Torsten Zühlsdorff @ 2010-05-11 12:36 UTC (permalink / raw)
To: pgsql-sql
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.
My problem: the aim-table is not static. It's just always the table
which invoked the trigger. The trigger itself could be called at many
tables. I've tried some other ways of expressing the INSERT but nothing
worked:
- 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*'
- INSERT INTO TG_TABLE_NAME SELECT NEW.*
- EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW;
Do you have any hints?
Greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-11 14:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2010-05-11 14:23 UTC (permalink / raw)
To: Torsten Zühlsdorff <foo@meisterderspiele.de>; +Cc: pgsql-sql
=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de> writes:
> 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;
This seems like the hard way. Why don't you just RETURN NEW and let the
normal insertion happen?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-11 14:26 =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Torsten Zühlsdorff @ 2010-05-11 14:26 UTC (permalink / raw)
To: pgsql-sql
Tom Lane schrieb:
> =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de> writes:
>> 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;
>
> This seems like the hard way. Why don't you just RETURN NEW and let the
> normal insertion happen?
The trigger catches an UPDATE, not an INSERT. I need the old and the new
row, because this should emulate revision-control of the content.
Greetings,
Torsten
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-11 22:06 Stuart <sfbarbee@earthlink.net>
parent: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
0 siblings, 0 replies; 9+ messages in thread
From: Stuart @ 2010-05-11 22:06 UTC (permalink / raw)
To: Torsten Zühlsdorff <foo@meisterderspiele.de>; pgsql-sql
Torsten,
Usually, the “insert ... (select ...)” has the select portion enclosed in parenthesis. Don't know if solution is that simple but did not see it in your examples. It may be worth a try.
Stuart
----- Original message -----
> 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 after
> 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.
>
> My problem: the aim-table is not static. It's just always the table
> which invoked the trigger. The trigger itself could be called at many
> tables. I've tried some other ways of expressing the INSERT but nothing
> worked:
> - 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*'
> - INSERT INTO TG_TABLE_NAME SELECT NEW.*
> - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW;
>
> Do you have any hints?
>
> Greetings from Germany,
> Torsten
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-12 06:00 silly sad <sad@bankir.ru>
parent: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
0 siblings, 0 replies; 9+ messages in thread
From: silly sad @ 2010-05-12 06:00 UTC (permalink / raw)
To: pgsql-sql
On 05/11/10 18:26, Torsten Zühlsdorff wrote:
>
>
> Tom Lane schrieb:
>> =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de> writes:
>>> 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;
>>
>> This seems like the hard way. Why don't you just RETURN NEW and let the
>> normal insertion happen?
>
> The trigger catches an UPDATE, not an INSERT. I need the old and the new
> row, because this should emulate revision-control of the content.
just set whatever value u want to the fields of the row NEW.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-13 10:51 Jasen Betts <jasen@xnet.co.nz>
parent: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
1 sibling, 1 reply; 9+ messages in thread
From: Jasen Betts @ 2010-05-13 10:51 UTC (permalink / raw)
To: pgsql-sql
On 2010-05-11, Torsten Zühlsdorff <foo@meisterderspiele.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [Trigger] Help needed with NEW.* and TG_TABLE_NAME
@ 2010-05-17 08:57 Torsten Zühlsdorff <foo@meisterderspiele.de>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 9+ messages in thread
From: Torsten Zühlsdorff @ 2010-05-17 08:57 UTC (permalink / raw)
To: pgsql-sql
Jasen Betts schrieb:
> On 2010-05-11, Torsten Zühlsdorff <foo@meisterderspiele.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
That's an quite interesting solution. I've tested it in several ways and
it works like i want. :)
Thank you very much - and every other responder - for your time.
Greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-05-17 08:57 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-10 09:35 [Trigger] Help needed with NEW.* and TG_TABLE_NAME =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
2010-05-11 22:06 ` Stuart <sfbarbee@earthlink.net>
2010-05-11 12:31 [Trigger] Help needed with NEW.* and TG_TABLE_NAME =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
2010-05-11 14:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-11 14:26 ` =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
2010-05-12 06:00 ` silly sad <sad@bankir.ru>
2010-05-13 10:51 ` Jasen Betts <jasen@xnet.co.nz>
2010-05-17 08:57 ` Torsten Zühlsdorff <foo@meisterderspiele.de>
2010-05-11 12:36 [Trigger] Help needed with NEW.* and TG_TABLE_NAME =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox