在 PL/pgSQL 匿名块中引用 psql 参数 [英] Reference psql parameter inside PL/pgSQL anonymous block

问题描述

我想通过 psql 命令行将参数传递给匿名 PL/pgSQL 块，然后在条件中检查该参数.

I'd like to pass a parameter to an anonymous PL/pgSQL block via psql command line, then check that parameter in a conditional.

SQL 的相关部分在这里:

The relevant part of the SQL is here:

do $$
begin
    if (':para' = 1) then
        -- statements;
    end if;
end $$
;

我这样称呼这个脚本:

psql -d dbname -v para=1 < script.sql

我收到错误:

ERROR:  invalid input syntax for integer: ":para"
LINE 1: SELECT (':para' = 1)
            ^
QUERY:  SELECT (':para' = 1)
CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF

我尝试使用 case/when 范式，但也没有用.

I tried using the case/when paradigm, which did not work either.

我猜测 psql 参数与 PL/pgSQL 不兼容?最终，如果我将 1 作为 psql 参数传递，我的目标是运行单个 delete 语句，如果传递 则不运行它>0.

I am guessing that a psql parameter is not compatible with PL/pgSQL? Ultimately, my goal is to run a single delete statement if I pass 1 as a psql parameter, and not run it if I pass 0.

推荐答案

psql 解析器看不到字符串中的内容.这可能就是您想要的:

The psql parser can't see what is inside strings. This might be what you want:

delete from t
where :para = 1

在匿名块之外进行.如果你真的需要 PL/pgSQL 使用参数化函数:

Do it outside of an anonymous block. If you really need PL/pgSQL use a parameterized function:

create or replace function f(_para integer)
returns void as $$
begin
    if _para = 1 then
        --statements
    end if;
end; $$ language plpgsql;

您的脚本文件将包含:

select f(:para);

如果您不想将函数永久添加到数据库中，请在脚本中完成所有操作:

If you do not want to permanently add a function to the db do it all inside the script:

drop function if exists f_iu7YttW(integer);

create or replace function f_iu7YttW(_para integer)
returns void as $$
begin
    if _para = 1 then
        --statements
    end if;
end; $$ language plpgsql;

select f_iu7YttW(:para);

drop function f_iu7YttW(integer);

给函数一个唯一的名字，这样你就不会冒丢失其他东西的风险.

Give the function an unique name so you do not run the risk of dropping something else.

这篇关于在 PL/pgSQL 匿名块中引用 psql 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！