Postgresql - Is there a way to add subquery within case conditions?

OmerFarooq
October 25, 2022
266 views
2 votes
3 Answers

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.

Answers

Chosen as BEST ANSWER

@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

(Edit)

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;

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

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Is there a way to add subquery within case conditions?

Answers