从 Bash 脚本在 PostgreSQL 中创建 FUNCTION

发布于 2024-10-18 17:37:19 字数 1425 浏览 1 评论 0原文

我正在尝试从 Bash 脚本在 Postgres 数据库中创建一个函数。不幸的是，我无法让它工作。这是我的脚本：

#!/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}\""

当我运行脚本时，出现以下错误：

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

所以 $$ 似乎有问题？如何从 Bash 脚本创建像 Postgres 中的脚本一样的函数？我需要掩盖什么吗？

编辑：

最终的工作脚本（如果尚未注册，还添加了创建语言）：

#!/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;

原文

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柠檬色的秋千 2024-10-25 17:37:19

$$ 被进程 ID 替换，你应该转义 $$ ，像这样 \$\$ 甚至 \\$\\$ 因为它转义了两次

回复收藏 0 原文

祁梦 2024-10-25 17:37:19

使用 <<'EOF' 停止 bash 插入此处文档。

补充：

您可以使用以下方式避免通过“-c”传递所有内容：

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

因为标准输入应通过 sudo 和 su 保留

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

回复收藏 0 原文

仅一夜美梦 2024-10-25 17:37:19

如果有人正在寻找传递环境变量的版本（DB_USERNAME，DB_SCHEMA），这里是：

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

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

回复收藏 0 原文

~没有更多了~