How to UPDATE table column with call function after INSERT command in PostgreSQL?

Question

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

Answer 1

function as the same, just add:

CREATE OR REPLACE FUNCTION insert_llh_type_identifier()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    UPDATE llh_type SET identifier = (generate_identifier(NEW.id, 'TC')) WHERE id = NEW.id;
    RETURN NEW;
END ;
$$;

CREATE TRIGGER llh_type_changes
    AFTER INSERT
    ON llh_type
    FOR EACH ROW
EXECUTE PROCEDURE insert_llh_type_identifier();