Re: Perl and psql variables

[Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>]

On 4/26/06 8:11 AM, "Mark Campbell" <mdc(at)ucs(dot)co(dot)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