Обсуждение: substitute variable in sql?

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:

> Running PostgreSQL 7.2.1 on RedHat Linux 7.2:
>
> I have a query where I have to replace the
> date parameter each time I run the query.
>
> Is there a way to substitute just the date
> parameter in the sql without having to
> edit the script, re-run and edit again?

There are loads of ways to do this.  What client language are you
using?  Are you running 'psql' from a shell script?

-Doug

--thanks for the reply

--i just have an sql script that i launch
--by hand (psql -U <user> -d <database> -f ./<script>)

--the date is a rolling date which is supposed to
--change every 3 months.  and as variable 1 is moved
--up every month, so should variable 2 move up every
--month.

--the result is var1 and var2 should always be
--3 months apart every month (or, every time i
--run the script).

--now i *could* run psql from a shell ... can i
--substitute a variable and just stick it in place
--when i run the sql script?

--thanks again!

-X

-----Original Message-----
From: Doug McNaught [mailto:doug@mcnaught.org]

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:

> Running PostgreSQL 7.2.1 on RedHat Linux 7.2: 
> 
> I have a query where I have to replace the
> date parameter each time I run the query.
> 
> Is there a way to substitute just the date
> parameter in the sql without having to
> edit the script, re-run and edit again?

There are loads of ways to do this.  What client language are you
using?  Are you running 'psql' from a shell script?

-Doug

On Tue, Oct 29, 2002 at 11:19:31 -0500,
  Doug McNaught <doug@mcnaught.org> wrote:
> "Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
>
> > Running PostgreSQL 7.2.1 on RedHat Linux 7.2:
> >
> > I have a query where I have to replace the
> > date parameter each time I run the query.
> >
> > Is there a way to substitute just the date
> > parameter in the sql without having to
> > edit the script, re-run and edit again?
>
> There are loads of ways to do this.  What client language are you
> using?  Are you running 'psql' from a shell script?

And also what date do you want? It may be possible to calculate it based
on the current date and/or information in the database.

On Tue, Oct 29, 2002 at 11:35:42 -0500,
  "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> --thanks for the reply
>
> --i just have an sql script that i launch
> --by hand (psql -U <user> -d <database> -f ./<script>)
>
> --the date is a rolling date which is supposed to
> --change every 3 months.  and as variable 1 is moved
> --up every month, so should variable 2 move up every
> --month.

It sounds like you might be able to calculate the date based on the
current date. You would need to formula a precise rule for calculating
the two dates based on the date the script is being run. (And you may
still need another script if you need to be able to handle exceptions.)
If the script updates stuff than you may need to put in some extra care
in making sure that it can't be run twice or after skipping a time period
without some sort of override.

> On Tue, Oct 29, 2002 at 11:35:42 -0500,
>   "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> > --thanks for the reply
> >
> > --i just have an sql script that i launch
> > --by hand (psql -U <user> -d <database> -f ./<script>)
> >
> > --the date is a rolling date which is supposed to
> > --change every 3 months.  and as variable 1 is moved
> > --up every month, so should variable 2 move up every
> > --month.

You could create a template script which contains
DATA_1 for the first parameter and DATA_2 for the second
parameter.

Then, use sed to replace DATA_1 and DATA_2
with the actual values.

--- edit a.sql :
select * from tbl where ddata between DATA_1 and DATA_2;

--- run sed :
sed -e 's/DATA_1/10-09-2002/' -e 's/DATA_2/10-12-2002/' a.sql  > b.sql

--- cat b.sql :
select * from tbl where ddata between 10-09-2002 and 10-12-2002;

--- run the script :
psql -f b.sql

( I think this solution could be improved by using pipes ,
  in order to avoid the temporary b.sql file ... )


Adrian Maier
(am@fx.ro)