Postgresql – FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision

postgresql-9.4timestamptrigger

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

Best Answer

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;

UPDATE 2013-06-12 11:34 EDT

Here is your last comment

I am using above trigger with mysql server v 5.5.24 and it works fine. This is my local test site. Now I want to use it at my webhost, they have mysql server v 5.1.56 ... and it won; t work ! Am I missing something?

The problem is the MySQL version. If you look back in my two trigger posts, I used black market techniques to break the trigger midstream. This is necessary for older versions of MySQL. Notice what I stated on Apr 25, 2011 in Trigger in MySQL to prevent insertion :

The reason the book suggests preempting the Trigger in this manner stems from the fact that the MySQL Stored Procedure Language did not have SIGNAL implemented into the language (of course, SIGNAL is ANSI standard).

The authors of the book created work arounds by calling on SQL statements that are syntactically correct but fail at runtime. Pages 144-145 (Chapter 6: Error Handling) of the book gives these examples on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL emulation (Examples 6-19 and 6-20).

I directly quoted Pages 254-256 of the Book

dlckxmd

Please note the following:

MySQL 5.5.8 was released as GA Dec 03, 2010
The book was written March 2006

Therefore, signal processing DOES NOT WORK IN MySQL 5.1.

You will have to do the following:

Rewrite the trigger in MySQL 5.1 to break midstream
The trigger you wrote in the MySQL 5.5 database should stay the way it is now

That's why the trigger does not work in MySQL 5.1.

UPDATE 2013-06-12 11:44 EDT

Here is what you can do to the trigger in the MySQL 5.1.56 database

DELIMITER $$

CREATE TRIGGER chk_dates  
BEFORE INSERT ON verlof_aanvragen  
FOR EACH ROW  
BEGIN  
  DECLARE dummy INT;
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY) 
THEN  
  SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
END IF;
END $$

DELIMITER ;

Since mysql.user has no column called used, the query does not execute, breaking the trigger.

Postgresql: Insert trigger function fails on partial insert statement

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;

Best Answer

Related Solutions

Mysql – BEFORE INSERT trigger in MySQL

UPDATE 2013-06-12 11:34 EDT

UPDATE 2013-06-12 11:44 EDT

Postgresql: Insert trigger function fails on partial insert statement

Related Question