Check attribute in trigger function (PostgreSQL)

Question

I have a task in school which requires me to create a table and a trigger. I don't really know how to explain it but how can I check if cID is inside the select statement within the trigger function ? Basically my goal is to only allow cID values which are not inside of "SELECT * from Example2 natural join Example3". Can anyone help me with that? Thank you.

CREATE TABLE Example(
cID INTEGER REFERENCES Example2(attr),
level INTEGER CHECK (level BETWEEN 1 AND 10)); 


CREATE FUNCTION exp() RETURNS TRIGGER AS
$$
BEGIN
 IF EXISTS (select * from Example2 natural join Example3) THEN
 RAISE EXCEPTION '...';
 END IF;
 return null;
 END; $$ language plpgsql;


CREATE CONSTRAINT TRIGGER trg
AFTER INSERT OR UPDATE ON Example
FOR EACH ROW
EXECUTE PROCEDURE exp();

Not sure what "only allow cID values" means? This would be easier to answer if you would provide sample data showing what is you want to accomplish. — Adrian Klaver
– Adrian Klaver, Commented Oct 2, 2020 at 19:34

GMD · Accepted Answer · 2020-10-02 21:59:24Z

1

   

     CREATE FUNCTION exp() RETURNS TRIGGER AS
        $$
        BEGIN
         IF EXISTS (select 1 from Example2  a where   a.cID  = new.cID  ) THEN
         RAISE EXCEPTION '...';
         END IF;
         return         RETURN NEW;
         END; $$ language plpgsql;

answered Oct 2, 2020 at 21:59

GMD

406 bronze badges

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

3 Comments

Jeremy Caney Over a year ago

Welcome. Thank you for your contribution. Could you edit your answer to provide additional explanation? That will help people new to PostgreSQL better understand the reasoning here.

Trenton McKinney Over a year ago

The provided answer was flagged for review as a Low Quality Post. Here are some guidelines for How do I write a good answer?. This provided answer could benefit from an explanation. Code only answers are not considered "good" answers. From Review.

10 Rep Over a year ago

Yeah, I mean this code could work, but maybe an explanation would be cool. Thanks!

Collectives™ on Stack Overflow

Check attribute in trigger function (PostgreSQL)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related