3

I created two tables on PostgreSQL.

I would like to note in a layer a date and retrieve: the corresponding year in a variable, then reuse the year variable in various other attributes integrating attributes from the second table.

To do this, I have a function launched by a trigger.

My problem is linked to the fact that if I note a date 01/01/2020 I do not recover the year 2020 in my variables. Could anyone tell me where the problem is coming from? There is a shift, the year is assigned to the 2nd line (see capture).

DROP TABLE public.test;
CREATE TABLE public.test
(id_sign SERIAL NOT NULL PRIMARY KEY,   
s_date_signal date, 
code_insee VARCHAR(5),
nom varchar(150),  
annee_signal integer,
cle_1 VARCHAR(150),
cle_2 VARCHAR(150), 
cle_3 VARCHAR (20), 
geom geometry(point,2154));

 

CREATE OR REPLACE FUNCTION public.concat_2_var (s1 varchar, s2 varchar, s3 varchar) RETURNS varchar AS
$body$
BEGIN
RETURN s1||s2||s3;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.t_maj ()
RETURNS trigger AS
$BODY$
BEGIN
SELECT DISTINCT date_part('year', s_date_signal) from public.test INTO NEW.annee_signal;
SELECT DISTINCT public.concat_2_var(NEW.code_insee::varchar,'-',NEW.id_sign::varchar) from public.t_test_com INTO NEW.cle_1;
SELECT DISTINCT public.concat_2_var(NEW.annee_signal::varchar,'-',NEW.cle_1::varchar) from public.t_test_com INTO NEW.cle_2;
SELECT DISTINCT public.concat_2_var(NEW.annee_signal::varchar,'-',NEW.id_sign::varchar) from public.t_test_com INTO NEW.cle_3;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER t_maj_t 
BEFORE INSERT
ON public.test
FOR EACH ROW
EXECUTE PROCEDURE public.t_maj  ();

enter image description here

1 Answer 1

6

For each inserted row, you are fetching the complete list of DISTINCT date_part('year', s_date_signal) entries from the public.test table, of which the first record in random (semi-deterministic, or by PK) order is chosen for assignment to NEW.annee_signal.

I do not quite understand the motive behind fetching data from a table in this and all following statements, but my guess is you want to directly access the NEW.s_date_signal record field instead:

CREATE OR REPLACE FUNCTION public.t_maj()
RETURNS TRIGGER AS
  $BODY$
  BEGIN
    NEW.annee_signal := DATE_PART('YEAR', NEW.s_date_signal)::INT;

    NEW.cle_1 := public.concat_2_var(NEW.code_insee::varchar,'-',NEW.id_sign::varchar);
    NEW.cle_2 := public.concat_2_var(NEW.annee_signal::varchar,'-',NEW.cle_1::varchar);
    NEW.cle_3 := public.concat_2_var(NEW.annee_signal::varchar,'-',NEW.id_sign::varchar);
    
    RETURN NEW;
  END;
  $BODY$
  LANGUAGE plpgsql
;

Note that, with your setup you will also need TRIGGER logic in place to handle UPDATEs to the columns used in the expressions! My suggestion: take a look at my last point below.


