Postgresql function, do different JOIN conditionally-CodePudding

Postgresql 12. In a function returning a SELECT result of table A and B, when the function input Boolean parameter is true, do

select * from A INNER join B on A.id = B.id;

when the parameter is false, do

select * from A LEFT join B on A.id = B.id;

checked case-when-end but it doesn't work in "where". Is there any way to achieve this without writing two separate SELECT?

CodePudding user response：

A LEFT JOIN in which the non-nullable column of right table is forced to be NOT NULL in the result is equivalent to INNER JOIN.

Test 2:

WITH A (id) AS (
         VALUES (1), (2), (3)
     )
   , B (id) AS (
         VALUES (1),      (3)
     )
   , args (isInner) AS (
         SELECT false
     )
SELECT *
  FROM A LEFT JOIN B
    ON A.id = B.id
 WHERE (SELECT NOT isInner FROM args)
    OR B.id IS NOT NULL
;

Result (LEFT JOIN):