Hello,
the construct surprised me when I saw it in the function the first time, but it is correct and works as expected - it allows writing the function as SQL instead of PLPGSQL while it ensures that for a parameter null value it evaluates to true instead of filtering the resultset.
What is the issue/better solution proposal?
Kind regards Ales Zeleny
čt 4. 8. 2022 v 23:15 odesílatel Joe Conway <mail@xxxxxxxxxxxxx> napsal:
On 8/4/22 08:34, Aleš Zelený wrote:
>> SELECT ... simple join of two tables...
>> WHERE opd.id_data_provider = _id_data_provider
>> AND CASE WHEN _external_id IS NULL
>> THEN external_id IS NULL
>> ELSE external_id = _external_id
>> END
>> AND CASE WHEN _external_complete_id IS NULL
>> THEN _external_complete_id IS NULL
Unrelated to your question, but shouldn't that actually read:
AND CASE WHEN _external_complete_id IS NULL
THEN external_complete_id IS NULL
^^^
>> ELSE external_complete_id = _external_complete_id
>> END;
>> $function$
--
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
