Sorry I don't know if this is right list.

I use scripts of such a kind (I say "SQLbang")

#!/usr/local/bin/psql -qQ
\a \t \pset fieldsep ' '

\set router '\'' :1 '\''

SELECT ispdb_sfbsdr_allow(network(inet),niface)
 FROM ispdb_sfbsdr_riaddr, nets
 WHERE nrouter = :router 
   AND int_type = index_int_type('int')
   AND network(inet) <<= network(net)
   AND nets.control

Parameters after sqlbang's name goes to :1, :2 so on.
This is patch:

--- doc/src/sgml/ref/psql-ref.sgml	Sun Apr  1 23:17:30 2001
+++ doc/src/sgml/ref/psql-ref.sgml	Thu Apr 26 05:46:20 2001
@@ -1406,6 +1406,22 @@
 
 
     <varlistentry>
+      <term>-Q <replaceable class="parameter">filename</replaceable></term>
+      <listitem>
+      <para>
+      Use the file <replaceable class="parameter">filename</replaceable>
+      as the source of queries instead of reading queries interactively.
+      After the file is processed, <application>psql</application> terminates.
+      This in many ways similar to the <literal>-f</literal> flag,
+      but for use in <quote>sqlbang</quote> scripts.
+      First script's psrameters will be assigned to
+      <literal>:1</literal> .. <literal>:9</literal> variables.
+      </para>
+      </listitem>
+    </varlistentry>
+
+
+    <varlistentry>
       <term>-R, --record-separator <replaceable class="parameter">separator</replaceable></term>
       <listitem>
       <para>
--- src/bin/psql/help.c	Thu Oct 25 09:49:54 2001
+++ src/bin/psql/help.c	Sun Mar 17 02:56:34 2002
@@ -112,6 +112,7 @@
 
 	puts(_("  -P VAR[=ARG]    Set printing option 'VAR' to 'ARG' (see \\pset command)"));
 	puts(_("  -q              Run quietly (no messages, only query output)"));
+	puts(_("  -Q              Like -f, for scripts, arguments :1 .. :9"));
 	puts(_("  -R STRING       Set record separator (default: newline) (-P recordsep=)"));
 	puts(_("  -s              Single step mode (confirm each query)"));
 	puts(_("  -S              Single line mode (end of line terminates SQL command)"));
--- src/bin/psql/mainloop.c	Sun Apr  1 23:17:32 2001
+++ src/bin/psql/mainloop.c	Thu Apr 26 05:51:46 2001
@@ -53,7 +53,7 @@
 	const char *var;
 	volatile unsigned int bslash_count = 0;
 
-	int			i,
+	int			i,j,
 				prevlen,
 				thislen;
 
@@ -91,7 +91,7 @@
 
 
 	/* main loop to get queries and execute them */
-	while (1)
+	for(j = 0;;j++)
 	{
 #ifndef WIN32
 
@@ -189,6 +189,11 @@
 				line = gets_fromFile(source);
 		}
 
+		if (!j && line && line[0] == '#' && line[1] == '!')
+		{
+			free(line);
+			continue;
+		}
 
 		/* Setting this will not have effect until next line. */
 		die_on_error = GetVariableBool(pset.vars, "ON_ERROR_STOP");
