X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E238D9DCBE4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 10:54:51 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 84027-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 10:54:55 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from qtm.net (mummra.qtm.net [216.163.32.14])
	by postgresql.org (Postfix) with SMTP id B47659DCA38
	for <pgsql-sql@postgresql.org>; Sat, 14 Jan 2006 10:54:48 -0400 (AST)
Received: (qmail 29456 invoked from network); 14 Jan 2006 14:53:44 -0000
Received: from unknown (HELO ?192.168.0.101?) (mur@216.163.42.222)
	by mummra.qtm.net with SMTP; 14 Jan 2006 14:53:44 -0000
Message-ID: <43C910B6.9080904@endpoint.com>
Date: Sat, 14 Jan 2006 09:54:46 -0500
From: Jeff Boes <jeff@endpoint.com>
Organization: End Point, Inc.
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: psql client: technique for applying default values to :variables?
References: <43C55316.60403@endpoint.com>
	<6d8daee30601111551y68b2663ek6484c830f2107bee@mail.gmail.com>
In-Reply-To: <6d8daee30601111551y68b2663ek6484c830f2107bee@mail.gmail.com>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.119,
	HTML_MESSAGE=0.001, MIME_HTML_ONLY=0.001]
X-Spam-Score: 0.121
X-Spam-Level: 
X-Archive-Number: 200601/72
X-Sequence-Number: 23861

<!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">&lt;jeff@endpoint.com&gt;</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>
=&gt; \set edate 'CURRENT_DATE::DATE'<br>
=&gt; select :edate;<br>
&nbsp;&nbsp;&nbsp; date<br>
------------<br>
&nbsp;2006-01-14<br>
<br>
<br>
</tt><br>
<pre class="moz-signature" cols="72">-- 
Jeffery Boes  &lt;&gt;&lt;
<a class="moz-txt-link-abbreviated" href="mailto:jeff@endpoint.com">jeff@endpoint.com</a>
</pre>
</body>
</html>