-- 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;
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.
