CREATE TRIGGER AND TRIGGER FUNCTION WITH RECURSIVE AND IF STATEMENT

Question

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 ?

Answer 1

You have two statements in the function: the first one is “WITH RECURSIVE parent_list … SELECT name FROM …” but it doesn't do anything with the result such as assigning it to a variable.

I think you may need to move the WITH clause into the condition:

IF 9817 in (
  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 _id FROM component
    WHERE _id in (SELECT parent FROM parent_list)
)
…