Bonus:

  • Use CONCAT_WS instead of a cumbersome re-implementation of its logic:

    CREATE OR REPLACE FUNCTION public.t_maj()
    RETURNS TRIGGER AS
      $BODY$
      BEGIN
        NEW.annee_signal := DATE_PART('YEAR', NEW.s_date_signal);
    
        NEW.cle_1 := CONCAT_WS('-', NEW.code_insee, NEW.id_sign)::VARCHAR;
        NEW.cle_2 := CONCAT_WS('-', NEW.annee_signal, NEW.code_insee, NEW.id_sign)::VARCHAR;
        NEW.cle_3 := CONCAT_WS('-', NEW.annee_signal, NEW.id_sign)::VARCHAR;
    
        RETURN NEW;
      END;
      $BODY$
      LANGUAGE plpgsql
    ;
    
  • Follow normalization rules and avoid data duplication - retrieve the concatenated values explicitly when accessing the table, instead of storing them in the table. If annee_signal is of no use other than being part of the cle expressions, don't store it either. The main benefit other than storage overhead is that UPDATEs to the table are immediately projected.

    • Here you can go the syntactic sugar way and write yourself some composite type functions, e.g.:
      CREATE OR REPLACE FUNCTION cle_1(
        IN  public.test,    -- pass the specific table composite type
        OUT VARCHAR
      ) IMMUTABLE STRICT PARALLEL SAFE
        LANGUAGE SQL AS
        $BODY$
          SELECT CONCAT_WS('-', $1.code_insee, $1.id_sign)::VARCHAR;
        $BODY$
      ;
      
      CREATE OR REPLACE FUNCTION cle_2(
        IN  public.test,    -- pass the specific table composite type
        OUT VARCHAR
      ) IMMUTABLE STRICT PARALLEL SAFE
        LANGUAGE SQL AS
        $BODY$
          SELECT CONCAT_WS('-', DATE_PART('YEAR', $1.s_date_signal), $1.code_insee, $1.id_sign)::VARCHAR;
        $BODY$
      ;
      
      CREATE OR REPLACE FUNCTION cle_3(
        IN  public.test,    -- pass the specific table composite type
        OUT VARCHAR
      ) IMMUTABLE STRICT PARALLEL SAFE
        LANGUAGE SQL AS
        $BODY$
          SELECT CONCAT_WS('-', DATE_PART('YEAR', $1.s_date_signal), $1.id_sign)::VARCHAR;
        $BODY$
      ;
      
      which you can then use in a SELECT like this:
      SELECT
        t.id_sign,
        t.cle_1,    -- the functions always have to be fully qualified with the table name or alias - so it cannot be just 'cle_1`, it has to be 't.cle_1'!
        t.cle_2,
        t.cle_3
      FROM
        public.test AS t
      ;
      
      Alternatively, use a similar function to get all three concatenated values in one go:
      CREATE OR REPLACE FUNCTION cl(
        IN  public.test,    -- pass the specific table composite type
        OUT e_1 VARCHAR,
        OUT e_2 VARCHAR,
        OUT e_3 VARCHAR
      ) IMMUTABLE STRICT PARALLEL SAFE
        LANGUAGE SQL AS
        $BODY$
          SELECT
            CONCAT_WS('-', $1.code_insee, $1.id_sign)::VARCHAR,
            CONCAT_WS('-', annee_signal, $1.code_insee, $1.id_sign)::VARCHAR,
            CONCAT_WS('-', annee_signal, $1.id_sign)::VARCHAR
          FROM
            DATE_PART('YEAR', $1.s_date_signal) AS annee_signal
          ;
        $BODY$
      ;
      
      and use it by directly accessing the returned fields, or expanding its results with *:
      SELECT
        t.id_sign,
        t.cl.e_1,
        t.cl.e_2,
        t.cl.e_3
        -- or simply t.cl.*
      FROM
        public.test AS t
      ;
      
  • Alternatively, use GENERATED columns - they are made for this, and while technically storing data they are a superb way to keep the UPDATE dependencies intact for purely computationally derived column values, without writing additional TRIGGER logic.

    First we need an IMMUTABLE version of CONCAT_WS in order to work on GENERATED columns:

    CREATE OR REPLACE FUNCTION immutable_concat_ws(
      text,
      VARIADIC text[]
    ) RETURNS text
      LANGUAGE internal
      IMMUTABLE PARALLEL SAFE AS
        'text_concat_ws'
    ;
    

    Note that you need superuser privileges to create LANGUAGE internal functions - if that is not possible, write a simple LANGUAGE SQL wrapper using CONCAT_WS.

    and then we use it in the respective column definitions:

    CREATE TABLE public.test(
      id_sign       INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, --recommended way to add a serial id   
      s_date_signal DATE, 
      code_insee    VARCHAR(5),
      nom           VARCHAR(150),  
      annee_signal  INT GENERATED ALWAYS AS ( DATE_PART('YEAR', s_date_signal)::INT ) STORED,
      cle_1         VARCHAR(150) GENERATED ALWAYS AS ( IMMUTABLE_CONCAT_WS('-', code_insee, id_sign) ) STORED,
      cle_2         VARCHAR(150) GENERATED ALWAYS AS ( IMMUTABLE_CONCAT_WS('-', DATE_PART('YEAR', s_date_signal), code_insee, id_sign) ) STORED, 
      cle_3         VARCHAR (20) GENERATED ALWAYS AS ( IMMUTABLE_CONCAT_WS('-', DATE_PART('YEAR', s_date_signal), id_sign) ) STORED, 
      geom          GEOMETRY(POINT, 2154)
    );
    
1
  • 1
    Thank you very much for your very complete, very enriching and very educational. Good day !
    – fcka
    Sep 13, 2023 at 12:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.