PostgreSQL - make .sql file quit if condition is true, do not run lines under (while processed with bash)

Question

I am processing .sql files with bash automatically in my CI solution in a for loop, to catch faulty migrations before deployment.

MIGS is a bash array containing in time order the .sql in a folder

for sqlfile in ${MIGS[@]};
do
    psql myproject_unit < $sqlfile
done

Is there a way to terminate processing of the .sql if a condition is met?

So what I do now:

file sql20180515n.sql

CREATE TABLE IF NOT EXISTS something (
...
/* imagine here other 400 lines of SQL */

What I am looking for (please replace the first two lines to the correct syntax if applicable or tell me if it is not possible)

IF TABLE_EXISTS(something) /* we already ran, no need */
EXIT;

CREATE TABLE something (
...
/* imagine here other 400 lines of SQL */

So to confirm: if condition is met (table/col/trigger/index already exists), I would like to jump to the next .sql source in my for loop, so do not quit the whole shell, just the psql processor as defined in the .sql file when to terminate it.

if you need calculations and logic in sql script - use DO statement with plpgsql language, not sql... — Vao Tsun
– Vao Tsun, Commented May 15, 2018 at 13:49
yes - but you'll probably need dollar quoting, so don't forget to escape em — Vao Tsun
– Vao Tsun, Commented May 15, 2018 at 15:06

Vao Tsun · Accepted Answer · 2018-05-15 15:12:51Z

2

you would most probably need plpgsql logic and thus DO statement, below is example:

MacBook-Air:Downloads vao$ psql so << EOF
>  do \$\$
>  begin
>   if (select count(*) from pg_class where oid = 'public.t'::regclass and relkind = 'r') > 0 then
>  insert into t values(1);
>  update t set i = 9 where i = 1;
>  end if;
>  end;
>  \$\$;
> select * from t;
> EOF
Timing is on.
Pager usage is off.
DO
Time: 8.952 ms
 i
---
 9
 9
 9
(3 rows)

answered May 15, 2018 at 15:12

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dszakal · Accepted Answer · 2018-05-15 15:35:23Z

PlPgsql sorts it, we use the following code to use as sample to start from.

-- USE THIS AS EXAMPLE CODE FOR CREATING MORE COMPLEX TABLE STRUSCTURES NEVER TO BE DEFINED TWICE

CREATE OR REPLACE FUNCTION install_these_tables()
RETURNS INT AS $$

DECLARE

  does_exist int;

BEGIN

SELECT COUNT(*) as cnt INTO does_exist from pg_tables where tablename = 'testtable'; -- REPLACE TABLE NAME

  IF does_exist = 1 THEN
    RAISE NOTICE 'Existed';
    RETURN 1;
  ELSE
    -- REPLACE START
    CREATE TABLE testtable (
      id_testtable bigserial PRIMARY KEY,
      status text DEFAULT NULL
    );
    -- REPLACE END
    RAISE NOTICE 'Not existed, created';
    RETURN 0;
  END IF;

END; $$ LANGUAGE plpgsql;

SELECT install_these_tables();
DROP FUNCTION install_these_tables();

Also PostgreSQL 10 supports \if, but we use 9.6 and also did not find easy to alter example codes for that

Laurenz Albe · Accepted Answer · 2018-05-15 17:00:27Z

1

The other answers are good, but maybe you'd prefer to keep your logic in shell.

You can query for the condition in a separate psql call.

In the following example I test for the existence of table something:

psql -d mydb -c "SELECT 'something'::regclass" >/dev/null 2>&1
# run the script only if the above errors out
if [ $? -ne 0 ]; then
    # create "something" since it doesn't exist
    psql -d mydb -1 -f create_something.sql
    if [ $? -ne 0 ]; then
        # exit with error if script fails
        exit 1
    fi
fi

answered May 15, 2018 at 17:00

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - make .sql file quit if condition is true, do not run lines under (while processed with bash)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related