Stored procedure to return count

I am trying to make a stored procedure for the query I have:

SELECT count(DISTINCT account_number)
from account
NATURAL JOIN branch
WHERE branch.branch_city='Albany';

or

SELECT count(*)
from (
   select distinct account_number
   from account
   NATURAL JOIN branch
   WHERE branch.branch_city='Albany'
   ) as x;

I have written this stored procedure but it returns count of all the records in column not the result of query plus I need to write stored procedure in plpgsql not in SQL.

CREATE FUNCTION account_count_in(branch_city varchar) RETURNS int AS $$
   PERFORM DISTINCT count(account_number) from (account NATURAL JOIN branch)
   WHERE (branch.branch_city=branch_city); $$ LANGUAGE SQL;

Help me write this type of stored procedure in plpgsql which returns returns the number of accounts managed by branches located in the specified city.

Answer

The plpgsql version could look like this:

CREATE FUNCTION account_count_in(_branch_city text)
  RETURNS int AS
$func$
BEGIN
   RETURN (
   SELECT count(DISTINCT a.account_number)::int
   FROM   account a
   NATURAL JOIN branch b
   WHERE  b.branch_city = _branch_city
   );
END
$func$ LANGUAGE plpgsql;

Call:

SELECT account_count_in('Albany');

• Avoid naming coalitions by making the parameter name unique or table-qualifying columns in the query. I did both.

• Just RETURN the result for a simple query like this.

• The function is declared to integer. Make sure the return type matches by casting the bigint to int.

• NATURAL JOIN is short syntax, but it may not be the safest form. Later changes to underlying tables can easily break this. Better to join on column names explicitly.

• PERFORM is only valid in plpgsql functions, not in sql functions and not useful here at all.