尝试在 postgreSQL-9.6 中更改类型时出现 $1 或附近的语法错误 [英] Syntax error at or near $1 while trying to alter type in postgreSQL-9.6

问题描述

我正在尝试向 postgreSQL-9.6 中的枚举类型添加一个值，但无法弄清楚我做错了什么.

I'm trying to add a value to an enumerated type in postgreSQL-9.6, and am having trouble figuring out what I'm doing wrong.

var tc = new NpgsqlCommand(@"ALTER TYPE attributeName ADD VALUE IF NOT EXISTS 
:a", conn);

//tc.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Text));
//tc.Parameters[0].Value = "test";
tc.Parameters.AddWithValue("a", NpgsqlDbType.Text, "test");

tc.ExecuteNonQuery();

我尝试了注释掉的代码和当前版本，都导致异常.异常详情是:

I tried both the commented out code and the current version, and both resulted in an exception. The exception details are:

$exception  {"42601: syntax error at or near \"$1\""}

我知道 $1 是指通过a"参数传递的文本，但我不明白为什么会出现问题或如何解决它.PostgreSQL 的文档说不能在事务块中执行 ALTER TYPE，但据我所知，Npgsql 不会自动启动事务，所以这应该不是问题.如果我使用类似的语法执行不同的 SQL 命令，例如:

I understand the $1 is in reference to the text being passed through the "a" parameter, but I don't understand why there is a problem or how to fix it. PostgreSQL's documentation says that ALTER TYPE cannot be performed in a transaction block, but as far as I can tell Npgsql doesn't automatically start transactions, so that shouldn't be a problem. If I perform a different SQL command with similar syntax, such as:

var tc = new NpgsqlCommand(@"INSERT INTO test VALUES (:a)", conn);

该程序运行完美.另外，如果我直接在 psql shell 中键入命令，如下所示:

the program works flawlessly. Also, if I type the command directly into the psql shell, like this:

ALTER TYPE attributeName ADD VALUE IF NOT EXISTS 'test';

它按预期工作.有人能帮我理解我做错了什么吗?谢谢.

it works as expected. Can anybody help me understand what I'm doing wrong? Thanks.

推荐答案

我可能错了，但我认为它无法实现，原因与表名不能作为查询参数传递一样.

I might be wrong, but I think it can't be achieved for the same reason a table name cannot be passed as a query parameter.

但是，您可以对其使用字符串替换:

However, you can use a string replacement for it:

string name = "test";
var tc = new NpgsqlCommand($"ALTER TYPE attributeName ADD VALUE IF NOT EXISTS '{name}'", conn);

请记住，这不是一种安全的方法！

Please keep in mind it's not a secure approach!

更新:

另一种解决方案是使用执行命令的存储过程.但是，您不能简单地在过程/函数内调用 ALTER TYPE ... ADD VALUE ... 命令，因为它在事务块内不起作用.您将收到以下错误:

Another solution would be usage of a stored procedure that executes the command. However, you cannot simply call the ALTER TYPE ... ADD VALUE ... command inside a procedure/function because it doesn't work inside transaction blocks. You would get the following error:

"ALTER TYPE ... ADD 不能从函数或多命令字符串"

"ALTER TYPE ... ADD cannot be executed from a function or multi-command string"

这个帖子应该能说明这个问题:9.1 中的类型操作

This thread should shed some light on the issue: Problems with ENUM type manipulation in 9.1

但是，您的问题可能有解决方案.看起来您正在尝试修改现有的枚举类型(添加新值).您可以创建一个直接对 pg_enum 表进行操作的函数.用于枚举类型操作的数据库函数集可以在这里找到:PostgreSQL 8.3+, 9.1+ ALTERENUM 仿真:元素添加/删除、事务.

However, there might be a solution for your problem. It looks like you're trying to modify the existing enum type (add a new value). You can create a function that operates on the pg_enum table directly. The set of database functions, designed for enum types manipulation, can found here: PostgreSQL 8.3+, 9.1+ ALTER ENUM emulation: element addition/removal, transactions.

向现有枚举添加新值的函数如下所示:

The function that adds a new value to existing enum looks as follows:

-- Also works within transactions in PostgreSQL 9.1+ (but you need
-- to reconnect to the database after transaction commit, because 
-- new enum items are not be visible within the session you used
-- to add them).
--
-- See http://en.dklab.ru/lib/dklab_postgresql_enum/
--
-- (C) Dmitry Koterov, 2013
-- This code is BSD licensed.
--

CREATE SCHEMA enum AUTHORIZATION postgres;


SET search_path = enum, pg_catalog;
SET check_function_bodies = false;
CREATE OR REPLACE FUNCTION enum.enum_add (
  enum_name varchar,
  enum_elem varchar
)
RETURNS void AS
$body$
DECLARE
    eoid OID;
    has_sortorder BOOLEAN;
BEGIN
    eoid := (
        SELECT pg_type.oid
        FROM pg_type JOIN pg_namespace ON pg_namespace.oid=pg_type.typnamespace
        WHERE typtype='e' AND enum_name IN(typname, nspname||'.'||typname)
    );
    has_sortorder := EXISTS(
        select 1
        from pg_attribute
        where attrelid=(select oid from pg_class where relname='pg_enum') and attname='enumsortorder'
    );
    IF has_sortorder THEN
        EXECUTE '
            INSERT INTO pg_enum(enumtypid, enumlabel, enumsortorder) VALUES(
                '||eoid||',
                '||quote_literal(enum_elem)||',
                (SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid='||eoid||')
            )
        ';
    ELSE
        EXECUTE E'INSERT INTO pg_enum(enumtypid, enumlabel) VALUES('||eoid||', '||quote_literal(enum_elem)||')';
    END IF;
END;
$body$
    LANGUAGE 'plpgsql';

COMMENT ON FUNCTION enum.enum_add (enum_name character varying, enum_elem character varying) IS 'Inserts a new ENUM element wthout re-creating the whole type.';

现在，您可以从 C# 代码中调用存储过程/函数:

Now, you can just call the stored procedure/function from your C# code:

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = @"enum.enum_add";
    cmd.Parameters.AddWithValue("enum_name", "attributeName");
    cmd.Parameters.AddWithValue("enum_elem", "O'Reilly");
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.ExecuteNonQuery();
}

请注意，如果您尝试添加现有值，上述函数将失败.

Please note, that the above function will fail if you try to add an existing value.

这篇关于尝试在 postgreSQL-9.6 中更改类型时出现 $1 或附近的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！