CODEWITHSUNDEEP
sqlpostgresql
markyoung
markyoung

Reputation: 179

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

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?

Upvotes: 0

Views: 2150

Answers (2)

user330315
user330315

Reputation:

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()

Upvotes: 3

jonii
jonii

Reputation: 193

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.

Upvotes: 0

Related Questions