1

I'm trying to create a function in PostgreSQL to count some values based on three parameters (a start date, an end date and a team's uuid) that is passed into the function.

I want to know if it is possible to pass an array of team uuid's into this function and perform a SELECT count for all of these teams.

My code:

CREATE OR REPLACE FUNCTION funcSelectedTeamActivtyDashboard
     (IN aStartDate date, IN aEndDate date, IN aTeamID uuid[])
RETURNS TABLE(teamName text, a integer, b integer, c integer, d integer) 
AS
$BODY$

SELECT t.name AS teamName, tm.name AS memberName,
    count(distinct a._id_) FILTER (WHERE a.datetime_registered BETWEEN aStartDate AND aEndDate)::integer AS a,
    count(distinct b._id_) FILTER (WHERE b.datetime_completed BETWEEN aStartDate AND aEndDate)::integer AS b,
    count(distinct c._id_) FILTER (WHERE c.datetime_completed BETWEEN aStartDate AND aEndDate)::integer AS c,
    count(distinct d._id_) FILTER (WHERE d.start_date BETWEEN aStartDate AND aEndDate)::integer AS d
FROM team_member tm
JOIN team t ON ...
JOIN table_a a ON ...
LEFT JOIN table_b b ON ...
LEFT JOIN table_c c ON ...
JOIN table_d d ON ...

WHERE t._id_ = ANY(aTeamID)

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

I want to use this function to say pass in either:

A) aTeamID = abc123 and then perform these counts for this selected team and over the selected dates. It should return something like...

- TEAM1  MEMBER1     2   4   5   2
- TEAM1  MEMBER2     1   5   1   3
- TEAM1  MEMBER3     3   3   2   2
- TEAM1  MEMBER4     7   9   7   3

OR

B) aTeamID = abc123, def456, ghi789 and then perform these counts for all of these teams and over the selected dates. It should return something like...

- TEAM1  MEMBER1     2   4   5   2
- TEAM1  MEMBER2     1   5   1   3
- TEAM1  MEMBER3     3   3   2   2
- TEAM1  MEMBER4     7   9   7   3
- TEAM2  MEMBER1     2   4   5   2
- TEAM2  MEMBER2     1   5   1   3
- TEAM2  MEMBER3     3   3   2   2
- TEAM2  MEMBER4     7   9   7   3
- TEAM3  MEMBER1     2   4   5   2
- TEAM3  MEMBER2     1   5   1   3
- TEAM3  MEMBER3     3   3   2   2
- TEAM3  MEMBER4     7   9   7   3

I have left out some basic parts of my query as they are obvious as to what it is and should do... My focus is on being able to pass in an array to the function and then count for all of the instances in the array.

I do have a basic knowledge of PostgreSQL and SQL but I haven't come across anything like this before and I'm not sure if it is even possible. Thanks in advance for any help provided and please feel free to ask for more details should it be necessary.

C. Viljoen
  • 53
  • 1
  • 2
  • 8

1 Answers1

0

Speaking specifically of ANY, yes you can.

ANY expects array expression to work , like 

SELECT 'Apple' = ANY(ARRAY['Apple', 'Orange', 'Mango']);

So in your case its input would be something like ARRAY['abc123', 'def456', 'ghi789'].

You can also is "IN" in place of ANY. Its simple as

SELECT 'Apple' IN ('Apple', 'Orange', 'Mango');

Further more these could help.

IN vs ANY operator in PostgreSQL

postgreSQL - in vs any

lat long
  • 920
  • 6
  • 16
  • 1
    My problem is not with how to check for something in a list/array... I'm trying to find out how to pass an array into a function and then checking for something in that array. More specifically a UUID array – C. Viljoen Jan 05 '18 at 11:40
  • Call function like .. SELECT * FROM funcSelectedTeamActivtyDashboard ('2014-07-07', '2014-07-08', sub.arr) FROM (SELECT array_agg(aTeamID) AS arr FROM your_table ) AS sub; – lat long Jan 05 '18 at 12:51
  • Which table would be `your_table`? – C. Viljoen Jan 08 '18 at 07:02
  • it is that table from which you want to select a column and convert that into a array for passing in the function. You shoud replace your_table with your own table name – lat long Jan 08 '18 at 07:29
  • Oh I get it... Is there not a way to pass in the array from a Java call? I want a user to be able to select a single team or a set of teams that they can see all the data of. My idea for the call would be like = `string query = 'Select * from funcSelectedTeamActivtyDashboard(start_date, end_date, team_id_array)'` – C. Viljoen Jan 08 '18 at 07:39
  • I am not sure about JAVA. But if you are using some library for PG in JAVA. As for as the array format to pass in the PG query is correct, it should work as well i think. – lat long Jan 08 '18 at 07:52
  • Great thanks very much for all your help. Will get back once I know more – C. Viljoen Jan 08 '18 at 08:18