PostgreSQL 1 to many trigger procedure

Question

I wrote this query in PostgreSQL:

CREATE OR REPLACE FUNCTION pippo() RETURNS TRIGGER AS $$
BEGIN
  CHECK (NOT EXISTS (SELECT * FROM padre WHERE cod_fis NOT IN (SELECT padre FROM paternita)));
END;
$$ LANGUAGE plpgsql;

It returns:

Syntax error at or near CHECK.

I wrote this code because I have to realize a 1..n link between two tables.

@Andreas: Maybe you can start by telling him where his syntax error is. — Robert Harvey
– Robert Harvey, Commented Dec 1, 2014 at 19:06
Can you post the table structures and some sample records. It's hard to see what you're trying to do... — mlinth
– mlinth, Commented Dec 1, 2014 at 19:09
@RobertHarvey, his syntax error is at or near CHECK. We already know that. — Andreas
– Andreas, Commented Dec 1, 2014 at 19:09
What is CHECK(...)? AFAIK it is not a function built into postgresql. — Andreas
– Andreas, Commented Dec 1, 2014 at 19:19

Denis de Bernardy · Accepted Answer · 2014-12-01 19:29:28Z

1

You can't use CHECK here. CHECK is for table and column constraints.

Two further notes:

If this is supposed to be a statement level constraint trigger, I'm guessing you're actually looking for IF ... THEN RAISE EXCEPTION 'message'; END IF;
(If not, you may want to expand and clarify what you're trying to do.)
The function should return NEW, OLD or NULL.

answered Dec 1, 2014 at 19:29

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 1 to many trigger procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related