Re: How to pass parameters into a sql script ?

From: Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com>
To: Atul Chojar <achojar(at)airfacts(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, Andy <ayoder(at)airfacts(dot)com>
Subject: Re: How to pass parameters into a sql script ?
Date: 2009-05-28 19:42:42
Message-ID: 690707f60905281242h6d85af5cme9e188181866b0c9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

2009/5/28 Atul Chojar <achojar(at)airfacts(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Douglas Alan 2009-05-28 19:45:22 Re: What is the right way to deal with a table with rows that are not in a random order?
Previous Message Douglas Alan 2009-05-28 19:12:58 Re: What is the right way to deal with a table with rows that are not in a random order?