How to pass parameters into a sql script ?

["Atul Chojar" <achojar@xxxxxxxxxxxx>]

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