Procedures & triggers
Write PL/pgSQL functions, stored procedures, and triggers — Silos runs the real Postgres procedural engine.
Silos runs a real PostgreSQL engine, so the procedural side of Postgres works as written: PL/pgSQL functions, stored procedures, triggers, and views all behave the way they do in upstream Postgres.
PL/pgSQL functions
Define functions in PL/pgSQL and call them from SQL:
CREATE OR REPLACE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(first || ' ' || last);
END;
$$;
SELECT full_name('Ada', 'Lovelace');Stored procedures
Procedures can manage their own transaction boundaries with CALL:
CREATE OR REPLACE PROCEDURE transfer(from_id bigint, to_id bigint, amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;
CALL transfer(1, 2, 100);Triggers
Attach a trigger function to a table to run logic automatically on INSERT,
UPDATE, or DELETE:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();Views and CTEs
Views, common table expressions, and window functions are all standard:
CREATE VIEW active_users AS
SELECT id, email
FROM users
WHERE last_seen > now() - interval '30 days';
WITH ranked AS (
SELECT id, email,
row_number() OVER (ORDER BY created_at) AS signup_rank
FROM users
)
SELECT * FROM ranked WHERE signup_rank <= 10;These are ordinary Postgres features running on the real engine. If a construct works on PostgreSQL, it's part of the same SQL surface here. See PostgreSQL compatibility for the full picture and Limitations for the genuine caveats.