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

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

  •  1
  • 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 年前
        1
  •  0
  •   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?

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

        2
  •  0
  •   Laurenz Albe    6 年前

    The documentation 说:

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

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

    我想不出办法 psql 变量。

    推荐文章