Thread: Enhancement to psql command, feedback.

I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,

you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

To me this looks similar to a UNIX shell script. Now, going sideways for a second, if someone wanted to create a "self contained" awk script. It would look something like:

#!/bin/awk -f

... awk code ...

When a user executes the above from the command line, the UNIX system runs the program in the first "magic" line as if the user had entered "/bin/awk -f ..." where the ... is replaced by the name of the file executed followed by the rest of the command line parameters.

I think it would be nice if psql would do the same, mainly for "consistency" with other UNIX scripting languages, such as python, perl, & gawk.

The example above would then become:

#!/bin/psql -U postgres template1 -f
REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

Does this seem reasonable to others? When I actually try the following as a "script", I get an error.

=== transcript ===

$ls -l ./x.psql; cat ./x.psql; ./x.psql
-rwxr-xr-x. 1 joarmc joarmc 40 May  9 02:55 ./x.psql
#!/usr/bin/psql -f
select * from table;
psql:./x.psql:2: ERROR:  syntax error at or near "#!/"
LINE 1: #!/usr/bin/psql -f
       ^

​I have not looked at the source yet, but it seems that it would be "easy" to implement if psql would simply ignore the first line of any file referenced via the "-f" parameter if it started with "#!" or maybe even just "#". I'm not suggesting ignoring _every_ line that start with that "magic", just the first.​

--

We all have skeletons in our closet.

Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

2018-05-09 9:59 GMT+02:00 John McKown <john.archie.mckown@gmail.com>:

I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,

you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

To me this looks similar to a UNIX shell script. Now, going sideways for a second, if someone wanted to create a "self contained" awk script. It would look something like:

#!/bin/awk -f

... awk code ...

When a user executes the above from the command line, the UNIX system runs the program in the first "magic" line as if the user had entered "/bin/awk -f ..." where the ... is replaced by the name of the file executed followed by the rest of the command line parameters.

I think it would be nice if psql would do the same, mainly for "consistency" with other UNIX scripting languages, such as python, perl, & gawk.

These languages has defined # as line comment. It is not true for SQL.

2018-05-09 9:59 GMT+02:00 John McKown <john.archie.mckown@gmail.com>:

I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,

you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

To me this looks similar to a UNIX shell script. Now, going sideways for a second, if someone wanted to create a "self contained" awk script. It would look something like:

#!/bin/awk -f

... awk code ...

When a user executes the above from the command line, the UNIX system runs the program in the first "magic" line as if the user had entered "/bin/awk -f ..." where the ... is replaced by the name of the file executed followed by the rest of the command line parameters.

I think it would be nice if psql would do the same, mainly for "consistency" with other UNIX scripting languages, such as python, perl, & gawk.

These languages has defined # as line comment. It is not true for SQL.

Regards

Pavel

On 05/09/2018 02:59 AM, John McKown wrote:

I just wanted to throw this out to the users before I made a complete fool of myself by formally requesting it. But I would like what I hope would be a minor change (enhancement) to the psql command. If you look on this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,

you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

To me this looks similar to a UNIX shell script.

Because it is a Unix shell script.  The "<< EOT" is part of a heredoc, which is designed to keep everything in one place instead of needing a second file for the SQL commands.

https://en.wikipedia.org/wiki/Here_document

(The concept is as old as computing.  Anyone who's worked on mainframes or proprietary minicomputers from DEC will instantly recognize it.)

--
Angular momentum makes the world go 'round.

Again, this is just a discussion point. And I'm quite willing to admit defeat if most people don't think that it is worth the effort.

On Wed, May 9, 2018 at 6:44 AM, John McKown <john.archie.mckown@gmail.com> wrote:

Again, this is just a discussion point. And I'm quite willing to admit defeat if most people don't think that it is worth the effort.

​-1, at least per the example.  I would not want "-U postgres" inside the file.  I tend to rely on service entries, not environment variables, and wouldn't want to hard-code them either.  While psql has grown more flow-control capabilities recently it is, in most cases, a support language for me, not a main entry point.  Shell scripts merge the per-instance run-time environment I need with the behavior the script provides - merging that I find I need more often than not and don't miss the added overhead in the few cases where it is unnecessary.

David J.

Greetings,

* John McKown (john.archie.mckown@gmail.com) wrote:
> Again, this is just a discussion point. And I'm quite willing to admit
> defeat if most people don't think that it is worth the effort.

For my 2c, at least, I do think it'd be kind of neat to have, but we'd
need a fool-proof way to realize that's how we're being called and,
ideally, that would be something we could detect without having to have
special flags for psql which anyone writing such a script would have to
be aware of.

Do you know if there's a way to detect that we're being called this
way..?

Thanks!

Stephen

However, in the second case, the "magic" first line causes psql, at present, to report an error and abort. This is why I'd like to modify how the file referenced via the -f argument is processed. That is, the first line of any file referenced & executed via the -f argument will be ignored if and only if it starts with a shebang (#!). If the first line of the file does not start with a shebang, it is processed normally as are all subsequent lines.

On Wed, 9 May 2018 at 04:00, John McKown <john.archie.mckown@gmail.com>
wrote:
> To me this looks similar to a UNIX shell script. Now, going sideways for
a second, if someone wanted to create a "self contained" awk script. It
would look something like:

> #!/bin/awk -f
> ... awk code ...

I have found it convenient when Lisp implementations (that use ; as the
comment indicator, and where # tends to mean something quite different)
have provided something like this.

I'd quite like it if I could start a script with
#!psql
or similar and have it be, yes, indeed, directly executable via psql.

There are several complications that leap out at me...