skip to Main Content

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.

3

Answers


  1. 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
    

  2. 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; 
    
    Login or Signup to reply.
  3. 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:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search