Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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;
share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks - looks even nicer! –  ifischer Feb 22 '11 at 17:56

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

share|improve this answer
    
It actually works when escaping the dollar signs twice. Didn't knew about that. Thanks! –  ifischer Feb 22 '11 at 17:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.