代码之家 › 专栏 › 技术社区 › OwenP

如何在Postgres脚本中使用变量?

variable-expansion psql postgresql

OwenP · 技术社区 · 6 年前

我正在开发一个使用Postgres作为后端的原型。我不会做很多SQL,所以我正在摸索着通过它。我做了一个 .pgsql 我运行的文件 psql 它执行建立我的数据库的许多文件中的每一个,我使用一个变量来定义将要使用的模式,这样我就可以在不破坏“好”实例的情况下测试特性:

\set schema_name 'example_schema'

\echo 'The Schema name is' :schema_name

\ir sql/file1.pgsql
\ir sql/file2.pgsql

这项工作进展顺利。我定义了几个扩展函数 :schema_name 适当地:

CREATE OR REPLACE FUNCTION :schema_name.get_things_by_category(...

由于我无法理解的原因,这在我的最新功能中不起作用:

CREATE OR REPLACE FUNCTION :schema_name.update_thing_details(_id uuid, _details text)
    RETURNS text
    LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
    UPDATE :schema_name.things
    ...

语法错误表明它正在解释 图式名称 字面上 UPDATE 而不是扩大它。如何让它在这里使用变量值而不是文字值?我知道也许在 BEGIN..END 是一个不同的上下文,但肯定有一种方法可以在所有地方编写这个模式名的脚本?

2 回复 | 直到 6 年前

Brandon 6 年前

我可以想到三种方法,因为 psql 不能直接这样做。

外壳脚本

使用bash脚本执行变量替换,并将结果传输到 PSQL ,就像。

#!/bin/bash

$schemaName = $1
$contents = `cat script.sql | sed -e 's/@SCHEMA_NAME@/$schemaName'`

echo $contents | psql

如果您有很多.sql脚本,这可能会是一个很大的问题。

分期模式

使用硬编码的模式保持您现在使用的方法,例如 staging 然后用bash脚本进行重命名 分期 无论您希望实际模式是什么。

自定义搜索路径

您的入口点可以是bash中的一个内联脚本,通过管道连接到psql中,对默认连接架构进行预先更新,然后使用\ir将您的 .sql 文件,应该不指定架构。

#!/bin/bash

$schemaName = $1

psql <<SCRIPT
SET search_path TO $schemaName;
\ir sql/file1.pgsql
\ir sql/file2.pgsql
SCRIPT

一些细节: How to select a schema in postgres when using psql?

就我个人而言,我倾向于后一种方法,因为它似乎是最简单和最可扩展的方法。

Laurenz Albe 6 年前

The documentation 说:

变量插值不会在引用的SQL文本和标识符中执行。因此,如 ':foo' 无法从变量的值生成带引号的文本(如果它确实有效,则不安全,因为它无法正确处理嵌入在值中的引号)。

现在函数体是一个“dollar-quoted%rdquo;字符串”文本( $BODY$...$BODY$ ,因此变量不会在那里被替换。

我想不出办法 psql 变量。