From Clause Conditional

Confidential

Hello All

We are testing a upgrade from pg11 to pg14 and have some issues to overcome. One of these is that we have upgraded pgsodium to the latest and there is a functional change - this question is not about sodium BTW.

So here is a sample bit of code that I will use to explain the issue - this would usually be params passed in, but I have hard coded one particular case that does not need the decryption. The code below would return decrypted data if the key is supplied (non 0) otherwise return null. This code used to work because the secret box call would allow null params. It no longer does. When the key is 0, the data passed in would be null.

LEFT JOIN lateral (
SELECT
CASE
WHEN (0 > 0) THEN
convert_from(crypto_secretbox_open, 'utf8')::JSON
ELSE
NULL
END AS edata
FROM
crypto_secretbox_open(coalesce(null, '')::bytea, coalesce(null, '')::bytea,0)
where (0>0)
) enc ON true

The issue is that, even when the key is 0, the select is still run (its part of a lateral join) and what we need to achieve is to effectively have a conditional where we only run the select if the key > 0 otherwise we return null - I have a brain freeze on this! I am sure there is an easy solution, but right now I can't see it.

Thx in advance

Z