Thread: conditionally terminate psql script
Hi, is there a way to stop execution of a psql script if a select returns some rows (or no rows) The idea is to add a safety check on data, specifically to select all new rows that would conflict on a bulk insert, show them and stop Best regards Wolfgang
Hi
po 17. 12. 2018 v 13:14 odesílatel <hamann.w@t-online.de> napsal:
Hi,
is there a way to stop execution of a psql script if a select returns some rows (or no rows)
The idea is to add a safety check on data, specifically to select all new rows that would conflict
on a bulk insert, show them and stop
you need psql from PostgreSQL 10 and higher
there is a \if statement
Regards
Pavel
Best regards
Wolfgang
Hi
is there a way to stop execution of a psql script if a select returns some rows (or no rows) The idea is to add a safety check on data, specifically to select all new rows that would conflict on a bulk insert, show them and stop
Look at \if command in psql (since v10):select count(*) as total from pg_class where 1 = 1\gset
select :total = 0 as notfound\gset
\if :notfound
\echo Nothing found.
\q
\endif
\echo :total records found.
----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Hi, many thanks -- too bad I am still using 9.3 Best regards Wolfgang >> Hi >> >> po 17. 12. 2018 v 13:14 odesílatel <hamann.w@t-online.de> napsal: >> >> > >> > >> > Hi, >> > >> > is there a way to stop execution of a psql script if a select returns some >> > rows (or no rows) >> > The idea is to add a safety check on data, specifically to select all new >> > rows that would conflict >> > on a bulk insert, show them and stop >> > >> >> you need psql from PostgreSQL 10 and higher >> >> there is a \if statement >> >> Regards >> >> Pavel >> >> > >> > Best regards >> > Wolfgang >> > >> > >> > >>
On 17.12.2018 16:07, hamann.w@t-online.de wrote: > Hi, many thanks -- too bad I am still using 9.3 In this case you can try ON_ERROR_STOP psql variable. Something like this: \set ON_ERROR_STOP on do $$ declare total bigint; begin select count(*) into total from pg_class where 1=1; if total = 0 then raise exception 'Nothing found.'; end if; raise notice '% records found.', total; end; $$ language plpgsql; \echo Continue execution... ----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Hi, many thanks, I will give it a try tomorrow Best regards Wolfgang >> >> On 17.12.2018 16:07, hamann.w@t-online.de wrote: >> > Hi, many thanks -- too bad I am still using 9.3 >> >> In this case you can try ON_ERROR_STOP psql variable. >> Something like this: >> >> \set ON_ERROR_STOP on >> >> do $$ >> declare >> total bigint; >> begin >> select count(*) into total from pg_class where 1=1; >> if total = 0 then >> raise exception 'Nothing found.'; >> end if; >> >> raise notice '% records found.', total; >> end; >> $$ language plpgsql; >> >> \echo Continue execution... >> >> ----- >> Pavel Luzanov >> Postgres Professional: http://www.postgrespro.com >> The Russian Postgres Company >> >> >>
hamann.w@t-online.de writes: > Hi, > > many thanks -- too bad I am still using 9.3 Just because your server backend is 9.3 does not rule out using much newer clients, such as psql. HTH > > Best regards > Wolfgang > >>> Hi >>> >> po 17. 12. 2018 v 13:14 odesílatel <hamann.w@t-online.de> napsal: >>> >> > >>> > >>> > Hi, >>> > >>> > is there a way to stop execution of a psql script if a select returns some >>> > rows (or no rows) >>> > The idea is to add a safety check on data, specifically to select all new >>> > rows that would conflict >>> > on a bulk insert, show them and stop >>> > >>> >> you need psql from PostgreSQL 10 and higher >>> >> there is a \if statement >>> >> Regards >>> >> Pavel >>> >> > >>> > Best regards >>> > Wolfgang >>> > >>> > >>> > >>> > > > > > > -- Jerry Sievers e: jerry.sievers@comcast.net p: 312.241.7800
On 12/17/2018 09:01 AM, Jerry Sievers wrote: > hamann.w@t-online.de writes: > >> Hi, >> >> many thanks -- too bad I am still using 9.3 > Just because your server backend is 9.3 does not rule out using much > newer clients, such as psql. While technically true, and is useful, many production servers (especially ones that must be PCI compliant) heavily restrict who can remotely connect to the database, and so superusers are stuck with what's installed, since installing new stuff has enough paperwork and bureaucracy to make Kafka weep. (Of course, if you must be PCI compliant, you should get off 9.3 before the auditors drop the hammer on you.) -- Angular momentum makes the world go 'round.
On Mon, 17 Dec 2018 13:42:14 +0100
Pavel Stehule <pavel.stehule@gmail.com> wrote:
> you need psql from PostgreSQL 10 and higher
>
> there is a \if statement
Q: What is the "abort cycle command"?
e.g.,
select count(1) < 1 as "lacks_rows";
from foobar
where blah blah
\gset
\if :lacks_rows
\echo foobar lacks rows to process.
\echo goodnight :-)
????????? <--- what goes here to stop execution?
\endif
The point is that adding many levels of if-block logic is becomes
difficult to maintain. It would be nice to stop execution without
having to nest everything one level deeper.
--
Steven Lembark 3920 10th Ave South
Workhorse Computing Birmingham, AL 35222
lembark@wrkhors.com +1 888 359 3508
On Tue, Dec 18, 2018 at 2:44 PM Steven Lembark <lembark@wrkhors.com> wrote: > > there is a \if statement > > Q: What is the "abort cycle command"? [...] > \if :lacks_rows > > \echo foobar lacks rows to process. > \echo goodnight :-) > > ????????? <--- what goes here to stop execution? \quit David J.
On Mon, Dec 17, 2018 at 2:07 PM <hamann.w@t-online.de> wrote:
many thanks -- too bad I am still using 9.3
not sure if it has been mentioned in the thread so far. But according to the docs, 9.3 psql does support the \gset command. So you should be able to do something like this:
select case when exists (select 1 from pg_namespace where nspname='tf')
then '\echo schema tf exists \\ \q'
else '\echo schema does not exist -- creating ...'
end as cmd\gset
:cmd
create schema tf;
create table tf.i(i int);