How to change script variable of postgis script in batch

Question

I have a script like this:

DO $$
DECLARE someName TEXT;
BEGIN
  someName := 'something';
  DELETE FROM schema.tablename1 WHERE someColumn= someName ;
  DELETE FROM schema.tablename2  WHERE someColumn= someName ;
  DELETE FROM schema.tablename3  WHERE someColumn= someName ;
END $$;

Its saved as something.sql.

Now I want to call this script in a batch with this code: [POSTGISPATH]\psql.exe -h [HOSENAME] -p [PORT] -U [USERNAME] -d [DB] -f [SCRIPTPATH]\something.sql

And that works perfectly. But now I'd like this someName := 'something' to be something that I ca change in a batchfile.

SET SOMETHING= 'something'

[POSTGISPATH]\psql.exe -h [HOSENAME] -p [PORT] -U [USERNAME]  -d [DB] -f  [SCRIPTPATH]\something.sql

And I'd like to change my SQL-Script to something like this:

DO $$
DECLARE someName TEXT;
BEGIN
  someName := %SOMETHING%;
  DELETE FROM schema.tablename1 WHERE someColumn= someName ;
  DELETE FROM schema.tablename2  WHERE someColumn= someName ;
  DELETE FROM schema.tablename3  WHERE someColumn= someName ;
END $$;

Any idea how to do that?

Does this answer your question? Pass command line args to sql (Postgres) — JGH
– JGH, Commented Nov 20, 2020 at 14:54

Jim Jones · Accepted Answer · 2020-11-20 13:17:34Z

0

Have you considered providing the column value as parameter in your batch file? For instance:

Data Sample

CREATE TABLE t (f text);
INSERT INTO t VALUES ('foo'),('bar');
CREATE TABLE t2 (f text);
INSERT INTO t2 VALUES ('foo'),('bar');

Batch file

#!/bin/bash

psql -d db -h localhost -c "
DELETE FROM t WHERE f= '$1' ;
DELETE FROM t2 WHERE f= '$1' ;
"

Executing the file and deleting all foo records from the tables

$ ./test.sh foo

And the records are gone:

$ psql -d db -h localhost -c "SELECT * FROM t,t2"
  f  |  f  
-----+-----
 bar | bar

answered Nov 20, 2020 at 13:17

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

How to change script variable of postgis script in batch

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related