In T-SQL I can specify parameters in where statement like this:
declare @start date set @start ='2021-10-10'
declare @start date set @end ='2021-10-10'
select *
from dbo.table
where date between @start and @end
How can I write the equivalent statement in PostgreSQL?
The solution may depend on the scope where you need to use your parameters :
If the scope is just one plpgsql function or procedure, you can simply DECLARE your parameters as described in the documentation.
If the scope is one transaction or session, you can alter and query configuration parameters at run-time as described in the documentation using the set_config and current_setting functions :
txt = set_config('myvar.startdate','2021-10-10', false) ;
txt = set_config('myvar.enddate','2021-10-10', false) ;
select *
from dbo.table
where date between current_setting('myvar.startdate') and current_setting('myvar.enddate')
If the scope is multi sessions, you can define configuration parameters at the database level as described in the documentation :
ALTER DATABASE database_name SET myvar.startdate = '2021-10-10' ;
ALTER DATABASE database_name SET myvar.enddate = '2021-10-10' ;
select *
from dbo.table
where date between current_setting('myvar.startdate') and current_setting('myvar.enddate')
You may need to convert the configuration parameters to the type of the date column in the query, for instance :
where date between CAST(current_setting('myvar.startdate') AS timestamp with time zone) and CAST(current_setting('myvar.enddate') AS timestamp with time zone)
