Skip to content
This repository has been archived by the owner on Jan 21, 2024. It is now read-only.

Trigger to populate array

Will Schmid edited this page Aug 31, 2014 · 2 revisions

Create triggers that automatically populate arrays of 1-to-many foreign keys

CREATE OR REPLACE FUNCTION authors_books() RETURNS trigger AS
$body$
BEGIN
  IF TG_OP != 'INSERT' THEN
    UPDATE authors
    SET books = (
      SELECT ARRAY(
        SELECT id
        FROM books
        WHERE author_id = OLD.author_id
        ORDER BY title ASC
      )
    )
    WHERE id = OLD.author_id;
  END IF;
  UPDATE authors
  SET books = (
    SELECT ARRAY(
      SELECT id
      FROM books
      WHERE author_id = NEW.author_id
      ORDER BY title ASC
    )
  )
  WHERE id = NEW.author_id;
  RETURN null;
END;
$body$
LANGUAGE 'plpgsql';

CREATE TRIGGER book_tr1
  AFTER INSERT OR UPDATE OF author_id OR DELETE
  ON books FOR EACH ROW
  EXECUTE PROCEDURE authors_books();

TODO: make this function generic so it works for any table