Before insert trigger to replace new data postgres

Multi tool use
I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
(CASE
WHEN NEW.col1='111' THEN NEW.col1='aaa'
WHEN NEW.col1='222' THEN NEW.col1='bbb'
WHEN NEW.col1='333' THEN NEW.col1='ccc'
ELSE NEW.col1='error'
END);
return NEW;
END;
$BODY$
And that could be the before trigger (just current values should be affected, not all rows):
CREATE TRIGGER schema.table_replace
BEFORE INSERT
ON schema.table
EXECUTE PROCEDURE schema.table_replace();
To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
Could you give some help to make it running and creating the function and the trigger?
Thanks.
postgresql plpgsql database-trigger
add a comment |
I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
(CASE
WHEN NEW.col1='111' THEN NEW.col1='aaa'
WHEN NEW.col1='222' THEN NEW.col1='bbb'
WHEN NEW.col1='333' THEN NEW.col1='ccc'
ELSE NEW.col1='error'
END);
return NEW;
END;
$BODY$
And that could be the before trigger (just current values should be affected, not all rows):
CREATE TRIGGER schema.table_replace
BEFORE INSERT
ON schema.table
EXECUTE PROCEDURE schema.table_replace();
To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
Could you give some help to make it running and creating the function and the trigger?
Thanks.
postgresql plpgsql database-trigger
add a comment |
I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
(CASE
WHEN NEW.col1='111' THEN NEW.col1='aaa'
WHEN NEW.col1='222' THEN NEW.col1='bbb'
WHEN NEW.col1='333' THEN NEW.col1='ccc'
ELSE NEW.col1='error'
END);
return NEW;
END;
$BODY$
And that could be the before trigger (just current values should be affected, not all rows):
CREATE TRIGGER schema.table_replace
BEFORE INSERT
ON schema.table
EXECUTE PROCEDURE schema.table_replace();
To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
Could you give some help to make it running and creating the function and the trigger?
Thanks.
postgresql plpgsql database-trigger
I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
(CASE
WHEN NEW.col1='111' THEN NEW.col1='aaa'
WHEN NEW.col1='222' THEN NEW.col1='bbb'
WHEN NEW.col1='333' THEN NEW.col1='ccc'
ELSE NEW.col1='error'
END);
return NEW;
END;
$BODY$
And that could be the before trigger (just current values should be affected, not all rows):
CREATE TRIGGER schema.table_replace
BEFORE INSERT
ON schema.table
EXECUTE PROCEDURE schema.table_replace();
To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
Could you give some help to make it running and creating the function and the trigger?
Thanks.
postgresql plpgsql database-trigger
postgresql plpgsql database-trigger
edited Dec 30 '18 at 4:54


