How will you create your aggregate function using PostgreSQL?

Answer:

Let us consider that we have two tables say Employee and Department with the below data.

/* Employee Table(tblEmployee) */

DeptId		EmpName	

-------		-------

1		Shashi Bhushan

1		Deepak Singh

1		Abhijeet Moshambique

1		Manish Bharat

2		Fatima  Sarani

2		Sumanta Manik

3		Amitav Mallik

3		Deepak Kumar Goyal

3		Amitav Salonki

4		Amit Ojha







/* Department Table(tblDept) */

DeptId		DeptName

------		--------

1		Accounts

2		Finance

3		IT

4		Sales



*/

And we are looking for the below output

/* Result */

Deptname	Employee List

--------	-------------

Accounts	Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat

Finance	Fatima  Sarani,Sumanta Manik

IT		Deepak Kumar Goyal,Amitav Salonki

Sales	Amit Ojha

We can achieve this by using custom aggregate function as under

CREATE  AGGREGATE ListAggregation1 (anyelement)(  

	sfunc = array_append,  

	stype = anyarray,  

	initcond = '{}' 

  );

Then invoke it as under

Select DeptName,Array_To_String(ListAggregation1(EmpName),',') "Employee List"

FROM tblEmployee 

Join tblDept

Using(DeptId) 

Group By DeptName;