Postgres script in a bash script

Question

I created a script in pgadmin4. The script consists of querying a table.

In this table I have an element which contains value1 | value2 | valueX (the number of elements may vary from row to row).

In pgadmin4 I used this script:

#!/bin/bash

psql "postgresql://id:[email protected]/table" << EOF
do $$
DECLARE
        _id int;
        _name text;
        _value text;
begin
FOR _id, _name IN select id, unnest(string_to_array(themes, '|')) from data LOOP
if EXISTS (select id_theme from theme where uri_theme = concat('<',_name,'>')) then
 insert into data_themes(data_id, theme_id) values (_id, (select id_theme from theme where uri_theme = concat('<',_name,'>')) );
RAISE NOTICE 'test % / %', _id, _name;
end if;
end loop;
end;
$$;
EOF

the script works as I want it to, in pgadmin4.

However, when I want to run this script in a bash script it gives me an error

WARNING: there is no transaction in progress COMMIT

It stops in the middle of the loop (around 25,000 lines) and shows me the error.

I put this:

\echo :AUTOCOMMIT
\set AUTOCOMMIT off
\echo :AUTOCOMMIT

I don't understand why the script works on pgadmin and doesn't work in a bash script. Thanks for your help

Answer 1

I don't understand why the script works on pgadmin and doesn't work in a bash script.

Because you effectively run a different script. $$ is a variable in bash. Inside a here-document (<< EOF ... EOF) variables are expanded. Therefore, you run something like

do 1234
....
1234;

To fix this, quote the here document:

psql "postgresql://id:[email protected]/table" << 'EOF'
do $$
...
$$;
EOF