Script checking to see what database it's connected to

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Script checking to see what database it's connected to

Ron-2

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.


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

Julien Rouhaud
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
[...]


lup
Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

lup
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/


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

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


lup
Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

lup


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


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

Ron-2
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.
Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

Tim Cross
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


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

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


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

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


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

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


Reply | Threaded
Open this post in threaded view
|

Re: Script checking to see what database it's connected to

Thomas Kellerer-4
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/