Petition: Treat #!... shebangs as comments

On Fri, Jul 18, 2014 at 10:16 AM, Andrew Pennebaker <
andrew.pennebaker@gmail.com> wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line of
a file as a comment? This would enable .psql scripts to be run with
dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

+1

On 07/18/2014 08:16 AM, Andrew Pennebaker wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line
of a file as a comment? This would enable .psql scripts to be run with
dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

Would not doing the below accomplish the same thing for you?

http://www.postgresql.org/docs/9.3/interactive/app-psql.html

"Because of these legacy behaviors, putting more than one command in the
-c string often has unexpected results. It's better to feed multiple
commands to psql's standard input, either using echo as illustrated
above, or via a shell here-document, for example:

psql <<EOF
\x
SELECT * FROM foo;
EOF
"

So:

#!/bin/sh
psql -d production -U aklaver <<EOF
\x
SELECT * FROM plant1;
EOF

--
Cheers,

Andrew Pennebaker
www.yellosoft.us <http://www.yellosoft.us&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 11:32 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 07/18/2014 08:16 AM, Andrew Pennebaker wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line
of a file as a comment? This would enable .psql scripts to be run with
dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

Would not doing the below accomplish the same thing for you?

Snip ...

Actually, it wouldn't. Andrew's request is to be able to take advantage of
Unix's method of using all text after a #! on the first line of a file to
actually mean to execute the program mentioned after the #! and then feed
the file to mentioned program. So having a script file like

select * from foo;

modified to

#!/usr/local/pgsql/bin/psql
select * from foo;

would result in a file that could be executed directly on an Unix platform,
yet still be capable of being using on a Window's platform using the 'psql
-f file' style of execution.

Sounds like a good idea. A more generic approach is to have '#' itself be a
comment delimiter. But I suspect doing such would be in conflict with the
SQL standard.

Hi:

On Fri, Jul 18, 2014 at 5:16 PM, Andrew Pennebaker
<andrew.pennebaker@gmail.com> wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line of a
file as a comment? This would enable .psql scripts to be run with dot-slash
notation preferred by many unix users:

./script.psql

Dot-slash is not a notation, it's a requisite if you do ( correctly )
not have . in your $PATH.

Anyway, this is a little bit complex, as psql many times needs arguments.

It's been pointed it can be done with a shell script. If you can live
with a little noise you can have it dual ( The \r makes it ignore the
previous lines, the \q makes it exit before reading the EOF line ):

~/tmp $ cat kk.sql
#!/bin/bash
psql service=redacted <<EOF
\r
select 2*3*7;
\q
EOF
~/tmp $ psql -f kk.sql service=redacted
Query buffer reset (cleared).
?column?
----------
42
(1 row)

~/tmp $ psql service=redacted < kk.sql
Query buffer reset (cleared).
?column?
----------
42
(1 row)

~/tmp $ ./kk.sql
Query buffer reset (cleared).
?column?
----------
42
(1 row)

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line
of a file as a comment? This would enable .psql scripts to be run with
dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

Would not doing the below accomplish the same thing for you?

http://www.postgresql.org/docs/9.3/interactive/app-psql.html

"Because of these legacy behaviors, putting more than one command in the -c
string often has unexpected results. It's better to feed multiple commands
to psql's standard input, either using echo as illustrated above, or via a
shell here-document, for example:

psql <<EOF
\x
SELECT * FROM foo;
EOF
"

So:

#!/bin/sh
psql -d production -U aklaver <<EOF
\x
SELECT * FROM plant1;
EOF

I think the OP is talking about executable scripts so both of

$> psql -f the-file.sql

and

$> ./the-file.sql

(where the-file.sql starts with "#!/usr/bin/env psql")

would work given that the-file.sql has got execute permission.

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/18/2014 08:52 AM, Karsten Hilbert wrote:

On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote:

I think the OP is talking about executable scripts so both of

$> psql -f the-file.sql

