conditionally terminate psql script

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

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

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

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);