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