Trigger that will build one row from several rows
up vote
1
down vote
favorite
I mean:
INSERT INTO test VALUES(1, 'message'), (2, 'message'), (3, 'message);
triggering will cause the result in the table to look like this:
1, E'messagenmessagenmessage'
How to forbid inserting rows and then continue operations on the transferred data in the insert?
I am using postresgql.
postgresql plpgsql database-trigger
add a comment |
up vote
1
down vote
favorite
I mean:
INSERT INTO test VALUES(1, 'message'), (2, 'message'), (3, 'message);
triggering will cause the result in the table to look like this:
1, E'messagenmessagenmessage'
How to forbid inserting rows and then continue operations on the transferred data in the insert?
I am using postresgql.
postgresql plpgsql database-trigger
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I mean:
INSERT INTO test VALUES(1, 'message'), (2, 'message'), (3, 'message);
triggering will cause the result in the table to look like this:
1, E'messagenmessagenmessage'
How to forbid inserting rows and then continue operations on the transferred data in the insert?
I am using postresgql.
postgresql plpgsql database-trigger
I mean:
INSERT INTO test VALUES(1, 'message'), (2, 'message'), (3, 'message);
triggering will cause the result in the table to look like this:
1, E'messagenmessagenmessage'
How to forbid inserting rows and then continue operations on the transferred data in the insert?
I am using postresgql.
postgresql plpgsql database-trigger
postgresql plpgsql database-trigger
edited 2 days ago
a_horse_with_no_name
286k45431526
286k45431526
asked 2 days ago


Bocian
107
107
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago
add a comment |
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
In Postgres 10+ you can use a transition table in an AFTER trigger, see Example 43.7. Auditing with Transition Tables. Assuming that id
is a primary key (or unique):
create table my_table(id int primary key, message text);
you can update one and delete the remaining inserted rows:
create or replace function after_insert_on_my_table()
returns trigger language plpgsql as $$
declare r record;
begin
select
array_agg(id) as ids,
array_to_string(array_agg(message), e'n') as message
from new_table
into r;
update my_table
set message = r.message
where id = r.ids[1];
delete from my_table
where id = any(r.ids[2:]);
return null;
end $$;
In a trigger definition declare a transition table (as new_table
):
create trigger after_insert_on_my_table
after insert on my_table
referencing new table as new_table
for each statement
execute procedure after_insert_on_my_table();
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10.
Test it in db<>fiddle.
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
In Postgres 10+ you can use a transition table in an AFTER trigger, see Example 43.7. Auditing with Transition Tables. Assuming that id
is a primary key (or unique):
create table my_table(id int primary key, message text);
you can update one and delete the remaining inserted rows:
create or replace function after_insert_on_my_table()
returns trigger language plpgsql as $$
declare r record;
begin
select
array_agg(id) as ids,
array_to_string(array_agg(message), e'n') as message
from new_table
into r;
update my_table
set message = r.message
where id = r.ids[1];
delete from my_table
where id = any(r.ids[2:]);
return null;
end $$;
In a trigger definition declare a transition table (as new_table
):
create trigger after_insert_on_my_table
after insert on my_table
referencing new table as new_table
for each statement
execute procedure after_insert_on_my_table();
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10.
Test it in db<>fiddle.
add a comment |
up vote
0
down vote
accepted
In Postgres 10+ you can use a transition table in an AFTER trigger, see Example 43.7. Auditing with Transition Tables. Assuming that id
is a primary key (or unique):
create table my_table(id int primary key, message text);
you can update one and delete the remaining inserted rows:
create or replace function after_insert_on_my_table()
returns trigger language plpgsql as $$
declare r record;
begin
select
array_agg(id) as ids,
array_to_string(array_agg(message), e'n') as message
from new_table
into r;
update my_table
set message = r.message
where id = r.ids[1];
delete from my_table
where id = any(r.ids[2:]);
return null;
end $$;
In a trigger definition declare a transition table (as new_table
):
create trigger after_insert_on_my_table
after insert on my_table
referencing new table as new_table
for each statement
execute procedure after_insert_on_my_table();
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10.
Test it in db<>fiddle.
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
In Postgres 10+ you can use a transition table in an AFTER trigger, see Example 43.7. Auditing with Transition Tables. Assuming that id
is a primary key (or unique):
create table my_table(id int primary key, message text);
you can update one and delete the remaining inserted rows:
create or replace function after_insert_on_my_table()
returns trigger language plpgsql as $$
declare r record;
begin
select
array_agg(id) as ids,
array_to_string(array_agg(message), e'n') as message
from new_table
into r;
update my_table
set message = r.message
where id = r.ids[1];
delete from my_table
where id = any(r.ids[2:]);
return null;
end $$;
In a trigger definition declare a transition table (as new_table
):
create trigger after_insert_on_my_table
after insert on my_table
referencing new table as new_table
for each statement
execute procedure after_insert_on_my_table();
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10.
Test it in db<>fiddle.
In Postgres 10+ you can use a transition table in an AFTER trigger, see Example 43.7. Auditing with Transition Tables. Assuming that id
is a primary key (or unique):
create table my_table(id int primary key, message text);
you can update one and delete the remaining inserted rows:
create or replace function after_insert_on_my_table()
returns trigger language plpgsql as $$
declare r record;
begin
select
array_agg(id) as ids,
array_to_string(array_agg(message), e'n') as message
from new_table
into r;
update my_table
set message = r.message
where id = r.ids[1];
delete from my_table
where id = any(r.ids[2:]);
return null;
end $$;
In a trigger definition declare a transition table (as new_table
):
create trigger after_insert_on_my_table
after insert on my_table
referencing new table as new_table
for each statement
execute procedure after_insert_on_my_table();
In earlier versions of Postgres you can simulate a transition table introduced in Postgres 10.
Test it in db<>fiddle.
answered yesterday


klin
53.5k54573
53.5k54573
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373298%2ftrigger-that-will-build-one-row-from-several-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– eurotrash
2 days ago
Yes, exactly this I mean.
– Bocian
2 days ago
You can create on statement trigger that selects inserted rows and changes them to the format you desired and then deletes old records from the table.
– JustMe
2 days ago
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
2 days ago