Re: Enhancement to psql command, feedback.

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Wed, 9 May 2018 10:05:25 +0200

2018-05-09 9:59 GMT+02:00 John McKown <john.archie.mckown@xxxxxxxxx>:
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

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