postgresql Programming with PL/pgSQL Basic PL/pgSQL Function
Example
A simple PL/pgSQL function:
CREATE FUNCTION active_subscribers() RETURNS bigint AS $$
DECLARE
-- variable for the following BEGIN ... END block
subscribers integer;
BEGIN
-- SELECT must always be used with INTO
SELECT COUNT(user_id) INTO subscribers FROM users WHERE subscribed;
-- function result
RETURN subscribers;
EXCEPTION
-- return NULL if table "users" does not exist
WHEN undefined_table
THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql;
This could have been achieved with just the SQL statement but demonstrates the basic structure of a function.
To execute the function do:
select active_subscribers();
