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
@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.
Try this new version set up from your own trial :
Simplified:
Using a simpler "switched" SQL
CASE
expression.But breaking out the distinct cases will typically result in faster query plans for each:
Using a "switched" PL/pgSQL
CASE
expression. And no additional PL/pgSQL variable and separate query. Cheaper. Don’t confuse SQL and PL/pgSQLCASE
. 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 expensiveDISTINCT
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: