Re: How to pass parameters into a sql script ?

2009/5/28 Atul Chojar <achojar@airfacts.com>:
> 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
>


bdteste=# select '20081023' =  ''''||20081023::char(8)||'''';
 ?column?
----------
 f
(1 registro)

bdteste=# select '20081023', length('20081023'),
''''||20081023::char(8)||'''', length(''''||20081023::char(8)||'''');
 ?column? | length |  ?column?  | length
----------+--------+------------+--------
 20081023 |      8 | '20081023' |     10
(1 registro)

Osvaldo