pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql client: technique for applying default values to :variables?
6+ messages / 4 participants
[nested] [flat]
* psql client: technique for applying default values to :variables?
@ 2006-01-11 18:48 Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Re: psql client: technique for applying default values to :variables? Tony Wasson <ajwasson@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Boes @ 2006-01-11 18:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql client: technique for applying default values to :variables?
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
@ 2006-01-11 23:51 ` Tony Wasson <ajwasson@gmail.com>
2006-01-14 14:54 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tony Wasson @ 2006-01-11 23:51 UTC (permalink / raw)
To: Jeff Boes <jeff@endpoint.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql client: technique for applying default values to :variables?
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Re: psql client: technique for applying default values to :variables? Tony Wasson <ajwasson@gmail.com>
@ 2006-01-14 14:54 ` Jeff Boes <jeff@endpoint.com>
2006-01-14 22:38 ` Re: psql client: technique for applying default values to :variables? Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Boes @ 2006-01-14 14:54 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tony Wasson wrote:
<blockquote
cite="mid6d8daee30601111551y68b2663ek6484c830f2107bee@mail.gmail.com"
type="cite">
<pre wrap="">On 1/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: 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')
</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 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.<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>
</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql client: technique for applying default values to :variables?
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Re: psql client: technique for applying default values to :variables? Tony Wasson <ajwasson@gmail.com>
2006-01-14 14:54 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
@ 2006-01-14 22:38 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-16 15:33 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Glaesemann @ 2006-01-14 22:38 UTC (permalink / raw)
To: Jeff Boes <jeff@endpoint.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql client: technique for applying default values to :variables?
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Re: psql client: technique for applying default values to :variables? Tony Wasson <ajwasson@gmail.com>
2006-01-14 14:54 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-14 22:38 ` Re: psql client: technique for applying default values to :variables? Michael Glaesemann <grzm@myrealbox.com>
@ 2006-01-16 15:33 ` Jeff Boes <jeff@endpoint.com>
2006-01-16 17:02 ` Re: psql client: technique for applying default values to :variables? Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
0 siblings, 1 reply; 6+ messages in thread
From: Jeff Boes @ 2006-01-16 15:33 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql client: technique for applying default values to :variables?
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Re: psql client: technique for applying default values to :variables? Tony Wasson <ajwasson@gmail.com>
2006-01-14 14:54 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-14 22:38 ` Re: psql client: technique for applying default values to :variables? Michael Glaesemann <grzm@myrealbox.com>
2006-01-16 15:33 ` Re: psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
@ 2006-01-16 17:02 ` Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
0 siblings, 0 replies; 6+ messages in thread
From: Andreas Seltenreich @ 2006-01-16 17:02 UTC (permalink / raw)
To: Jeff Boes <jeff@endpoint.com>; +Cc: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-01-16 17:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-11 18:48 psql client: technique for applying default values to :variables? Jeff Boes <jeff@endpoint.com>
2006-01-11 23:51 ` Tony Wasson <ajwasson@gmail.com>
2006-01-14 14:54 ` Jeff Boes <jeff@endpoint.com>
2006-01-14 22:38 ` Michael Glaesemann <grzm@myrealbox.com>
2006-01-16 15:33 ` Jeff Boes <jeff@endpoint.com>
2006-01-16 17:02 ` Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox