Views, triggers and routines

Views

A view exposes a query as if it were a table. The console lists the schema’s views and lets you create or replace a definition: the rewrite is checked before it is applied, so reads that depend on it do not break.

Triggers

A trigger attaches a function to a table and reacts to inserts, updates or deletes. The referenced function must already exist and return a trigger: create it before the trigger.

Functions and procedures

SQL and PL/pgSQL routines are created by pasting their DDL. The list shows the name, signature, type and language; deletion targets the exact signature, never every overload.

• A function returning a set of rows is queryable like a view, including over the REST API.
• Keep logic small: a short function is easier to test, review and promote without endangering the rest of the schema.
• These objects belong to the schema: changing them requires an authorised organisation role.

Good practice

Everything living in the schema follows the same path as tables — console or migration — and is promoted the same way. For a delicate change, go through the sandbox: you check the definition in isolation before pushing it to production.

-- Vue : exposer une requête comme une table
CREATE OR REPLACE VIEW taches_ouvertes AS
SELECT id, titre, created_at
FROM mes_taches
WHERE statut = 'ouvert';

-- Fonction de trigger, puis trigger sur la table
CREATE OR REPLACE FUNCTION maj_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER taches_touch
BEFORE UPDATE ON mes_taches
FOR EACH ROW EXECUTE FUNCTION maj_updated_at();

Next steps

• Tables
• Sandbox
• Remote SQL migrations