The spi module provides several workable examples of using the Server Programming Interface (SPI) and triggers.

YugabyteDB supports the following four (of five — timetravel is not currently supported) extensions provided in the spi module:

  • autoinc functions auto-increment fields.
  • insert_username functions track who changed a table.
  • moddatetime functions track last modification times.
  • refint functions implement referential integrity.

Example

  1. Connect using ysqlsh and run the following commands:

    CREATE EXTENSION insert_username; CREATE EXTENSION moddatetime;
  2. Set up a table with triggers for tracking modification time and user (role):

    CREATE TABLE spi_test ( id int primary key, content text, username text not null, moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL ); CREATE TRIGGER insert_usernames BEFORE INSERT OR UPDATE ON spi_test FOR EACH ROW EXECUTE PROCEDURE insert_username (username); CREATE TRIGGER update_moddatetime BEFORE UPDATE ON spi_test FOR EACH ROW EXECUTE PROCEDURE moddatetime (moddate);
  3. Insert some rows. Each insert should add the current role as username and the current timestamp as moddate.

    SET ROLE yugabyte; INSERT INTO spi_test VALUES(1, 'desc1'); SET ROLE postgres; INSERT INTO spi_test VALUES(2, 'desc2'); INSERT INTO spi_test VALUES(3, 'desc3'); SET ROLE yugabyte; INSERT INTO spi_test VALUES(4, 'desc4'); SELECT * FROM spi_test ORDER BY id;
     id | content | username |          moddate
    ----+---------+----------+----------------------------
      1 | desc1   | yugabyte | 2019-09-13 16:55:53.969907
      2 | desc2   | postgres | 2019-09-13 16:55:53.983306
      3 | desc3   | postgres | 2019-09-13 16:55:53.98658
      4 | desc4   | yugabyte | 2019-09-13 16:55:53.991315
    (4 rows)
    

    The yugabyte and (for compatibility) postgres YSQL users are created by default.

  4. Update some rows. This should update both username and moddate accordingly.

    UPDATE spi_test SET content = 'desc1_updated' WHERE id = 1; UPDATE spi_test SET content = 'desc3_updated' WHERE id = 3; SELECT * FROM spi_test ORDER BY id;
    id |    content    | username |          moddate
    ----+---------------+----------+----------------------------
      1 | desc1_updated | yugabyte | 2019-09-13 16:56:27.623513
      2 | desc2         | postgres | 2019-09-13 16:55:53.983306
      3 | desc3_updated | yugabyte | 2019-09-13 16:56:27.634099
      4 | desc4         | yugabyte | 2019-09-13 16:55:53.991315
    (4 rows)