Intermediate LevelPostgreSQL
How do you write procedural database functions using PL/pgSQL and PL/Python?
❓ Interview Prompt: Explain PL/pgSQL procedural language, function parameters, control structures (`IF`, `LOOP`), `SECURITY DEFINER`, and PL/Python.
PostgreSQL supports procedural database functions written in PL/pgSQL, PL/Python, or PL/v8 (JavaScript):
Key Concepts:
•
'LANGUAGE plpgsql': Native procedural language supporting SQL execution, loops, variables, and exception blocks.
•
'SECURITY DEFINER': Function executes with privileges of the user who created it (useful for administrative API wrappers!).
Code Implementation Example
sql
CREATE OR REPLACE FUNCTION process_monthly_interest(account_id INT, rate NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
current_bal NUMERIC;
new_bal NUMERIC;
BEGIN
SELECT balance INTO current_bal FROM accounts WHERE id = account_id FOR UPDATE;
IF current_bal > 0 THEN
new_bal := current_bal + (current_bal * rate);
UPDATE accounts SET balance = new_bal WHERE id = account_id;
RETURN new_bal;
END IF;
RETURN current_bal;
END;
$$ LANGUAGE plpgsql;💡Writing a PL/pgSQL procedural database function with row locking.
🎯 Interview Key Takeaway
Use PL/pgSQL functions to encapsulate complex multi-step database operations with row locking.
#PL/pgSQL#Stored Procedures#Functions#Intermediate
Back to all PostgreSQL Questions→