and

$> ./the-file.sql

(where the-file.sql starts with "#!/usr/bin/env psql")

would work given that the-file.sql has got execute permission.

Yea, it finally dawned on me what was being asked, so ignore my previous
post.

Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

+1

Skickat från min iPhone

18 jul 2014 kl. 17:58 skrev Adrian Klaver <adrian.klaver@aklaver.com>:

On 07/18/2014 08:52 AM, Karsten Hilbert wrote:
On Fri, Jul 18, 2014 at 08:32:53AM -0700, Adrian Klaver wrote:

I think the OP is talking about executable scripts so both of

$> psql -f the-file.sql

and

$> ./the-file.sql

(where the-file.sql starts with "#!/usr/bin/env psql")

would work given that the-file.sql has got execute permission.

Yea, it finally dawned on me what was being asked, so ignore my previous post.

Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I personally like Francisco Olarte's approach. Hashbang's don't support
arguments well (
http://stackoverflow.com/questions/4303128/how-to-use-multiple-arguments-with-a-shebang-i-e)
and being able to put JUST psql as the command to execute the script
doesn't scale across environments. Previously I've just used a quick
wrapper:

https://gist.github.com/frankpinto/3427cf769a72ef25ffac

It can be modified to accept arguments for the script name, run a sql
script by the same name, have a default environment, etc.

Frank

On Fri, Jul 18, 2014 at 10:43 AM, Martin Gudmundsson <
martingudmundsson@gmail.com> wrote:

Frank Pinto wrote

I personally like Francisco Olarte's approach. Hashbang's don't support
arguments well (
http://stackoverflow.com/questions/4303128/how-to-use-multiple-arguments-with-a-shebang-i-e)
and being able to put JUST psql as the command to execute the script
doesn't scale across environments. Previously I've just used a quick
wrapper:

https://gist.github.com/frankpinto/3427cf769a72ef25ffac

It can be modified to accept arguments for the script name, run a sql
script by the same name, have a default environment, etc.

Frank

While I agree with the sentiment unless someone can present a reason why
allowing and then ignoring a she-bang is a terrible idea then this seems
like a case for letting end-users decide what is best for themselves. The
issue then is that apparently this isn't exactly high on anyone's list of
ToDo items so unless the OP or one of the +1 people are willing to supply a
patch the odds that it gets done decreases quite quickly.

As an aside - putting an "sql" extension onto a file that uses psql commands
and features seems wrong...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Petition-Treat-shebangs-as-comments-tp5811962p5811987.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 10:51 AM, Francisco Olarte
<folarte@peoplecall.com> wrote:

Hi:

On Fri, Jul 18, 2014 at 5:16 PM, Andrew Pennebaker
<andrew.pennebaker@gmail.com> wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line of a
file as a comment? This would enable .psql scripts to be run with dot-slash
notation preferred by many unix users:

./script.psql

Dot-slash is not a notation, it's a requisite if you do ( correctly )
not have . in your $PATH.

Anyway, this is a little bit complex, as psql many times needs arguments.

true, but pretty much everything you might need can be handled via the
environment and the script itself. there are plenty of good reasons
do this and I can't think of any not to.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

18 jul 2014 kl. 17:31 skrev Dennis Jenkins <dennis.jenkins.75@gmail.com>:

On Fri, Jul 18, 2014 at 10:16 AM, Andrew Pennebaker <andrew.pennebaker@gmail.com> wrote:
Could we please have the PostgreSQL lexer treat #!... on the first line of a file as a comment? This would enable .psql scripts to be run with dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

+1

+1, Sounds great!
Even though you can accomplish most things in other ways, this seems like the easiest in many scenarios.

As a workaround, I can use this shebang hack:

$ cat hello.psql
--() { :; }; exec psql -f "$0"

SELECT 'Hello World!';

$ ./hello.psql
?column?
--------------
Hello World!
(1 row)

$ psql -f hello.psql
?column?
--------------
Hello World!
(1 row)

But I would prefer to use a traditional (#!/usr/bin/env psql -f) shebang.
It took a few hours on irc to hack this one together.

On Fri, Jul 18, 2014 at 2:28 PM, Martin Gudmundsson <
martingudmundsson@gmail.com> wrote:

18 jul 2014 kl. 17:31 skrev Dennis Jenkins <dennis.jenkins.75@gmail.com>:

On Fri, Jul 18, 2014 at 10:16 AM, Andrew Pennebaker <
andrew.pennebaker@gmail.com> wrote:

Could we please have the PostgreSQL lexer treat #!... on the first line
of a file as a comment? This would enable .psql scripts to be run with
dot-slash notation preferred by many unix users:

./script.psql

While still allowing the traditional (and Windows compatible) style:

psql -f script.psql

+1

+1, Sounds great!
Even though you can accomplish most things in other ways, this seems like
the easiest in many scenarios.

--
Cheers,

Andrew Pennebaker
www.yellosoft.us

On Fri, Jul 18, 2014 at 10:41:30AM -0700, David G Johnston wrote:

As an aside - putting an "sql" extension onto a file that uses psql commands
and features seems wrong...

Weeell, yeah, but, it can make life easier. Take Midnight
Commander for a start -- it recognizes *SQL files by their
.sql extension for syntax highlighting. It doesn't recognize
.psql ones as yet.

Now, of course, I should work on getting mc to

- recognize *SQL files based on content
- recognize .psql files as containing (one "dialect" of) SQL
- fully parse .psql files as "Postgre"-SQL

but until that happens I am quite happy with mc properly
highlighting (most SQL) in my .sql-named psql files ;-)

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 05:51:35PM +0200, Francisco Olarte wrote:

Anyway, this is a little bit complex, as psql many times needs arguments.

Agreed.

Could we please have the PostgreSQL lexer treat #!... on the first line of a
file as a comment? This would enable .psql scripts to be run with dot-slash
notation

...

It's been pointed it can be done with a shell script. If you can live
with a little noise you can have it dual ( The \r makes it ignore the
previous lines, the \q makes it exit before reading the EOF line ):

~/tmp $ cat kk.sql
#!/bin/bash
psql service=redacted <<EOF
\r
select 2*3*7;
\q
EOF

Nice solution but that won't work on Windows ...

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 2:37 PM, Andrew Pennebaker
<andrew.pennebaker@gmail.com> wrote:

As a workaround, I can use this shebang hack:

$ cat hello.psql
--() { :; }; exec psql -f "$0"

I hope that isn't patented! <grin?> "Cause I'm gonna use it when I need to.

SELECT 'Hello World!';

$ ./hello.psql
?column?
--------------
Hello World!
(1 row)

$ psql -f hello.psql
?column?
--------------
Hello World!
(1 row)

But I would prefer to use a traditional (#!/usr/bin/env psql -f) shebang. It
took a few hours on irc to hack this one together.

FWIW - I like #! also. Even though it may cause the Windows users to
want something equivalent. Assuming there are any Windows people who
really use a command prompt.

--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G Johnston <david.g.johnston@gmail.com> writes:

While I agree with the sentiment unless someone can present a reason why
allowing and then ignoring a she-bang is a terrible idea then this seems
like a case for letting end-users decide what is best for themselves. The
issue then is that apparently this isn't exactly high on anyone's list of
ToDo items so unless the OP or one of the +1 people are willing to supply a
patch the odds that it gets done decreases quite quickly.

It's not just that it's "not high on anyone's priority list", it's that
we'd want to be sure that the patch didn't break any existing use-cases
or make things unmaintainable. (This isn't exactly a negligible concern
considering that Postgres thinks #! is a legal operator name.)

The proposal seems a bit reminiscent of the requests we've had for psql
to ignore a UTF8 byte-order mark (BOM) at the start of an input file.
The details are considerably different of course, but anyone who wants
to push this idea should probably read the archives to see why that idea
still hasn't made it in.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jul 18, 2014 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

David G Johnston <david.g.johnston@gmail.com> writes:

While I agree with the sentiment unless someone can present a reason why
allowing and then ignoring a she-bang is a terrible idea then this seems
like a case for letting end-users decide what is best for themselves. The
issue then is that apparently this isn't exactly high on anyone's list of
ToDo items so unless the OP or one of the +1 people are willing to supply a
patch the odds that it gets done decreases quite quickly.

It's not just that it's "not high on anyone's priority list", it's that
we'd want to be sure that the patch didn't break any existing use-cases
or make things unmaintainable. (This isn't exactly a negligible concern
considering that Postgres thinks #! is a legal operator name.)

The proposal seems a bit reminiscent of the requests we've had for psql
to ignore a UTF8 byte-order mark (BOM) at the start of an input file.
The details are considerably different of course, but anyone who wants
to push this idea should probably read the archives to see why that idea
still hasn't made it in.

I think the operator objection is specious -- ISTM there is no
scenario where an operator could be legally parsed without seeing a
keyword first.

OTOH (recalling the BOM discussion), the situation with stdin is
hopeless -- only psql -f or \i could strip out the shebang. And
herein lies the problem: there's no easy way to pass multiple scripts
to single psql invocation except by abusing stdin which in turn
prevents psql from knowing what the file boundaries are. So scripts
written to be run this way are kind of limited in how they could be
used. I still think it's a neat idea though.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Merlin Moncure <mmoncure@gmail.com> writes:

On Fri, Jul 18, 2014 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

It's not just that it's "not high on anyone's priority list", it's that
we'd want to be sure that the patch didn't break any existing use-cases
or make things unmaintainable. (This isn't exactly a negligible concern
considering that Postgres thinks #! is a legal operator name.)

I think the operator objection is specious -- ISTM there is no
scenario where an operator could be legally parsed without seeing a
keyword first.

[ pokes at it... ] Yeah, perhaps so. I had been thinking that it might
be possible for a SQL command to be continued across files, but the way
psql's MainLoop works, that's not the case. If you do something like

SELECT 42
\i foo
;

whatever is read from foo is sent to the backend separately, it's not
folded into the outer file's SELECT. So this may be a non-problem,
at least till such time as somebody wishes they could change that.

OTOH (recalling the BOM discussion), the situation with stdin is
hopeless -- only psql -f or \i could strip out the shebang.

Right, but the use-case for this is (I suppose)

#! /path/to/psql -f

which according to the shell docs I'm looking at will result in
"./foo.sql other-args" turning into

/path/to/psql -f ./foo.sql other-args

So it seems like it could work, and the complaint about needing additional
connection parameters isn't that strong either.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Merlin:

On Fri, Jul 18, 2014 at 9:23 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
....snip, snip....

Anyway, this is a little bit complex, as psql many times needs arguments.

true, but pretty much everything you might need can be handled via the
environment and the script itself. there are plenty of good reasons
do this and I can't think of any not to.

Oh, don't get me wrong. I would like it and use it, I use a lot of
she-bangs, even on linux which has the space-pasting behaviour, and I
would not have problems with the default args. The only problems I see
is more features, more places for bugs to hide, and a somehow
difficult to use feature which would lead to a lot of surprises for
the unexperienced. After all this years I still got puzzled for some
of the she-bang problems. I doubt anyone would have backwards
compatibility problems. I do not have cons, but I don't see that much
pros.

I would also point that in the many scripts I have running against
postgres, not a single one of them would benefit from these, all need
some heading, tailing, redirecting or whatevering intermixed with the
DB access, although I suppose some of the more db-oriented ones could
be redone to use this. But I ( personally ) would stick with doing
them in shell, one more system of tricky variable interpolations and
redirections would be too much for me.

Regards.

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general