How will you create your aggregate function using PostgreSQL?

 Posted by Niladri.Biswas on 2/10/2013 | Category: PostgreSQL Interview questions | Views: 2904 | Points: 40
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;


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response