Обсуждение: Perl and psql variables

Hi

I am currently running a Perl script once a month which in turn uses a
sql script file to extract info from a postgres database.

I would like to automate this perl script, but the problem that I have
is that I need to pass on a variable from the perl script to the sql script.


Yes, I can add the entire block of sql code into the perl script, but I
don't want it to function like that ie duplicate the sql code in the
perl script, as I might need a different perl script to execute the same
sql.

eg

the perl script generates a variable called $month (which is the current
month), I then need that month variable passed as a command line line
parameter to psql. eg psql -f sql.file --variable "month = $month"
then do a SELECT with the variable called :month

Is this possible to do, if so how?

Regards

--

Mark Campbell


Confidentiality Notice: http://ucs.co.za/conf.html

On 4/26/06 8:11 AM, "Mark Campbell" <mdc@ucs.co.za> wrote:

> Hi
>
> I am currently running a Perl script once a month which in turn uses a
> sql script file to extract info from a postgres database.
>
> I would like to automate this perl script, but the problem that I have
> is that I need to pass on a variable from the perl script to the sql script.
>
>
> Yes, I can add the entire block of sql code into the perl script, but I
> don't want it to function like that ie duplicate the sql code in the
> perl script, as I might need a different perl script to execute the same
> sql.
>
> eg
>
> the perl script generates a variable called $month (which is the current
> month), I then need that month variable passed as a command line line
> parameter to psql. eg psql -f sql.file --variable "month = $month"
> then do a SELECT with the variable called :month
>
> Is this possible to do, if so how?

Probably the easiest thing to do is to write a function WITHIN THE DATABASE
that you then call from your perl script.  You could write your function in
SQL like shown in this section of the documentation:

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html

Sean

Mark Campbell <mdc@ucs.co.za> writes:
> the perl script generates a variable called $month (which is the current
> month), I then need that month variable passed as a command line line
> parameter to psql. eg psql -f sql.file --variable "month = $month"
> then do a SELECT with the variable called :month

How about something like

    (
      echo "\set :month = $month"
      cat sql.file
    ) | psql

            regards, tom lane

Thx for the replies

What I found after really reading the man pages of psql is the -v switch

So the following now works for me:

psql -f sql.file -v month=$month

and I have the variable :month in the sql.file

Regards

Mark Campbell


Confidentiality Notice: http://ucs.co.za/conf.html

Tom Lane wrote:

Mark Campbell <mdc@ucs.co.za> writes: 

the perl script generates a variable called $month (which is the current 
month), I then need that month variable passed as a command line line 
parameter to psql. eg psql -f sql.file --variable "month = $month"
then do a SELECT with the variable called :month   

How about something like
(  echo "\set :month = $month"  cat sql.file) | psql
		regards, tom lane