FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision

Question

In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of publication of the law. If this condition is respected, then the function updates the other attributes and inserts the other fields of the tuple, ie updates the counter of the approved laws, adding one more and consents to the insertion of all the other values of the attributes in NEW.

I hope I have been clearer now.

I tried to isolate the error by first doing the select inside the if, but I found that you can not convert the date to a string, so how do you do it? do I convert it into a character? but then it would take another cycle.

I want to extract the month and the year from the attribute of type date into law_approvata.data_promulgazione

SELECT EXTRACT(MONTH FROM TIMESTAMP legge_approvata.data_promulgazione);
SELECT EXTRACT(YEAR FROM TIMESTAMP  legge_approvata.data_promulgazione);

ERRORE:  errore di sintassi a o presso "legge_approvata"
LINE 5:  SELECT EXTRACT(MONTH FROM TIMESTAMP legge_approvata.data_pr...
                                             ^
********** Error **********

ERRORE: errore di sintassi a o presso "legge_approvata"
SQL state: 42601}

QUERY:

INSERT INTO public.pubblicazione(
   titolo_legge,
   legislatura,
   gazzetta_numero,
   data_pubblicazione, 
   leggi_legislatura,
   tot_num_leggi,
   testo,
    relatore,
   numero_legge)
 VALUES (
    '[Legge di bilancio 2018] Bilancio di previsione dello Stato per l anno finanziario 2018 e bilancio pluriennale per il triennio 2018-2020',
    17,
   '302' ,--gazzetta_numero
   '29/12/2017' , -- data_pubblicazione
    0,--leggi_legislatura int
    0,
   'https://parlamento17.openpolis.it/atto/documento/id/523404' ,
   '{"Marcello Gualdani","Magda Angela Zanoni","Pier Carlo Padoan"}' ,
  'S.2960-B');

BEFORE TRIGGER:

CREATE TRIGGER inserimento
  BEFORE INSERT
  ON public.pubblicazione
  FOR EACH ROW
  EXECUTE PROCEDURE public.inserimento_pubblicazione();

Trigger function

   begin
   if exists (select 1 from legge_approvata
              where NEW.titolo_legge = legge_approvata.titolo and 
               new.data_pubblicazione between 
                  (SELECT EXTRACT(MONTH FROM /*TIMESTAMP*/ legge_approvata.data_promulgazione))and
                  (SELECT EXTRACT(YEAR FROM /*TIMESTAMP */ legge_approvata.data_promulgazione)) 
             )
   then
       new.leggi_legislatura= (select count(*) from legge_approvata where NEW.titolo_legge = legge_approvata.titolo and
                         data_promulgazione between
                         (SELECT EXTRACT(MONTH FROM /*TIMESTAMP*/ legge_approvata.data_promulgazione))and
                  (SELECT EXTRACT(YEAR FROM /*TIMESTAMP*/  legge_approvata.data_promulgazione)) and
                  legge_approvata.legislatura=new.legislatura )
                  +1;
       new.tot_num_leggi=(select count(*) from legge_approvata);
       return new;
   else
       raise exception '% non è stata promulgata nello stesso mese e anno %',new.titolo_legge, legge_approvata.data_promulgazione;
   end if;
end;}

OUTPUT ERROR:

ERRORE:  l'operatore non esiste: date >= double precision
LINE 3:                new.data_pubblicazione between 
                                              ^
HINT:  Nessun operatore trovato con nome e tipi di argomenti forniti. Potrebbe essere necessario convertire i tipi esplicitamente.
QUERY:  SELECT exists (select 1 from legge_approvata
              where NEW.titolo_legge = legge_approvata.titolo and 
               new.data_pubblicazione between 
                  (SELECT EXTRACT(MONTH FROM /*TIMESTAMP*/ legge_approvata.data_promulgazione))and
                  (SELECT EXTRACT(YEAR FROM /*TIMESTAMP */ legge_approvata.data_promulgazione)) 
             )
CONTEXT:  funzione PL/pgSQL inserimento_pubblicazione() riga 5 a IF
********** Error **********

ERRORE: l'operatore non esiste: date >= double precision
SQL state: 42883
Hint: Nessun operatore trovato con nome e tipi di argomenti forniti. Potrebbe essere necessario convertire i tipi esplicitamente.
Context: funzione PL/pgSQL inserimento_pubblicazione() riga 5 a IF

Thank you.

EXAMPLE:

SELECT EXTRACT(MONTH FROM TIMESTAMP '25/03/1982');
SELECT EXTRACT(YEAR FROM TIMESTAMP '2018-02-16');

EXTRACTION YEAR

EXTRACTION MONTH

Answer 1

You are overcomplicating things.

For one, you don't have to write SELECT in front of every function call. select extract(...) can simply be replaced with extract(...)

But you seem to be confusing what the function does.

As data_pubblicazione is a date value, this (simplified) condition

new.data_pubblicazione between EXTRACT(MONTH FROM data_promulgazione)
                           and EXTRACT(YEAR FROM data_promulgazione)

boils down to something like:

where '2019-03-08` between 3 and 2019

clearly that doesn't make any sense. Both sides of the comparison of a comparison need to be of the same type.

You just want to check if both dates are in the same month. You can use either use date_trunc() or to_char() to "normalize" both dates to the start of the month:

date_trunc('month', new.data_pubblicazione) = date_trunc('month', data_promulgazione)

or using to_char()

to_char(new.data_pubblicazione, 'yyyy-mm') = to_char(data_promulgazione, 'yyyy-mm')

You could in theory use extract, but that makes the condition extremely unreadable in my opinion

 where (extract(year from new.data_pubblicazione), 
        extract(month from new.data_pubblicazione)) = (extract(year from data_promulgazione), 
                                                       extract(month from data_promulgazione))

The above condition results in something like: where (2019,3) = (2019,3)

So the if statement should be like this:

if exists (select 1 
          from legge_approvata la
          where NEW.titolo_legge = la.titolo 
            and date_trunc('month', new.data_pubblicazione) = date_trunc('month', la.data_promulgazione)
         )
then 

The assignment needs to be adjusted as well. I personally prefer to use select ... into variable from ... in PL/pgSQL, but that is a matter of style:

   select count(*) + 1
     into new.leggi_legislatura  -- this replaces the :=  assignment
   from legge_approvata la 
   where NEW.titolo_legge = la.titolo 
     and date_trunc('month', new.data_pubblicazione) = date_trunc('month', la.data_promulgazione)
     and la.legislatura = new.legislatura;