Is there a way to add subquery within case conditions?

Question

I have the function below in Postgres which was working fine. but then I had to add other types, So I tried to add cases to it. But this isn't working as expected.

Basically what I am trying to do is if user is ALPHA then add the last 2 where clauses. If it's a BETA then only use the second last clause and ignore the last where clause.

Old method without checking the logged in user role:

     begin
         return query SELECT distinct(gl.user_id) as user_id, u.name_tx FROM contact_linking cl
         INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
         INNER JOIN group_linking gl ON gl.group_id = gc.group_id
         INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
         INNER JOIN users u ON u.user_id = gl.user_id
         WHERE cl.ref_contact_type_cd = 'PRIMARY' 
         AND cl.users_id = userId AND cl.activ_yn = 'Y' AND gl.activ_yn = 'Y' AND cl.contact_id IS NOT NULL
         AND gc.type LIKE 'ALPHA%'
         AND gcw.type = gc.type

         UNION ALL
         select userId as user_id; 
     end

After adding new type:

    begin
        return query SELECT distinct(gl.user_id) as user_id FROM contact_linking cl
        INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
        INNER JOIN group_linking gl ON gl.group_id = gc.group_id
        INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
        INNER JOIN users u ON u.user_id = gl.user_id
        WHERE cl.ref_contact_type_cd = 'PRIMARY' 
        AND cl.users_id = userId AND cl.activ_yn = 'Y' AND gl.activ_yn = 'Y' AND cl.contact_id IS NOT NULL
        AND CASE 
            WHEN 'ALPHA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gc.type LIKE 'ALPHA%'
            WHEN 'BETA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gc.type LIKE '%BETA'
            ELSE true
        END
        AND CASE 
            WHEN 'ALPHA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gcw.type = gc.type
            ELSE true
        END
            
        UNION ALL
        select userId as user_id; 
    end

Can you please help in making this query to work.

Could you not do `AND ((gc.type LIKE 'ALPHA%' AND gcw.type = gc.type) OR gc.type LIKE '%BETA')` — Radagast, Oct 25 '22 at 16:21
@PhilCoulson You are correct, this is actually quite relavant if you consider the first query. But in the second query i have changed a few things like i want to check if the logged in user is a ALPHA and if it is then i add conditions based on that. — Omer Farooq, Oct 25 '22 at 16:42
@PhilCoulson is there a way to store the logged in user type in a variable and then use that variable in the conditions. — Omer Farooq, Oct 25 '22 at 16:54
I am finding it hard to visualize the conditional where clause in my head. If you can mock up some sample data to explain the filtering logic, that would be helpful. As to your question about using a variable. I see no reason why you shouldn't be as long as the select query used to define the variable results in 1 value only — Radagast, Oct 25 '22 at 16:58
You should always disclose a minimal, *complete* function with header and input parameters. Can be relevant. Also your version of Postgres and core table definitions. — Erwin Brandstetter, Oct 27 '22 at 22:30

score 1 · Answer 1 · answered Oct 25 '22 at 17:56

Try this new version set up from your own trial :

return query
  SELECT distinct(gl.user_id) as user_id 
    FROM contact_linking cl
   INNER JOIN group_contacts gc
      ON gc.contact_id = cl.contact_id
   INNER JOIN group_linking gl
      ON gl.group_id = gc.group_id
   INNER JOIN group_contacts_w gcw
      ON gcw.group_link_id = gl.group_link_id
   INNER JOIN users u
      ON u.user_id = gl.user_id
   WHERE cl.ref_contact_type_cd = 'PRIMARY' 
     AND cl.users_id = userId
     AND cl.activ_yn = 'Y'
     AND gl.activ_yn = 'Y'
     AND cl.contact_id IS NOT NULL
     AND (   (     (SELECT ref_user_cd = 'ALPHA' FROM users WHERE user_id = userId) 
               AND gc.type LIKE 'ALPHA%'
               AND gcw.type = gc.type
             )
          OR (     (SELECT ref_user_cd = 'BETA' FROM users WHERE user_id = userId)
               AND gc.type LIKE '%BETA'
             )
          OR (SELECT ref_user_cd <> 'ALPHA' AND ref_user_cd <> 'BETA' FROM users WHERE user_id = userId)
        )
UNION ALL
  select userId as user_id;

The case statements have been quite well rephrased and replaced with the normal conditions, and much simpler version of my query. I understand that you have kept most of the query same, and no optimizations have been done on purpose. But i have come up with different solution that i would like to share as a solution because i didnt find something similar on stackoverflow. — Omer Farooq, Oct 25 '22 at 19:03

score 1 · Answer 2 · edited Oct 27 '22 at 22:32

@Edouard answer is quite a good representation on how to simplify this query. But because i was inside a function i had the option to use variables, and i would like to share my answer as i didnt find something like this.

DECLARE
    loggedin_ref_user_cd character varying(25);
