Обсуждение: Petition: Treat #!... shebangs as comments

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>


--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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

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

+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

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.

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

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

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

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

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

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

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

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.

Hi Andrew...

On Fri, Jul 18, 2014 at 9:37 PM, Andrew Pennebaker
<andrew.pennebaker@gmail.com> wrote:
> As a workaround, I can use this shebang hack:
... More 'this no shebang hack'.

> 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.

I see why. The lack of shebang puzzled me until I did man bash, search
for executable and found this gem " If this execution fails because
the file is not in executable format, and the file is not a directory,
it is assumed to be a shell script, a file containing shell commands.
A subshell is spawned to execute it.". Does this works in other
shells?

Francisco Olarte.

Hi Karsten:

On Fri, Jul 18, 2014 at 9:47 PM, Karsten Hilbert
<Karsten.Hilbert@gmx.net> wrote:
> Nice solution but that won't work on Windows ...

If "psql -f kk.psql" does, it works enough. ./kk.psql would not
notmally work on windows. It's been 12 years since I worked on it, but
IIRC although windows ( the OS ) recognized / as a path separator CMD
did not, so even if you set psql as a handler for .psql files it would
not work ( and, IIRC again, the customary way to exec that kind of
files would be 'kk', i.e. 'jj' not '.\jj.md' for a CMD file as windows
used to consider the CWD of the current drive before the PATH ). And
if you are using windows in POSIX mode with a bash shell it would
probably work.

Note the OP was not asking for a windows+unix shell script, but for a
psql + unix shell.

Francisco Olarte.

Hi John:

On Fri, Jul 18, 2014 at 10:41 PM, John McKown
<john.archie.mckown@gmail.com> wrote:

> 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.

I do not now, given the current trend of changing thing for the sake
of it leading to getting rid of lots of very useful features. But back
in the NT  3.51 era I was a heavy windows command prompt user. And
they have a feature which I consider superior to the irregular she
bang support in the unix world, the file type association which let
you associate an extension with an interpreter and which properly
parsed multiple arguments in the interpreter definition. I used it for
perl and awk, and even had a wrapper which let me execute a C file by
invoking it by name ( compiling it along the way ). You would still
have to overcome the problems with the weird quoting rules of every
command, but that is another tale.

Francisco Olarte.