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;