--- src/bin/psql/startup.c.orig	Mon Nov  5 20:46:31 2001
+++ src/bin/psql/startup.c	Sun Mar 17 03:28:01 2002
@@ -237,7 +237,7 @@
 	 */
 
 	/*
-	 * process file given by -f
+	 * process file given by -f or -Q
 	 */
 	if (options.action == ACT_FILE && strcmp(options.action_string, "-") != 0)
 	{
@@ -369,19 +369,19 @@
 	extern char *optarg;
 	extern int	optind;
 	int			c;
-	bool		used_old_u_option = false;
+	bool		used_old_u_option = false, sqlbang = false;
 
 	memset(options, 0, sizeof *options);
 
 #ifdef HAVE_GETOPT_LONG
-	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?", long_options, &optindex)) != -1)
+	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qQ:R:sStT:uU:v:VWxX?", long_options, &optindex)) != -1)
 #else							/* not HAVE_GETOPT_LONG */
 
 	/*
 	 * Be sure to leave the '-' in here, so we can catch accidental long
 	 * options.
 	 */
-	while ((c = getopt(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?-")) != -1)
+	while ((c = getopt(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qQ:R:sStT:uU:v:VWxX?-")) != -1)
 #endif   /* not HAVE_GETOPT_LONG */
 	{
 		switch (c)
@@ -464,6 +464,12 @@
 			case 'q':
 				SetVariableBool(pset.vars, "QUIET");
 				break;
+			case 'Q':
+				SetVariableBool(pset.vars, "ON_ERROR_STOP");
+				options->action = ACT_FILE;
+				options->action_string = optarg;
+				sqlbang = true;
+				break;
 			case 'R':
 				pset.popt.topt.recordSep = xstrdup(optarg);
 				break;
@@ -563,21 +569,45 @@
 		}
 	}
 
-	/*
-	 * if we still have arguments, use it as the database name and
-	 * username
-	 */
-	while (argc - optind >= 1)
+	if (sqlbang)
 	{
-		if (!options->dbname)
-			options->dbname = argv[optind];
-		else if (!options->username)
-			options->username = argv[optind];
-		else if (!QUIET())
-			fprintf(stderr, gettext("%s: warning: extra option %s ignored\n"),
-					pset.progname, argv[optind]);
+		char optname[] = "1";
+		while (argc - optind >= 1)
+		{
+			if (optname[0] <= '9')
+			{
+				if (!SetVariable(pset.vars, optname, argv[optind]))
+				{
+					fprintf(stderr, "%s: could not set variable %s\n",
+							pset.progname, optname);
+					exit(EXIT_FAILURE);
+				}
+			}
+			else if (!QUIET())
+				fprintf(stderr, "%s: warning: extra option %s ignored\n",
+						pset.progname, argv[optind]);
+			optname[0]++;
+			optind++;
+		}
+	}
+	else
+	{
+		/*
+		 * if we still have arguments, use it as the database name and
+		 * username
+		 */
+		while (argc - optind >= 1)
+		{
+			if (!options->dbname)
+				options->dbname = argv[optind];
+			else if (!options->username)
+				options->username = argv[optind];
+			else if (!QUIET())
+				fprintf(stderr, gettext("%s: warning: extra option %s ignored\n"),
+						pset.progname, argv[optind]);
 
-		optind++;
+			optind++;
+		}
 	}
 
 	if (used_old_u_option && !QUIET())

I propose to include this feature.
Sorry for bad English.

-- 
@BABOLO      http://links.ru/

Sun, 7 Apr 2002 06:31:43 +0400 (MSD)
"."@babolo.ru
sqlbang

Can someone comment on this feature?

---------------------------------------------------------------------------

"."@babolo.ru wrote:
> Sorry I don't know if this is right list.
> 
> I use scripts of such a kind (I say "SQLbang")
> 
> #!/usr/local/bin/psql -qQ
> \a \t \pset fieldsep ' '
> 
> \set router '\'' :1 '\''
> 
> SELECT ispdb_sfbsdr_allow(network(inet),niface)
>  FROM ispdb_sfbsdr_riaddr, nets
>  WHERE nrouter = :router 
>    AND int_type = index_int_type('int')
>    AND network(inet) <<= network(net)
>    AND nets.control
> 
> Parameters after sqlbang's name goes to :1, :2 so on.
> This is patch:
> 
> --- doc/src/sgml/ref/psql-ref.sgml	Sun Apr  1 23:17:30 2001
> +++ doc/src/sgml/ref/psql-ref.sgml	Thu Apr 26 05:46:20 2001
> @@ -1406,6 +1406,22 @@
>  
>  
>      <varlistentry>
> +      <term>-Q <replaceable class="parameter">filename</replaceable></term>
> +      <listitem>
> +      <para>
> +      Use the file <replaceable class="parameter">filename</replaceable>
> +      as the source of queries instead of reading queries interactively.
> +      After the file is processed, <application>psql</application> terminates.
> +      This in many ways similar to the <literal>-f</literal> flag,
> +      but for use in <quote>sqlbang</quote> scripts.
> +      First script's psrameters will be assigned to
> +      <literal>:1</literal> .. <literal>:9</literal> variables.
> +      </para>
> +      </listitem>
> +    </varlistentry>
> +
> +
> +    <varlistentry>
>        <term>-R, --record-separator <replaceable class="parameter">separator</replaceable></term>
>        <listitem>
>        <para>
> --- src/bin/psql/help.c	Thu Oct 25 09:49:54 2001
> +++ src/bin/psql/help.c	Sun Mar 17 02:56:34 2002
> @@ -112,6 +112,7 @@
>  
>  	puts(_("  -P VAR[=ARG]    Set printing option 'VAR' to 'ARG' (see \\pset command)"));
>  	puts(_("  -q              Run quietly (no messages, only query output)"));
> +	puts(_("  -Q              Like -f, for scripts, arguments :1 .. :9"));
>  	puts(_("  -R STRING       Set record separator (default: newline) (-P recordsep=)"));
>  	puts(_("  -s              Single step mode (confirm each query)"));
>  	puts(_("  -S              Single line mode (end of line terminates SQL command)"));
> --- src/bin/psql/mainloop.c	Sun Apr  1 23:17:32 2001
> +++ src/bin/psql/mainloop.c	Thu Apr 26 05:51:46 2001
> @@ -53,7 +53,7 @@
>  	const char *var;
>  	volatile unsigned int bslash_count = 0;
>  
> -	int			i,
> +	int			i,j,
>  				prevlen,
>  				thislen;
>  
> @@ -91,7 +91,7 @@
>  
>  
>  	/* main loop to get queries and execute them */
> -	while (1)
> +	for(j = 0;;j++)
>  	{
>  #ifndef WIN32
>  
> @@ -189,6 +189,11 @@
>  				line = gets_fromFile(source);
>  		}
>  
> +		if (!j && line && line[0] == '#' && line[1] == '!')
> +		{
> +			free(line);
> +			continue;
> +		}
>  
>  		/* Setting this will not have effect until next line. */
>  		die_on_error = GetVariableBool(pset.vars, "ON_ERROR_STOP");
> --- src/bin/psql/startup.c.orig	Mon Nov  5 20:46:31 2001
> +++ src/bin/psql/startup.c	Sun Mar 17 03:28:01 2002
> @@ -237,7 +237,7 @@
>  	 */
>  
>  	/*
> -	 * process file given by -f
> +	 * process file given by -f or -Q
>  	 */
>  	if (options.action == ACT_FILE && strcmp(options.action_string, "-") != 0)
>  	{
> @@ -369,19 +369,19 @@
>  	extern char *optarg;
>  	extern int	optind;
>  	int			c;
> -	bool		used_old_u_option = false;
> +	bool		used_old_u_option = false, sqlbang = false;
>  
>  	memset(options, 0, sizeof *options);
>  
>  #ifdef HAVE_GETOPT_LONG
> -	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?", long_options, &optindex)) != -1)
> +	while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qQ:R:sStT:uU:v:VWxX?", long_options, &optindex)) != -1)
>  #else							/* not HAVE_GETOPT_LONG */
>  
>  	/*
>  	 * Be sure to leave the '-' in here, so we can catch accidental long
>  	 * options.
>  	 */
> -	while ((c = getopt(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?-")) != -1)
> +	while ((c = getopt(argc, argv, "aAc:d:eEf:F:h:Hlno:p:P:qQ:R:sStT:uU:v:VWxX?-")) != -1)
>  #endif   /* not HAVE_GETOPT_LONG */
>  	{
>  		switch (c)
> @@ -464,6 +464,12 @@
>  			case 'q':
>  				SetVariableBool(pset.vars, "QUIET");
>  				break;
> +			case 'Q':
> +				SetVariableBool(pset.vars, "ON_ERROR_STOP");
> +				options->action = ACT_FILE;
> +				options->action_string = optarg;
> +				sqlbang = true;
> +				break;
>  			case 'R':
>  				pset.popt.topt.recordSep = xstrdup(optarg);
>  				break;
> @@ -563,21 +569,45 @@
>  		}
>  	}
>  
> -	/*
> -	 * if we still have arguments, use it as the database name and
> -	 * username
> -	 */
> -	while (argc - optind >= 1)
> +	if (sqlbang)
>  	{
> -		if (!options->dbname)
> -			options->dbname = argv[optind];
> -		else if (!options->username)
> -			options->username = argv[optind];
> -		else if (!QUIET())
> -			fprintf(stderr, gettext("%s: warning: extra option %s ignored\n"),
> -					pset.progname, argv[optind]);
> +		char optname[] = "1";
> +		while (argc - optind >= 1)
> +		{
> +			if (optname[0] <= '9')
> +			{
> +				if (!SetVariable(pset.vars, optname, argv[optind]))
> +				{
> +					fprintf(stderr, "%s: could not set variable %s\n",
> +							pset.progname, optname);
> +					exit(EXIT_FAILURE);
> +				}
> +			}
> +			else if (!QUIET())
> +				fprintf(stderr, "%s: warning: extra option %s ignored\n",
> +						pset.progname, argv[optind]);
> +			optname[0]++;
> +			optind++;
> +		}
> +	}
> +	else
> +	{
> +		/*
> +		 * if we still have arguments, use it as the database name and
> +		 * username
> +		 */
> +		while (argc - optind >= 1)
> +		{
> +			if (!options->dbname)
> +				options->dbname = argv[optind];
> +			else if (!options->username)
> +				options->username = argv[optind];
> +			else if (!QUIET())
> +				fprintf(stderr, gettext("%s: warning: extra option %s ignored\n"),
> +						pset.progname, argv[optind]);
>  
> -		optind++;
> +			optind++;
> +		}
>  	}
>  
>  	if (used_old_u_option && !QUIET())
> 
> I propose to include this feature.
> Sorry for bad English.
> 
> -- 
> @BABOLO      http://links.ru/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 17 Apr 2002 23:49:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [INTERFACES] sqlbang
Nobody interested?

I prepare my ISP Management System (ispms)
to publish and want reduce number of patches
needed to install it.

SQLbangs widely ised in it:
0cicuta~(1)>grep -r '^#\!/usr/local/bin/psql' /usr/local/libexec/ispms | wc -l
      61
The most reason for patch are paremeters,
because without parameters 

#!/usr/local/bin/psql -flags

can be substituted for:

#!/bin/sh
/usr/local/bin/psql -flags << "EOF"

but for substitute shell's ${1}, ${2} so on
for real parameters "EOF" in above example
MUST be without quotes.
So all SQL text will be preprocessored
by shell. Things are worst - some
of SQLbangs prepare simple shell's
programs with some shell
variables and quotes in it which must
be escaped to be not expanded
when SQL executes.

Yes, I have m4 build system to do such
escaping:
0cicuta~(2)>find w/ispms -name \*.m4 | wc -l
      71
for WWW interface mostly, but without
SQLbangs escape level will be 1 level more,
shell has some errors (or features, I dont
know) with high level escaping and
I do not want depend on this errors
(or features) in base ispms system
(WWW interface has low rights)

Bruce Momjian writes:
> Can someone comment on this feature?
> 
> ---------------------------------------------------------------------------
> 
> "."@babolo.ru wrote:
> > Sorry I don't know if this is right list.
> > 
> > I use scripts of such a kind (I say "SQLbang")
> > 
> > #!/usr/local/bin/psql -qQ
> > \a \t \pset fieldsep ' '
> > 
> > \set router '\'' :1 '\''
> > 
> > SELECT ispdb_sfbsdr_allow(network(inet),niface)
> >  FROM ispdb_sfbsdr_riaddr, nets
> >  WHERE nrouter = :router 
> >    AND int_type = index_int_type('int')
> >    AND network(inet) <<= network(net)
> >    AND nets.control
> > 
> > Parameters after sqlbang's name goes to :1, :2 so on.
<patch skiped>
> > I propose to include this feature.
> > Sorry for bad English.

-- 
@BABOLO      http://links.ru/

Sun, 21 Apr 2002 03:33:06 +0400 (MSD)
"."@babolo.ru
Re: [INTERFACES] sqlbang
.@babolo.ru writes:

> The most reason for patch are paremeters,

Parameters already exist:

peter ~$ cat test.sql
\echo :x1
\echo :x2
peter ~$ pg-install/bin/psql -f test.sql -v x1=foo -v x2=bar
foo
bar

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 21 Apr 2002 00:53:28 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: [INTERFACES] sqlbang
Peter Eisentraut writes:
> .@babolo.ru writes:
> > The most reason for patch are paremeters,
> 
> Parameters already exist:
> 
> peter ~$ cat test.sql
> \echo :x1
> \echo :x2
> peter ~$ pg-install/bin/psql -f test.sql -v x1=foo -v x2=bar
> foo
> bar
OK, positional parameters

-- 
@BABOLO      http://links.ru/

Sun, 21 Apr 2002 23:34:41 +0400 (MSD)
"."@babolo.ru
Re: [INTERFACES] sqlbang