On 2022-08-05 17:47:02 +0200, Aleš Zelený wrote:
> 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
The problem Joe spotted is that a variable name is misspelled in one
place. It should (probably) be external_complete_id, not
_external_complete_id).
> better solution proposal?
I think you can make that clearer by using IS [NOT] DISTINCT FROM:
SELECT ... simple join of two tables...
WHERE opd.id_data_provider = _id_data_provider
AND external_id IS NOT DISTINCT FROM _external_id
AND external_complete_id IS NOT DISTINCT FROM _external_complete_id
;
However, your version may be faster, as last time I looked (it's been
some time) the optimizer wasn't especially good at handlung DISTINCT
FROM (probably because it's so rarely used).
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
