Create FUNCTION in PostgreSQL from a Bash script

Question

I'm trying to create a FUNCTION in my Postgres database from a Bash script. Unfortunately, I cannot get it to work. This is my script:

#!/bin/bash
# Save Postgres command to $POSTGRES_CMD
read -d '' POSTGRES_CMD <<"EOF"
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ';';
    END LOOP;
END; 
$$
LANGUAGE plpgsql;
EOF

sudo su - postgres -c "psql -d postgres -U postgres -c \"${POSTGRES_CMD}\""

When I run the script, I get the following error:

ERROR:  Syntax error at »20541«
LINE 1: ...N truncate_tables(username IN VARCHAR) RETURNS void AS 20541

So it seems like something is wrong with the $$? How can I create a FUNCTION like in my script in Postgres from a Bash script? Do I have to mask anything?

Edit:

The final, working script (also added create language if it's not registered yet):

#!/bin/bash
sudo su - postgres -c "psql -d postgres -U postgres" << 'EOF'
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = 'username' AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ';';
    END LOOP;
END;
$$
LANGUAGE plpgsql;

your function doesn't take care of arguments passed as you hardcoded them. do you have a solution for the case when you can pass these args? — Ren
– Ren, Commented Jun 13, 2022 at 23:02

Xavier Combelle · Accepted Answer · 2011-02-22 15:56:17Z

9

The $$ is replaced by the process id you should escape the $$ thing like this \$\$ or even \\$\\$ as it is escaped two times

answered Feb 22, 2011 at 15:56

Xavier Combelle

11.3k5 gold badges30 silver badges53 bronze badges

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

2 Comments

Wolkenarchitekt Over a year ago

It actually works when escaping the dollar signs twice. Didn't knew about that. Thanks!

Xavier Combelle Over a year ago

The araqnid answer is far better than mine

araqnid · Accepted Answer · 2011-02-22 16:25:19Z

4

Use <<'EOF' to stop bash interpolating the here document.

addition:

You can avoid passing everything through "-c" by using:

sudo su - postgres -c "psql -d postgres -U postgres" <<'EOF'
...
EOF

as stdin should be preserved through sudo and su

answered Feb 22, 2011 at 16:25

araqnid

135k25 gold badges163 silver badges138 bronze badges

Comments

Ren · Accepted Answer · 2022-06-03 11:22:47Z

0

if anyone is looking for a version where env variables are passed (DB_USERNAME, DB_SCHEMA) here is:

psql -U dev -d clinical_trial -h db -v ON_ERROR_STOP=1 << EOF
CREATE OR REPLACE FUNCTION truncate_tables() RETURNS void AS \$\$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = '$DB_USERNAME' AND schemaname = '$DB_SCHEMA' AND tablename not like 'flyway%';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE $DB_SCHEMA.' || quote_ident(stmt.tablename) || ';';
    END LOOP;
END;
\$\$
LANGUAGE plpgsql;
EOF

answered Jun 3, 2022 at 11:22

Ren

1,0512 gold badges14 silver badges31 bronze badges

Collectives™ on Stack Overflow

Create FUNCTION in PostgreSQL from a Bash script

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related