Trigger that will build one row from several rows
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
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
add a comment |
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
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– 404
Nov 19 '18 at 11:48
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
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
Nov 19 '18 at 13:57
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44
add a comment |
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
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
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 Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
edited Nov 19 '18 at 11:09
a_horse_with_no_name
294k46451544
294k46451544
asked Nov 19 '18 at 11:06
BocianBocian
157
asked Nov 19 '18 at 11:06
BocianBocian
157
asked Nov 19 '18 at 11:06
BocianBocian
157
157
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– 404
Nov 19 '18 at 11:48
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
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
Nov 19 '18 at 13:57
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44
add a comment |
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– 404
Nov 19 '18 at 11:48
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
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
Nov 19 '18 at 13:57
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– 404
Nov 19 '18 at 11:48
Should we assume the 3 combined 'message' strings are the combined values of the 3 rows from the INSERT statement?
– 404
Nov 19 '18 at 11:48
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
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
Nov 19 '18 at 13:57
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
Nov 19 '18 at 13:57
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44
add a comment |
1 Answer
1
active
oldest
votes
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 Nov 19 '18 at 23:24
klinklin
57.2k64879
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 Nov 19 '18 at 23:24
klinklin
57.2k64879
add a comment |
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 Nov 19 '18 at 23:24
klinklin
57.2k64879
add a comment |
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 Nov 19 '18 at 23:24
klinklin
57.2k64879
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 Nov 19 '18 at 23:24
klinklin
57.2k64879
answered Nov 19 '18 at 23:24
klinklin
57.2k64879
answered Nov 19 '18 at 23:24
klinklin
57.2k64879
answered Nov 19 '18 at 23:24
klinklin
57.2k64879
57.2k64879
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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?
– 404
Nov 19 '18 at 11:48
Yes, exactly this I mean.
– Bocian
Nov 19 '18 at 13:02
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
Nov 19 '18 at 13:57
I think that this cannot be done. I also don't understand the requirement.
– Laurenz Albe
Nov 19 '18 at 18:44