BEGIN
    
    loggedin_ref_user_cd := (SELECT ref_user_cd FROM users WHERE user_id = userId);
    --RAISE WARNING 'value of loggedin_ref_user_cd : %', loggedin_ref_user_cd;  -- just for debugging
    
    RETURN QUERY
    SELECT distinct(gl.user_id) as user_id FROM contact_linking cl
    INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
    INNER JOIN group_linking gl ON gl.group_id = gc.group_id
    INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
    INNER JOIN users u ON u.user_id = gl.user_id
    WHERE cl.ref_contact_type_cd = 'PRIMARY' 
    AND cl.users_id = userId AND cl.activ_yn = 'Y' AND gl.activ_yn = 'Y' AND cl.contact_id IS NOT NULL
    AND CASE 
        WHEN loggedin_ref_user_cd = 'ALPHA' THEN ( gc.type LIKE 'ALPHA%' AND gcw.type = gc.type )
        WHEN loggedin_ref_user_cd = 'BETA' THEN gc.type LIKE '%BETA'
        ELSE true
    END
        
    UNION ALL
    select userId as user_id; 
    
END

Erwin Brandstetter · Accepted Answer · 2022-10-27T22:36:36.330

Simplified:

DECLARE
   loggedin_ref_user_cd text := (SELECT ref_user_cd FROM users WHERE user_id = userId);
BEGIN
   RETURN QUERY
   SELECT DISTINCT gl.user_id  -- No parentheses around gl.user_id!
   FROM   contact_linking  cl
   JOIN   group_contacts   gc  USING (contact_id)
   JOIN   group_linking    gl  ON gl.group_id = gc.group_id
   JOIN   group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
-- JOIN   users            u   ON u.user_id = gl.user_id  -- unused ballast
   WHERE  cl.ref_contact_type_cd = 'PRIMARY' 
   AND    cl.users_id = userId
   AND    cl.activ_yn = 'Y'                      -- should be boolean
-- AND    cl.contact_id IS NOT NULL              -- established by join condition
   AND    gl.activ_yn = 'Y'                      -- should be boolean
   AND    CASE loggedin_ref_user_cd 
          WHEN 'ALPHA' THEN gc.type LIKE 'ALPHA%' AND gcw.type = gc.type
          WHEN 'BETA'  THEN gc.type LIKE '%BETA'
          ELSE true
          END
   UNION ALL
   SELECT userid;  -- AS user_id  -- noise
END

Using a simpler "switched" SQL CASE expression.
But breaking out the distinct cases will typically result in faster query plans for each:

BEGIN
   -- plpgsql CASE, not to be confused with SQL CASE!
   CASE (SELECT ref_user_cd FROM users WHERE user_id = userId)
   WHEN 'ALPHA' THEN
      RETURN QUERY
      SELECT DISTINCT gl.user_id
      FROM   contact_linking  cl
      JOIN   group_contacts   gc  USING (contact_id)
      JOIN   group_linking    gl  ON gl.group_id = gc.group_id
      JOIN   group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
      WHERE  cl.ref_contact_type_cd = 'PRIMARY' 
      AND    cl.users_id = userId
      AND    cl.activ_yn = 'Y'
      AND    gl.activ_yn = 'Y'
      AND    gc.type LIKE 'ALPHA%'
      AND    gcw.type = gc.type;

   WHEN 'BETA'  THEN
      RETURN QUERY
      SELECT DISTINCT gl.user_id  -- do we still need DISTINCT ???
      FROM   contact_linking  cl
      JOIN   group_contacts   gc  USING (contact_id)
      JOIN   group_linking    gl  ON gl.group_id = gc.group_id
      WHERE  cl.ref_contact_type_cd = 'PRIMARY' 
      AND    cl.users_id = userId
      AND    cl.activ_yn = 'Y'
      AND    gl.activ_yn = 'Y'
      AND    gc.type LIKE '%BETA';
      
   ELSE
      RETURN QUERY
      SELECT DISTINCT gl.user_id  -- do we still need DISTINCT ???
      FROM   contact_linking  cl
      JOIN   group_contacts   gc  USING (contact_id)
      JOIN   group_linking    gl  ON gl.group_id = gc.group_id
      WHERE  cl.ref_contact_type_cd = 'PRIMARY' 
      AND    cl.users_id = userId
      AND    cl.activ_yn = 'Y'
      AND    gl.activ_yn = 'Y';
   END CASE;
   
   RETURN QUERY
   SELECT userid;
END

Using a "switched" PL/pgSQL CASE expression. And no additional PL/pgSQL variable and separate query. Cheaper. Don't confuse SQL and PL/pgSQL CASE. See:

Depending on your undisclosed table definitions you can probably simplify further. I already removed the join to group_contacts_w in two cases. And I suspect you don't need the possibly expensive DISTINCT at all.

Don't use parentheses after DISTINCT, which is an SQL key word, not a function. With a single column in the expression, that happens to work, but it will break with more columns. See:

Combine two queries to count distinct strings with different filters

Is there a way to add subquery within case conditions?

3 Answers3