Conditionally drop an insert in a before insert trigger without returning error
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have the following function in a before insert trigger:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '121432' THEN '321123'
ELSE <command> END CASE; --there should be a command aborting insertion without error or exception
END IF;
RETURN NEW;
END;
$BODY$
The ELSE statement should abort insertion. Is there a command which drops the query without telling it to the client and leaving the table untouched?
sql postgresql database-trigger
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
add a comment |
I have the following function in a before insert trigger:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '121432' THEN '321123'
ELSE <command> END CASE; --there should be a command aborting insertion without error or exception
END IF;
RETURN NEW;
END;
$BODY$
The ELSE statement should abort insertion. Is there a command which drops the query without telling it to the client and leaving the table untouched?
sql postgresql database-trigger
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
RETURN NULLto cancel insertion.
– Adam
Jan 3 at 15:04
add a comment |
I have the following function in a before insert trigger:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '121432' THEN '321123'
ELSE <command> END CASE; --there should be a command aborting insertion without error or exception
END IF;
RETURN NEW;
END;
$BODY$
The ELSE statement should abort insertion. Is there a command which drops the query without telling it to the client and leaving the table untouched?
sql postgresql database-trigger
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
I have the following function in a before insert trigger:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
IF NEW.col2 NOT NULL THEN
NEW.col1 := CASE NEW.col1
WHEN '121432' THEN '321123'
ELSE <command> END CASE; --there should be a command aborting insertion without error or exception
END IF;
RETURN NEW;
END;
$BODY$
The ELSE statement should abort insertion. Is there a command which drops the query without telling it to the client and leaving the table untouched?
sql postgresql database-trigger
sql postgresql database-trigger
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
edited Jan 3 at 15:10
Erwin Brandstetter
355k69642822
355k69642822
asked Jan 3 at 14:53
user7113942user7113942
295
asked Jan 3 at 14:53
user7113942user7113942
295
asked Jan 3 at 14:53
user7113942user7113942
295
295
RETURN NULLto cancel insertion.
– Adam
Jan 3 at 15:04
add a comment |
RETURN NULLto cancel insertion.
– Adam
Jan 3 at 15:04
RETURN NULL to cancel insertion.– Adam
Jan 3 at 15:04
RETURN NULL to cancel insertion.– Adam
Jan 3 at 15:04
add a comment |
1 Answer
1
active
oldest
votes
Just use
RETURN NULL;
instead of
RETURN NEW;
to cancel the INSERT for the row and do nothing instead.
But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
$func$
BEGIN
IF NEW.col2 NOT NULL THEN
IF NEW.col1 = '121432' THEN -- could also be plpgsql CASE ...
NEW.col1 := '321123';
ELSE
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
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%2f54024687%2fconditionally-drop-an-insert-in-a-before-insert-trigger-without-returning-error%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
Just use
RETURN NULL;
instead of
RETURN NEW;
to cancel the INSERT for the row and do nothing instead.
But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
$func$
BEGIN
IF NEW.col2 NOT NULL THEN
IF NEW.col1 = '121432' THEN -- could also be plpgsql CASE ...
NEW.col1 := '321123';
ELSE
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
add a comment |
Just use
RETURN NULL;
instead of
RETURN NEW;
to cancel the INSERT for the row and do nothing instead.
But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
$func$
BEGIN
IF NEW.col2 NOT NULL THEN
IF NEW.col1 = '121432' THEN -- could also be plpgsql CASE ...
NEW.col1 := '321123';
ELSE
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
add a comment |
Just use
RETURN NULL;
instead of
RETURN NEW;
to cancel the INSERT for the row and do nothing instead.
But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
$func$
BEGIN
IF NEW.col2 NOT NULL THEN
IF NEW.col1 = '121432' THEN -- could also be plpgsql CASE ...
NEW.col1 := '321123';
ELSE
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
Just use
RETURN NULL;
instead of
RETURN NEW;
to cancel the INSERT for the row and do nothing instead.
But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:
CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
$func$
BEGIN
IF NEW.col2 NOT NULL THEN
IF NEW.col1 = '121432' THEN -- could also be plpgsql CASE ...
NEW.col1 := '321123';
ELSE
RETURN NULL;
END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
edited Jan 3 at 22:28
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
answered Jan 3 at 15:04
Erwin BrandstetterErwin Brandstetter
355k69642822
355k69642822
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%2f54024687%2fconditionally-drop-an-insert-in-a-before-insert-trigger-without-returning-error%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
RETURN NULLto cancel insertion.– Adam
Jan 3 at 15:04