dimanche 22 mars 2020

CREATE TRIGGER AND TRIGGER FUNCTION WITH RECURSIVE AND IF STATEMENT

I would like to create a trigger called before an insert in my db.

The trigger function check one condition, if the conditions is true : CANNOT insert, else : CAN insert

To check my condition I need RECURSIVITY, This is what I've done :

CREATE OR REPLACE FUNCTION trigger_check_relation()
  RETURNS TRIGGER AS

$$BEGIN

    WITH RECURSIVE parent_list AS (

      SELECT relation.parent
      FROM relation
      WHERE relation.child = 9817

      UNION

      SELECT r.parent FROM relation r
        JOIN parent_list on parent_list.parent = r.child
    )
    SELECT name FROM component WHERE _id in (SELECT parent FROM parent_list);

    IF 9817 in (SELECT _id FROM component WHERE _id in (SELECT parent FROM parent_list))
        THEN RETURN OLD;
     ELSE 
        RETURN NEW;
     END IF;

END;$$ LANGUAGE plpgsql;

I can create my trigger and my function but when I run it have :

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function trigger_check_relation() line 3 at SQL statement SQL state: 42601

Could you please help me to understand and fix this issue ?

Aucun commentaire:

Enregistrer un commentaire