Thread: psql client: technique for applying default values to :variables?

Stumped: is there any way to set up default values for psql variables
within the .SQL file itself? Obviously, I can do something like:

$ psql -f my_script -v MYVAR=${myvar:-mydefault}

but I would prefer to have the value stored with the .SQL file, e.g. (if
this actually worked):

\set MYVAR COALESCE(:MYVAR,'mydefault')

-- 
Jeffery Boes  <><
jeff@endpoint.com

On 1/11/06, Jeff Boes <jeff@endpoint.com> wrote:
> Stumped: is there any way to set up default values for psql variables
> within the .SQL file itself? Obviously, I can do something like:
>
> $ psql -f my_script -v MYVAR=${myvar:-mydefault}
>
> but I would prefer to have the value stored with the .SQL file, e.g. (if
> this actually worked):
>
> \set MYVAR COALESCE(:MYVAR,'mydefault')

Stuff like this works for me in a SQL file....

\set edate 'CURRENT_DATE::DATE'

SELECT * FROM some_table WHERE update_date = :edate;

Tony Wasson wrote: <blockquote cite="mid6d8daee30601111551y68b2663ek6484c830f2107bee@mail.gmail.com" type="cite"><pre
wrap="">On1/11/06, Jeff Boes <a class="moz-txt-link-rfc2396E"
href="mailto:jeff@endpoint.com"><jeff@endpoint.com></a>wrote: </pre><blockquote type="cite"><pre wrap="">Stumped:
isthere any way to set up default values for psql variables
 
within the .SQL file itself? Obviously, I can do something like:

$ psql -f my_script -v MYVAR=${myvar:-mydefault}

but I would prefer to have the value stored with the .SQL file, e.g. (if
this actually worked):

\set MYVAR COALESCE(:MYVAR,'mydefault')   </pre></blockquote><pre wrap="">
Stuff like this works for me in a SQL file....

\set edate 'CURRENT_DATE::DATE'

SELECT * FROM some_table WHERE update_date = :edate; </pre></blockquote><br /> Sure, but that was not my question. I
wantto be able to set the variable on the command line, BUT have it default to a value inside the SQL script if not
presenton the command line.<br /><br /><tt>$ psql -v edate=2004-01-01<br /> ...<br /> => \set edate
'CURRENT_DATE::DATE'<br/> => select :edate;<br />     date<br /> ------------<br />  2006-01-14<br /><br /><br
/></tt><br/><pre class="moz-signature" cols="72">-- 
 
Jeffery Boes  <><
<a class="moz-txt-link-abbreviated" href="mailto:jeff@endpoint.com">jeff@endpoint.com</a>
</pre>

On Jan 14, 2006, at 23:54 , Jeff Boes wrote:

> Tony Wasson wrote:

>> Stuff like this works for me in a SQL file.... \set edate  
>> 'CURRENT_DATE::DATE' SELECT * FROM some_table WHERE update_date  
>> = :edate;
>
> Sure, but that was not my question. I want to be able to set the  
> variable on the command line, BUT have it default to a value inside  
> the SQL script if not present on the command line.

You could write a wrapper script that would accept a command line  
argument and load the file with the appropriate SET statement prepended.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:
>
> On Jan 14, 2006, at 23:54 , Jeff Boes wrote:
>
>> Tony Wasson wrote:
>
>>
>> Sure, but that was not my question. I want to be able to set the
>> variable on the command line, BUT have it default to a value inside
>> the SQL script if not present on the command line.
>
> You could write a wrapper script that would accept a command line
> argument and load the file with the appropriate SET statement prepended.

Which really gains me nothing over just:

$ psql -f myscript.sql -v "MYVAR=${myvar:-default}"

The original question was: how can I store the default value in the
"myscript.sql" script where it's used, rather than have it in a calling
script? The aim here was to put the default in the code, so they would
be bound together...


-- 
Jeffery Boes  <><
jeff@endpoint.com

Jeff Boes writes:

> Michael Glaesemann wrote:
>> You could write a wrapper script that would accept a command line
>> argument and load the file with the appropriate SET statement prepended.
>
> Which really gains me nothing over just:
>
> $ psql -f myscript.sql -v "MYVAR=${myvar:-default}"
>
> The original question was: how can I store the default value in the
> "myscript.sql" script where it's used, rather than have it in a calling
> script? The aim here was to put the default in the code, so they would
> be bound together...

How about using the traditional Unix macro processor "m4" instead of
psql's variables?

E.g., you could write a myscript.sql.m4 file with m4 variables instead
of psql ones. You could then use ifdef() to test for previous
definitions, or even define a default()-wrapper macro.

regards,
Andreas
--