PostgreSQL subquery with IF EXISTS in trigger function

Question

I have a PostgreSQL trigger function like so:

CREATE FUNCTION playlists_tld_update_trigger() RETURNS TRIGGER AS
    $$
    BEGIN
      IF EXISTS (SELECT 1 FROM "subjects" WHERE "subjects"."id" = new.subject_id) THEN
        new.tld = (SELECT "subjects"."tld" FROM "subjects" WHERE "subjects"."id" = new.subject_id LIMIT 1);
      END IF;
      RETURN new;
    END
    $$
    LANGUAGE plpgsql;

The trigger function will set the playlist's "tld" column to match the subject's "tld" column, but only if there exists a subject referenced by the subject_id foreign key. How do I use a subquery to combine the 2 queries into 1, or to avoid redundancy?

J Spratt · Accepted Answer · 2022-01-06 17:40:09Z

2

CREATE FUNCTION playlists_tld_update_trigger()
RETURNS TRIGGER
AS $$
DECLARE
    my_tld <data_type_of_tld>;
BEGIN
    SELECT subjects.tld
      INTO my_tld
      FROM subjects
     WHERE subjects.id = new.subject_id
     LIMIT 1
    ;
    IF FOUND
    THEN
        new.tld = my_tld;
        RETURN NEW;
    ELSE
        -- do something else
        RETURN OLD;
    END IF;
END
$$ LANGUAGE plpgsql;

answered Jan 6, 2022 at 17:40

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL subquery with IF EXISTS in trigger function

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