I have table with followings columns.
Emp name,emp id,emp ph no
X,1,99
X,2,10
Y,2,30
Output:
x,1,(99,10)
Based on emp name and order by emp Id form the phone array
Query:
select emp-name,array_agg(emp_phno order by emp_id) from emp
Error:
function array_agg called with an invalid number or type of parameters
What is the problem, and how can I fix it?
First you need to define an array type to hold the results of your aggregation. This would be something like
CREATE TYPE emp_phno_arr AS VARCHAR(10) ARRAY[5];
In the above SQL note the size of the VARCHAR. I set it to 10 because that's the length of a North American phone number. You might want it to be a different length. Also not the size of the array. I set it to 5. This means the aggregation will allow for at most 5 phone numbers per person. If there are more than 5 number the aggregation will fail. But you can change the size of the array as needed.
Next you will want to do the aggregation.
SELECT emp-name, ARRAY_AGG(emp_phno, NEW emp_phno_arr()) FROM emp GROUP BY emp-name;
Note the second argument to the ARRAY_AGG function. It calls the constructor for the new array type you created in the first SQL statement.