postgresql stored-procedures functions plpgsql triggers

Stored procedures and functions in PostgreSQL

Maria Garcia Feb 2026

2 tabs

-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$
BEGIN
  RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT get_full_name('John', 'Doe');

-- Function with complex logic
CREATE OR REPLACE FUNCTION calculate_discount(
  customer_id INT,
  order_total DECIMAL
)
RETURNS DECIMAL AS $$
DECLARE
  customer_tier VARCHAR;
  discount_pct DECIMAL := 0;
BEGIN
  -- Get customer tier
  SELECT tier INTO customer_tier
  FROM customers
  WHERE id = customer_id;

  -- Calculate discount based on tier and total
  discount_pct := CASE customer_tier
    WHEN 'gold' THEN 0.15
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0
  END;

  -- Additional discount for large orders
  IF order_total > 1000 THEN
    discount_pct := discount_pct + 0.05;
  END IF;

  RETURN order_total * discount_pct;
END;
$$ LANGUAGE plpgsql;

-- Table-returning function
CREATE OR REPLACE FUNCTION get_user_orders(user_id_param INT)
RETURNS TABLE (
  order_id INT,
  order_date TIMESTAMP,
  total DECIMAL,
  status VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT id, created_at, amount, status::VARCHAR
  FROM orders
  WHERE user_id = user_id_param
  ORDER BY created_at DESC;
END;
$$ LANGUAGE plpgsql;

-- Usage in SELECT
SELECT * FROM get_user_orders(123);

-- Function with loops
CREATE OR REPLACE FUNCTION generate_monthly_report(
  year_param INT
)
RETURNS TABLE (
  month INT,
  total_sales DECIMAL,
  total_orders INT
) AS $$
DECLARE
  month_num INT;
BEGIN
  FOR month_num IN 1..12 LOOP
    RETURN QUERY
    SELECT
      month_num,
      COALESCE(SUM(amount), 0),
      COUNT(*)::INT
    FROM orders
    WHERE EXTRACT(YEAR FROM created_at) = year_param
      AND EXTRACT(MONTH FROM created_at) = month_num;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Exception handling
CREATE OR REPLACE FUNCTION safe_divide(
  numerator DECIMAL,
  denominator DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Stored Procedure (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE process_monthly_billing()
LANGUAGE plpgsql
AS $$
DECLARE
  customer_record RECORD;
BEGIN
  FOR customer_record IN
    SELECT id, email, monthly_fee
    FROM customers
    WHERE status = 'active'
  LOOP
    -- Create invoice
    INSERT INTO invoices (customer_id, amount, due_date)
    VALUES (
      customer_record.id,
      customer_record.monthly_fee,
      CURRENT_DATE + INTERVAL '30 days'
    );

    -- Send email (log for now)
    INSERT INTO email_queue (recipient, subject)
    VALUES (
      customer_record.email,
      'Your monthly invoice'
    );

    COMMIT;  -- Commit each customer separately
  END LOOP;
END;
$$;

-- Call procedure
CALL process_monthly_billing();

-- Trigger function for auto-updating timestamps
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Trigger for data validation
CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Z|a-z]{2,}$' THEN
    RAISE EXCEPTION 'Invalid email format: %', NEW.email;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_email_format
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION validate_email();

-- Trigger for maintaining aggregate counts
CREATE OR REPLACE FUNCTION update_post_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE users
    SET posts_count = posts_count + 1
    WHERE id = NEW.user_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE users
    SET posts_count = posts_count - 1
    WHERE id = OLD.user_id;
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.user_id != OLD.user_id THEN
      UPDATE users SET posts_count = posts_count - 1
      WHERE id = OLD.user_id;
      UPDATE users SET posts_count = posts_count + 1
      WHERE id = NEW.user_id;
    END IF;
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_post_count
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_post_count();

-- Instead Of Trigger (for views)
CREATE VIEW user_summaries AS
SELECT
  u.id,
  u.name,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

CREATE OR REPLACE FUNCTION update_user_summary()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE users
  SET name = NEW.name
  WHERE id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_user_via_view
INSTEAD OF UPDATE ON user_summaries
FOR EACH ROW
EXECUTE FUNCTION update_user_summary();

-- Conditional trigger
CREATE TRIGGER notify_high_value_order
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.total > 10000)
EXECUTE FUNCTION notify_sales_team();

-- Drop trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

-- Disable/Enable trigger
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;

2 files · sql Explain with highlit

Stored procedures encapsulate business logic in database. Functions return values; procedures don't (PostgreSQL 11+). I use functions for reusable calculations, data transformations. PL/pgSQL provides procedural language—variables, loops, conditionals. Functions can be called in SELECT statements. Procedures support transaction control—COMMIT/ROLLBACK. Triggers execute functions automatically on data changes. User-defined functions enable complex aggregations. Understanding when to use database logic versus application code is key. Functions reduce network overhead for complex operations. Security-definer functions run with creator privileges. Stored procedures improve performance for data-intensive operations but reduce portability.