How to use a function inside a filter

executable 0
Viewed 27

I'm extracting some data between () and I want to use the extrated data as column. Here is my fiddle https://www.db-fiddle.com/f/hY1JFUwk3YNGYye345pny8/2

My base table :

----------------------------
| id | active | dept       |
----------------------------
| 1  | true   | TEST (AFG) |
| 2  | true   | TEST (AFG) |
| 3  | true   | TEST (AFG) |
| 4  | true   | TEST (POD) |
| 5  | true   | TEST (POD) |
| 6  | true   | TEST (KMN) |
| 7  | true   | TEST (AGO) |
| 8  | true   | TEST (AGO) |
| 9  | false  | TEST (AGO) |
| 10 | true   | TEST (AGO) |
| 11 | true   | TEST (AGO) |
| 12 | true   | TEST (SUD) |
| 13 | true   | TEST (SUD) |
| 14 | true   | TEST (MOL) |
----------------------------

My current request (retreive active and inactive request):

SELECT
    'Active Request' AS Title,
    (regexp_matches(dept, '\((.*?)\)'))[1] as dept,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
GROUP BY dept
UNION
SELECT
    'Inactive Request' AS Title,
    (regexp_matches(dept, '\((.*?)\)'))[1] as dept,
    COUNT(*) FILTER (WHERE dept = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE dept = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE dept = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE dept = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE dept = 'POD') AS POD,
    COUNT(*) FILTER (WHERE dept = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'false'
GROUP BY dept;

The issue is I can't use the regexp into my filter.

Using the sql request :

SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'POD') AS POD,
    COUNT(*) FILTER (WHERE (regexp_matches(dept, '\((.*?)\)'))[1] = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM req
WHERE active = 'true'
GROUP BY dept;

Will throw me the following error :

Query Error: error: set-returning functions are not allowed in FILTER

My goal is to retreive data like :

----------------------------------------------------------------
| Title            | AFG | AGO | KMN | MOL | POD | SUD | TOTAL | 
----------------------------------------------------------------
| Active Request   | 3   | 4   | 1   | 1   | 2   | 2   | 13    |
| Inactive Request | 0   | 1   | 0   | 0   | 0   | 0   | 1     |
----------------------------------------------------------------

At this moment I'm able to extract the dept between () using (regexp_matches(dept, '\((.*?)\)'))[1] however, I can't use it in a filter statment

postgresql
1 Answers

Not sure if this helps, you could pull the regexp out to a CTE and filter on that, not sure if it's the most efficient approach but may get you started:

WITH dept_codes AS (
    SELECT
        dept,
        active,
        (regexp_matches(dept, '\((.*?)\)'))[1] AS code
    FROM
        req
)
SELECT
    'Active Request' AS Title,
    COUNT(*) FILTER (WHERE code = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE code = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE code = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE code = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE code = 'POD') AS POD,
    COUNT(*) FILTER (WHERE code = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM
    dept_codes
WHERE
    active = 'true';

Returns

     title      | afg | ago | kmn | mol | pod | sud | total 
----------------+-----+-----+-----+-----+-----+-----+-------
 Active Request |   3 |   4 |   1 |   1 |   2 |   2 |    13

If you want to include inactive results, of course you could do something like this:

WITH dept_codes AS (
    SELECT
        dept,
        active,
        (regexp_matches(dept, '\((.*?)\)'))[1] AS code
    FROM
        req
)
SELECT
    CASE WHEN active = 'true' THEN 'Active Request' ELSE 'Inactive Request' END AS Title,
    COUNT(*) FILTER (WHERE code = 'AFG') AS AFG,
    COUNT(*) FILTER (WHERE code = 'AGO') AS AGO,
    COUNT(*) FILTER (WHERE code = 'KMN') AS KMN,
    COUNT(*) FILTER (WHERE code = 'MOL') AS MOL,
    COUNT(*) FILTER (WHERE code = 'POD') AS POD,
    COUNT(*) FILTER (WHERE code = 'SUD') AS SUD,
    COUNT(*) AS TOTAL
FROM
    dept_codes
  GROUP BY 1;

Which returns

      title       | afg | ago | kmn | mol | pod | sud | total 
------------------+-----+-----+-----+-----+-----+-----+-------
 Active Request   |   3 |   4 |   1 |   1 |   2 |   2 |    13
 Inactive Request |   0 |   1 |   0 |   0 |   0 |   0 |     1
(2 rows)
w08r0
Related