[GENERAL] How to pass parameters into a sql script ?

[Atul Chojar]

We are unable to pass parameters into any sql script. Could anyone look at the 
test below and give any suggestions? PostgreSQL version is 8.2.7, running on 
Linux.

 

Test Script

========

$ cat chk_param.sql 

select ''''||:p_date::char(8)||'''';

select count(*) from prod_debit_payments_unapplied where when_received = 
(select ''''||:p_date::char(8)||'''');

select count(*) from prod_debit_payments_unapplied where when_received = 
'20081023';

 

Test Results

=========

$ psql -d us_audit -e -1 -v p_date='20081023' -f chk_param.sql 

Timing is on.

select ''''||20081023::char(8)||'''';

  ?column?  

------------

 '20081023'

(1 row)

 

Time: 1.474 ms

select count(*) from prod_debit_payments_unapplied where when_received = 
(select ''''||20081023::char(8)||'''');--same results with direct assignment 
and to_date

 count 

-------

     0

(1 row)

 

Time: 36.290 ms

select count(*) from prod_debit_payments_unapplied where when_received = 
'20081023';

 count 

-------

   193

(1 row)

 

Time: 17.722 ms

 

Thanks!

atul

 <http://www.airfacts.com/> AirFacts, Inc.8120 Woodmont Ave., Suite 
700Bethesda, MD 20814Tel: 301-760-7315

<<image001.png>>