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

sqlpostgresqlstored-proceduresplpgsql


Solution 1:[1]

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.

Solution 2:[2]

you can use this template 

CREATE OR REPLACE FUNCTION a1()
  RETURNS integer AS
$BODY$
BEGIN
    return (select 1);
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
select a1()

Solution 3:[3]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 xardas
Solution 3 PM 77-1

Related Questions