PostgreSQL subquery with IF EXISTS in trigger function-CodePudding

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?

CodePudding user response：

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;