Barbaros Özhan
12.8k71632
12.8k71632
asked Dec 30 '18 at 0:22
user7113942user7113942
295
295
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
CASE
as a control structure is closed with END CASE
(whereas the expression is closed with just an END
). And in the branches there are statements, they need to be terminated by a semicolon.
Your LANGUAGE
is also misplaced. That belongs at the end. And you don't need the single quotes.
You can use an IF
to only do the replacement, when col2
is not null.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.col2 IS NOT NULL THEN
CASE
WHEN NEW.col1 = '111' THEN
NEW.col1 = 'aaa';
WHEN NEW.col1 = '222' THEN
NEW.col1 = 'bbb';
WHEN NEW.col1 = '333' THEN
NEW.col1 = 'ccc';
ELSE
NEW.col1 = 'error';
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
You also need to declare the trigger as a FOR EACH ROW
trigger for NEW
to work.
And a trigger name cannot be schema qualified.
CREATE TRIGGER table_replace
BEFORE INSERT
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_replace();
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
add a comment |
The main problem of your example is missing FOR EACH ROW
clause in your CREATE TRIGGER
statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE
statement.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '111' THEN 'aaa'
WHEN '222' THEN 'bbb'
WHEN '333' THEN 'ccc'
ELSE 'error' END CASE;
END IF;
RETURN NEW;
END;
$BODY$
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%2f53974370%2fbefore-insert-trigger-to-replace-new-data-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
CASE
as a control structure is closed with END CASE
(whereas the expression is closed with just an END
). And in the branches there are statements, they need to be terminated by a semicolon.
Your LANGUAGE
is also misplaced. That belongs at the end. And you don't need the single quotes.
You can use an IF
to only do the replacement, when col2
is not null.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.col2 IS NOT NULL THEN
CASE
WHEN NEW.col1 = '111' THEN
NEW.col1 = 'aaa';
WHEN NEW.col1 = '222' THEN
NEW.col1 = 'bbb';
WHEN NEW.col1 = '333' THEN
NEW.col1 = 'ccc';
ELSE
NEW.col1 = 'error';
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
You also need to declare the trigger as a FOR EACH ROW
trigger for NEW
to work.
And a trigger name cannot be schema qualified.
CREATE TRIGGER table_replace
BEFORE INSERT
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_replace();
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
add a comment |
CASE
as a control structure is closed with END CASE
(whereas the expression is closed with just an END
). And in the branches there are statements, they need to be terminated by a semicolon.
Your LANGUAGE
is also misplaced. That belongs at the end. And you don't need the single quotes.
You can use an IF
to only do the replacement, when col2
is not null.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.col2 IS NOT NULL THEN
CASE
WHEN NEW.col1 = '111' THEN
NEW.col1 = 'aaa';
WHEN NEW.col1 = '222' THEN
NEW.col1 = 'bbb';
WHEN NEW.col1 = '333' THEN
NEW.col1 = 'ccc';
ELSE
NEW.col1 = 'error';
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
You also need to declare the trigger as a FOR EACH ROW
trigger for NEW
to work.
And a trigger name cannot be schema qualified.
CREATE TRIGGER table_replace
BEFORE INSERT
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_replace();
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
add a comment |
CASE
as a control structure is closed with END CASE
(whereas the expression is closed with just an END
). And in the branches there are statements, they need to be terminated by a semicolon.
Your LANGUAGE
is also misplaced. That belongs at the end. And you don't need the single quotes.
You can use an IF
to only do the replacement, when col2
is not null.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.col2 IS NOT NULL THEN
CASE
WHEN NEW.col1 = '111' THEN
NEW.col1 = 'aaa';
WHEN NEW.col1 = '222' THEN
NEW.col1 = 'bbb';
WHEN NEW.col1 = '333' THEN
NEW.col1 = 'ccc';
ELSE
NEW.col1 = 'error';
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
You also need to declare the trigger as a FOR EACH ROW
trigger for NEW
to work.
And a trigger name cannot be schema qualified.
CREATE TRIGGER table_replace
BEFORE INSERT
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_replace();
CASE
as a control structure is closed with END CASE
(whereas the expression is closed with just an END
). And in the branches there are statements, they need to be terminated by a semicolon.
Your LANGUAGE
is also misplaced. That belongs at the end. And you don't need the single quotes.
You can use an IF
to only do the replacement, when col2
is not null.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.col2 IS NOT NULL THEN
CASE
WHEN NEW.col1 = '111' THEN
NEW.col1 = 'aaa';
WHEN NEW.col1 = '222' THEN
NEW.col1 = 'bbb';
WHEN NEW.col1 = '333' THEN
NEW.col1 = 'ccc';
ELSE
NEW.col1 = 'error';
END CASE;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
You also need to declare the trigger as a FOR EACH ROW
trigger for NEW
to work.
And a trigger name cannot be schema qualified.
CREATE TRIGGER table_replace
BEFORE INSERT
ON schema.table
FOR EACH ROW
EXECUTE PROCEDURE schema.table_replace();
edited Dec 30 '18 at 1:02
answered Dec 30 '18 at 0:43


sticky bitsticky bit
14.8k81632
14.8k81632
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
add a comment |
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?
– user7113942
Dec 30 '18 at 0:52
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
@user7113942: See my edit.
– sticky bit
Dec 30 '18 at 1:02
add a comment |
The main problem of your example is missing FOR EACH ROW
clause in your CREATE TRIGGER
statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE
statement.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '111' THEN 'aaa'
WHEN '222' THEN 'bbb'
WHEN '333' THEN 'ccc'
ELSE 'error' END CASE;
END IF;
RETURN NEW;
END;
$BODY$
add a comment |
The main problem of your example is missing FOR EACH ROW
clause in your CREATE TRIGGER
statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE
statement.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '111' THEN 'aaa'
WHEN '222' THEN 'bbb'
WHEN '333' THEN 'ccc'
ELSE 'error' END CASE;
END IF;
RETURN NEW;
END;
$BODY$
add a comment |
The main problem of your example is missing FOR EACH ROW
clause in your CREATE TRIGGER
statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE
statement.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '111' THEN 'aaa'
WHEN '222' THEN 'bbb'
WHEN '333' THEN 'ccc'
ELSE 'error' END CASE;
END IF;
RETURN NEW;
END;
$BODY$
The main problem of your example is missing FOR EACH ROW
clause in your CREATE TRIGGER
statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE
statement.
CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '111' THEN 'aaa'
WHEN '222' THEN 'bbb'
WHEN '333' THEN 'ccc'
ELSE 'error' END CASE;
END IF;
RETURN NEW;
END;
$BODY$
answered Dec 30 '18 at 14:24
Pavel StehulePavel Stehule
22.7k34857
22.7k34857
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%2f53974370%2fbefore-insert-trigger-to-replace-new-data-postgres%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
vpKLqCnqFThonBkkKUz19Jw,0oA8eRIGbl2x6Cs1