How to UPDATE table column with call function after INSERT command in PostgreSQL?
I need your help, I have an issue with updating specific column after running insert command.
table:
CREATE SEQUENCE public.llh_type_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 261
CACHE 1;
ALTER TABLE public.llh_type_id_seq
OWNER TO postgres;
CREATE TABLE public.llh_type
(id bigint NOT NULL DEFAULT nextval('llh_type_id_seq'::regclass),
identifier text,
name text)
WITH (
OIDS=FALSE);
ALTER TABLE public.llh_type
OWNER TO postgres;
function:
CREATE OR REPLACE FUNCTION public.generate_identifier(
id bigint,
prefix text)
RETURNS text AS
$BODY$
SELECT
CASE WHEN length($1::text) < 2
THEN UPPER($2 || '00000' || $1)
WHEN length($1::text) >= 2 AND length($1::text) < 3
THEN UPPER($2 || '0000' || $1)
WHEN length($1::text) >= 3 AND length($1::text) < 4
THEN UPPER($2 || '000' || $1)
WHEN length($1::text) >= 4 AND length($1::text) < 5
THEN UPPER($2 || '00' || $1)
WHEN length($1::text) >= 5 AND length($1::text) < 6
THEN UPPER($2 || '0' || $1)
ELSE
UPPER($2 || $1)
END;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
ALTER FUNCTION public.generate_identifier(bigint, text)
OWNER TO postgres;
After this I try to call my function after inserting data:
WITH t AS(
INSERT INTO llh_type (name) values('one')
RETURNING id)
UPDATE llh_type SET identifier = generate_identifier((select id from
t),'TC') WHERE id = (select id from t);
After running this code I have message:"Query returned successfully: 0 rows affected, 12 msec execution time." But table now looks like: http://joxi.ru/nAypMQjCYBggq2
In another case I have a solution, but I am not sure that it is correct:
INSERT INTO llh_type (name) values('one');
UPDATE llh_type SET identifier = generate_identifier((select id from
llh_type order by id desc limit 1),'TC')
WHERE id = (select id from llh_type order by id desc limit 1);
And after running, I have a message: "Query returned successfully: one row affected, 12 msec execution time." And result looks like as expected: http://joxi.ru/5md13oZCkM3el2
postgresql
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
add a comment |
I need your help, I have an issue with updating specific column after running insert command.
table:
CREATE SEQUENCE public.llh_type_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 261
CACHE 1;
ALTER TABLE public.llh_type_id_seq
OWNER TO postgres;
CREATE TABLE public.llh_type
(id bigint NOT NULL DEFAULT nextval('llh_type_id_seq'::regclass),
identifier text,
name text)
WITH (
OIDS=FALSE);
ALTER TABLE public.llh_type
OWNER TO postgres;
function:
CREATE OR REPLACE FUNCTION public.generate_identifier(
id bigint,
prefix text)
RETURNS text AS
$BODY$
SELECT
CASE WHEN length($1::text) < 2
THEN UPPER($2 || '00000' || $1)
WHEN length($1::text) >= 2 AND length($1::text) < 3
THEN UPPER($2 || '0000' || $1)
WHEN length($1::text) >= 3 AND length($1::text) < 4
THEN UPPER($2 || '000' || $1)
WHEN length($1::text) >= 4 AND length($1::text) < 5
THEN UPPER($2 || '00' || $1)
WHEN length($1::text) >= 5 AND length($1::text) < 6
THEN UPPER($2 || '0' || $1)
ELSE
UPPER($2 || $1)
END;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
ALTER FUNCTION public.generate_identifier(bigint, text)
OWNER TO postgres;
After this I try to call my function after inserting data:
WITH t AS(
INSERT INTO llh_type (name) values('one')
RETURNING id)
UPDATE llh_type SET identifier = generate_identifier((select id from
t),'TC') WHERE id = (select id from t);
After running this code I have message:"Query returned successfully: 0 rows affected, 12 msec execution time." But table now looks like: http://joxi.ru/nAypMQjCYBggq2
In another case I have a solution, but I am not sure that it is correct:
INSERT INTO llh_type (name) values('one');
UPDATE llh_type SET identifier = generate_identifier((select id from
llh_type order by id desc limit 1),'TC')
WHERE id = (select id from llh_type order by id desc limit 1);
And after running, I have a message: "Query returned successfully: one row affected, 12 msec execution time." And result looks like as expected: http://joxi.ru/5md13oZCkM3el2
postgresql
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
add a comment |
I need your help, I have an issue with updating specific column after running insert command.
table:
CREATE SEQUENCE public.llh_type_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 261
CACHE 1;
ALTER TABLE public.llh_type_id_seq
OWNER TO postgres;
CREATE TABLE public.llh_type
(id bigint NOT NULL DEFAULT nextval('llh_type_id_seq'::regclass),
identifier text,
name text)
WITH (
OIDS=FALSE);
ALTER TABLE public.llh_type
OWNER TO postgres;
function:
CREATE OR REPLACE FUNCTION public.generate_identifier(
id bigint,
prefix text)
RETURNS text AS
$BODY$
SELECT
CASE WHEN length($1::text) < 2
THEN UPPER($2 || '00000' || $1)
WHEN length($1::text) >= 2 AND length($1::text) < 3
THEN UPPER($2 || '0000' || $1)
WHEN length($1::text) >= 3 AND length($1::text) < 4
THEN UPPER($2 || '000' || $1)
WHEN length($1::text) >= 4 AND length($1::text) < 5
THEN UPPER($2 || '00' || $1)
WHEN length($1::text) >= 5 AND length($1::text) < 6
THEN UPPER($2 || '0' || $1)
ELSE
UPPER($2 || $1)
END;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
ALTER FUNCTION public.generate_identifier(bigint, text)
OWNER TO postgres;
After this I try to call my function after inserting data:
WITH t AS(
INSERT INTO llh_type (name) values('one')
RETURNING id)
UPDATE llh_type SET identifier = generate_identifier((select id from
t),'TC') WHERE id = (select id from t);
After running this code I have message:"Query returned successfully: 0 rows affected, 12 msec execution time." But table now looks like: http://joxi.ru/nAypMQjCYBggq2
In another case I have a solution, but I am not sure that it is correct:
INSERT INTO llh_type (name) values('one');
UPDATE llh_type SET identifier = generate_identifier((select id from
llh_type order by id desc limit 1),'TC')
WHERE id = (select id from llh_type order by id desc limit 1);
And after running, I have a message: "Query returned successfully: one row affected, 12 msec execution time." And result looks like as expected: http://joxi.ru/5md13oZCkM3el2
postgresql
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
I need your help, I have an issue with updating specific column after running insert command.
table:
CREATE SEQUENCE public.llh_type_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 261
CACHE 1;
ALTER TABLE public.llh_type_id_seq
OWNER TO postgres;
CREATE TABLE public.llh_type
(id bigint NOT NULL DEFAULT nextval('llh_type_id_seq'::regclass),
identifier text,
name text)
WITH (
OIDS=FALSE);
ALTER TABLE public.llh_type
OWNER TO postgres;
function:
CREATE OR REPLACE FUNCTION public.generate_identifier(
id bigint,
prefix text)
RETURNS text AS
$BODY$
SELECT
CASE WHEN length($1::text) < 2
THEN UPPER($2 || '00000' || $1)
WHEN length($1::text) >= 2 AND length($1::text) < 3
THEN UPPER($2 || '0000' || $1)
WHEN length($1::text) >= 3 AND length($1::text) < 4
THEN UPPER($2 || '000' || $1)
WHEN length($1::text) >= 4 AND length($1::text) < 5
THEN UPPER($2 || '00' || $1)
WHEN length($1::text) >= 5 AND length($1::text) < 6
THEN UPPER($2 || '0' || $1)
ELSE
UPPER($2 || $1)
END;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
ALTER FUNCTION public.generate_identifier(bigint, text)
OWNER TO postgres;
After this I try to call my function after inserting data:
WITH t AS(
INSERT INTO llh_type (name) values('one')
RETURNING id)
UPDATE llh_type SET identifier = generate_identifier((select id from
t),'TC') WHERE id = (select id from t);
After running this code I have message:"Query returned successfully: 0 rows affected, 12 msec execution time." But table now looks like: http://joxi.ru/nAypMQjCYBggq2
In another case I have a solution, but I am not sure that it is correct:
INSERT INTO llh_type (name) values('one');
UPDATE llh_type SET identifier = generate_identifier((select id from
llh_type order by id desc limit 1),'TC')
WHERE id = (select id from llh_type order by id desc limit 1);
And after running, I have a message: "Query returned successfully: one row affected, 12 msec execution time." And result looks like as expected: http://joxi.ru/5md13oZCkM3el2
postgresql
postgresql
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
asked Nov 13 '18 at 18:28
Sergey MankoSergey Manko
62
62
add a comment |
add a comment |
0
active
oldest
votes
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%2f53287369%2fhow-to-update-table-column-with-call-function-after-insert-command-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53287369%2fhow-to-update-table-column-with-call-function-after-insert-command-in-postgresql%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
