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

Socowi · Accepted Answer · 2021-07-12 10:08:28Z

2

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

answered Jul 12, 2021 at 10:08

Socowi

27.8k4 gold badges41 silver badges72 bronze badges

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

5 Comments

Camel4488 Over a year ago

How to thank you? I went to look in all directions except that of the 'EOF'. Indeed, everything works, a big thank you !!In terms of performance, (because I have around 3,000,000 lines to record), is it good to leave the loop in the bash script or is it more efficient to put it in a function?

Socowi Over a year ago

@Camel4488 Glad I could help. About the loop: I don't know much about postresql, so I cannot help you with that. Best to just try both versions I guess. Even though I don't think packing part of a program inside a function will improve performance in any language. After all, you still execute the same instructions.

Camel4488 Over a year ago

On pgadmin the script is executed in 10 minutes, the with psql and the bash script already 20 minutes and it has not yet finished.

Camel4488 Over a year ago

Soccowi I have no more problems since your solution. Thanks again. However, I am looking for how to make echo log files and raise notices. Can you help me?

Socowi Over a year ago

Sounds like you want to create the logs from inside the sql command. Sorry, I cannot help you with that. Either way, just open a new question.

Collectives™ on Stack Overflow

Postgres script in a bash script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related