Passing both TRUE and FALSE to a parameter in a SQL function-CodePudding

I have a PostgreSQL function which looks basically like this...

create or replace function myfunction(myparam boolean)
returns table 
language 'sql'
as $BODY$
  select * from mytable where myIndicator = myparam
$BODY$

...and it works just fine. But, I have now been told that this function may be asked to return all cases where myparam is true or false or both. However, this code fails because array is not a possible parameter type:

create or replace function myfunction(couldBeTrueOrFalseOrBoth array)
returns table 
language 'sql'
as $BODY$
  select * from mytable where myIndicator in couldBeTrueOrFalseOrBoth
$BODY$

Is there an easy solution here?

CodePudding user response：

You can use null for this:

create or replace function myfunction(myparam boolean)
returns table (...)
language sql
as $BODY$
  select * 
  from mytable 
  where myIndicator = myparam
     or myparam is null  
$BODY$

To get all rows use:

select *
from myfunction(null)

You can also use an optional parameter:

create or replace function myfunction(myparam boolean default null)
returns table (...)
language sql
as $BODY$
  select * 
  from mytable 
  where myIndicator = myparam
     or myparam is null  
$BODY$

Then calling it without a parameter is also possible (and the same as passing null)

select *
from myfunction()

CodePudding user response：

Option 1:

Use a string as the parameter with the options "true", "false" and "both". Then in your function use case statement to determine wether it is "true", "false" or "both".

create or replace function myfunction(myparam string)
returns table 
language 'sql'
as $BODY$
  CASE
      WHEN myParam='true'
      THEN select * from mytable where myIndicator = true
      WHEN myParam='false'
      THEN select * from mytable where myIndicator = false
      WHEN myParam='both'
      THEN select * from mytable
  END
$BODY$

Option 2:

Just call your function twice, with all the cases it needs the answer to.