Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce. This is what I've tried so far: postgres=# \if :DBNAME == postgres unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == postgres unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if :DBNAME == 'postgres' unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == 'postgres' unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# -- Angular momentum makes the world go 'round. |
On Mon, Feb 22, 2021 at 7:19 AM Ron <[hidden email]> wrote:
> > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. You have to use a dedicated variable. Something like SELECT :'DBNAME' = 'postgres' AS is_postgres \gset \if :is_postgres [...] |
In reply to this post by Ron-2
On 2/21/21 4:18 PM, Ron wrote: > > Postgresql 12.5 > > I've got scripts which can run on multiple database (dev, test, QA, > Integration, Training, etc, etc), so of course I've got to run them like > "psql my_db_name -f script.sql". > > Of course, I sometimes forget to specify the database name, and so it > fails. > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. > > This is what I've tried so far: > > postgres=# \if :DBNAME == postgres > unrecognized value "postgres == postgres" for "\if expression": Boolean > expected > postgres@# > > postgres=# \if ':DBNAME' == postgres > unrecognized value ":DBNAME == postgres" for "\if expression": Boolean > expected > postgres@# > > postgres=# \if :DBNAME == 'postgres' > unrecognized value "postgres == postgres" for "\if expression": Boolean > expected > postgres@# > > postgres=# \if ':DBNAME' == 'postgres' > unrecognized value ":DBNAME == postgres" for "\if expression": Boolean > expected > postgres@# > > Take it up a notch? Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ |
On 2/21/21 5:26 PM, Rob Sargent wrote:
> > > On 2/21/21 4:18 PM, Ron wrote: >> >> Postgresql 12.5 >> >> I've got scripts which can run on multiple database (dev, test, QA, >> Integration, Training, etc, etc), so of course I've got to run them like >> "psql my_db_name -f script.sql". >> >> Of course, I sometimes forget to specify the database name, and so it fails. >> >> Thus, I want to add a bit to the top of the script, something like this: >> >> \if :DBNAME = postgres >> echo "must not run in postgres" >> exit >> \endif >> >> However, I can't seem to find the magic sauce. >> >> This is what I've tried so far: >> >> postgres=# \if :DBNAME == postgres >> unrecognized value "postgres == postgres" for "\if expression": Boolean >> expected >> postgres@# >> >> postgres=# \if ':DBNAME' == postgres >> unrecognized value ":DBNAME == postgres" for "\if expression": Boolean >> expected >> postgres@# >> >> postgres=# \if :DBNAME == 'postgres' >> unrecognized value "postgres == postgres" for "\if expression": Boolean >> expected >> postgres@# >> >> postgres=# \if ':DBNAME' == 'postgres' >> unrecognized value ":DBNAME == postgres" for "\if expression": Boolean >> expected >> postgres@# >> >> > > Take it up a notch? Write a script which takes the dbname and the script > name: > > /pcode/ > > #!/bin/bash -e > if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi > dbn=$1; shift; > sql=$1; shift; > psql --dbname $dbn --file $sql > > /pcode/ I thought of that, yet so earnestly want avoid Yet Another Tiny Script. -- Angular momentum makes the world go 'round. |
>> >> Take it up a notch? Write a script which takes the dbname and the >> script name: >> >> /pcode/ >> >> #!/bin/bash -e >> if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi >> dbn=$1; shift; >> sql=$1; shift; >> psql --dbname $dbn --file $sql >> >> /pcode/ > > I thought of that, yet so earnestly want avoid Yet Another Tiny Script. > Or make it /really/ fancy: use proper arg parsing; check for existence of the sql script; add a usage function; split stdout/stderr... No end of fun. |
In reply to this post by Julien Rouhaud
On 2/21/21 5:26 PM, Julien Rouhaud wrote:
On Mon, Feb 22, 2021 at 7:19 AM Ron [hidden email] wrote:Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce.You have to use a dedicated variable. Something like SELECT :'DBNAME' = 'postgres' AS is_postgres \gset That works... \if :is_postgres [...] That almost works, My script: $ cat test_pg.sql SELECT :'DBNAME' = 'postgres' AS is_postgres \gset \echo :is_postgres \if :is_postgres \echo 'connected to postgres' exit \else \echo 'not connected to postgres' \endif The output: $ psql12 -f test_pg.sql t connected to postgres got here psql:test_pg.sql:15: ERROR: syntax error at or near "exit" LINE 1: exit --
Angular momentum makes the world go 'round. |
In reply to this post by lup
Rob Sargent <[hidden email]> writes: >>> >>> Take it up a notch? Write a script which takes the dbname and the >>> script name: >>> >>> /pcode/ >>> >>> #!/bin/bash -e >>> if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi >>> dbn=$1; shift; >>> sql=$1; shift; >>> psql --dbname $dbn --file $sql >>> >>> /pcode/ >> >> I thought of that, yet so earnestly want avoid Yet Another Tiny Script. >> > Isn't it a toss-up with putting the check in every sql script? > Or make it /really/ fancy: use proper arg parsing; check for existence > of the sql script; add a usage function; split stdout/stderr... No end > of fun. that would be my approach. A general purpose 'launcher' script that does argument checking, logging and reporting. Stick it in your bin directory and then call that instead of psql directly. Now all your SQL scripts are just DDL/DML statements. Nice thing is you can do it in whatever scripting language your most comfortable with - bash, perl, ruby, python, whatever and it is available for whatever project your working on. -- Tim Cross |
In reply to this post by lup
On 2/21/21 6:49 PM, Rob Sargent wrote:
> > >>> >>> Take it up a notch? Write a script which takes the dbname and the >>> script name: >>> >>> /pcode/ >>> >>> #!/bin/bash -e >>> if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi >>> dbn=$1; shift; >>> sql=$1; shift; >>> psql --dbname $dbn --file $sql >>> >>> /pcode/ >> >> I thought of that, yet so earnestly want avoid Yet Another Tiny Script. >> > Isn't it a toss-up with putting the check in every sql script? I was hoping to "\include" that at the top of each script, since doing that once is easier than remembering to use the script each time. > Or make it /really/ fancy: use proper arg parsing; check for existence of > the sql script; add a usage function; split stdout/stderr... No end of fun. -- Angular momentum makes the world go 'round. |
In reply to this post by Ron-2
On Mon, Feb 22, 2021 at 9:00 AM Ron <[hidden email]> wrote:
> > On 2/21/21 5:26 PM, Julien Rouhaud wrote: > > On Mon, Feb 22, 2021 at 7:19 AM Ron <[hidden email]> wrote: > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. > > You have to use a dedicated variable. Something like > > SELECT :'DBNAME' = 'postgres' AS is_postgres \gset > > > That works... > > \if :is_postgres > [...] > > > That almost works, > > My script: > $ cat test_pg.sql > SELECT :'DBNAME' = 'postgres' AS is_postgres \gset > \echo :is_postgres > \if :is_postgres > \echo 'connected to postgres' > exit > \else > \echo 'not connected to postgres' > \endif > > The output: > $ psql12 -f test_pg.sql > t > connected to postgres > got here > psql:test_pg.sql:15: ERROR: syntax error at or near "exit" > LINE 1: exit Well, the supported commands did work. You should probably look at https://www.postgresql.org/docs/current/app-psql.html, you'd see that "exit" is not a supported command and you should instead use \q[uit]. I recommend looking at the semantics of \quit though, given your next message mentioning \include. |
On 2/21/21 7:19 PM, Julien Rouhaud wrote:
[snip] > Well, the supported commands did work. You should probably look at > https://www.postgresql.org/docs/current/app-psql.html, you'd see that > "exit" is not a supported command and you should instead use \q[uit]. > I recommend looking at the semantics of \quit though, given your next > message mentioning \include. I assumed "exit" is a valid command because I do this so often in bash... :( -- Angular momentum makes the world go 'round. |
In reply to this post by Julien Rouhaud
Julien Rouhaud schrieb am 22.02.2021 um 02:19:
>> The output: >> $ psql12 -f test_pg.sql >> t >> connected to postgres >> got here >> psql:test_pg.sql:15: ERROR: syntax error at or near "exit" >> LINE 1: exit > > Well, the supported commands did work. You should probably look at > https://www.postgresql.org/docs/current/app-psql.html, you'd see that > "exit" is not a supported command and you should instead use \q[uit]. > I recommend looking at the semantics of \quit though, given your next > message mentioning \include. Since v11 "exit" and "quit" are allowed if no previous input was given on the line. It's only documented in the release notes[1] though, not in the manual. Thomas [1] https://www.postgresql.org/docs/release/11.0/ |
Free forum by Nabble | Edit this page |