Thread: How to pass parameters into a sql script ?

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

Thanks - with quoting, the 2^nd select (select count(*) from prod_debit_payments_unapplied where when_received = :p_date;) works.

Thanks!

atul

Looks like quoting issue.
Try this one:

$ psql -d us_audit -e -1 -v p_date=\'20081023\' -f chk_param.sql
select count(*) from prod_debit_payments_unapplied where when_received = (select :p_date::char(8));

or even

select count(*) from prod_debit_payments_unapplied where when_received = :p_date;

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.40/2135 - Release Date: 05/28/09 08:10:00

We want to implement a mechanism by which if any database function is created or modified in one database, then the same should automatically get done in another (1 or more) database(s).

It seems triggers on system catalogs like pg_proc are not allowed. Is there any way in postgres to do this, by some other way?

Thanks - with quoting, the 2^nd select (select count(*) from prod_debit_payments_unapplied where when_received = :p_date;) works.

Thanks!

atul

Looks like quoting issue.
Try this one:

$ psql -d us_audit -e -1 -v p_date=\'20081023\' -f chk_param.sql
select count(*) from prod_debit_payments_unapplied where when_received = (select :p_date::char(8));

or even

select count(*) from prod_debit_payments_unapplied where when_received = :p_date;

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.40/2135 - Release Date: 05/28/09 08:10:00

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.40/2135 - Release Date: 05/28/09 08:10:00