CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsql
Asav Patel
Asav Patel

Reputation: 1162

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.

Upvotes: 2

Views: 9716

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657397

The PL/pgSQL version can look like this:

CREATE FUNCTION account_count_in(_branch_city text)
  RETURNS int
  LANGUAGE plpgsql 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$;

Call:

SELECT account_count_in('Albany');

Avoid naming collisions. Make the parameter name unique or table-qualify columns in the query. I did both.

Just RETURN the result for a simple query like this.

The function is declared to return 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 PL/pgSQL functions, not in SQL functions and not useful here.

Upvotes: 3

Sultan -Bizgaze
Sultan -Bizgaze

Reputation: 1

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;

Upvotes: -1

xardas
xardas

Reputation: 296

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()

Upvotes: 1

Related Questions