Postgresql 字符串的 INSERT EXECUTE FORMAT

Question

我希望创建一个函数来更新表格。

我正在尝试运行该函数：

SELECT insert_function('asda', 1 ,1 , 'asd', 1)

但是我遇到了错误:

第3列VALUES("asda","1","1","asd","1")不存在。

当我尝试运行以下代码时：

SELECT insert_function('1', 1 ,1 , '1', 1)在"""""""附近有零长度的分隔符标识符

第3列VALUES(""1"","1","1",""1"","1") (^在第一项处)

CREATE TABLE IF NOT EXISTS commits (
    id SERIAL PRIMARY KEY, 
    goo CHAR(64) NOT NULL, 
    foo INTEGER NOT NULL, 
    bla INTEGER NOT NULL, 
    ma CHAR(512) NOT NULL, 
    fgt INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION insert_function(goo char(64), foo INTEGER, bla INTEGER, ma CHAR(512), fgt INTEGER)
  RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
   INSERT INTO commits 
   VALUES(%I,  %I,  %I,  %I, %I)', 
   goo , foo , bla , ma , fgt );

END
$func$ LANGUAGE plpgsql;

请问我该如何插入数值？如何使用EXECUTE format编写函数？

- user565447

Answer 1

在使用using时指定列名：

CREATE OR REPLACE FUNCTION insert_function(goo char(64), foo INTEGER,
                                           bla INTEGER, ma CHAR(512), fgt INTEGER)
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('INSERT INTO commits(goo, foo, bla, ma, fgt)
                VALUES($1,$2,$3,$4,$5);') using goo, foo, bla, ma, fgt;

END
$func$ LANGUAGE plpgsql;

SqlFiddle演示

- Lukasz Szozda

Answer 2

您有两个问题：

当您想要将数据 插入 到表中时，您必须指定所有列（即使是自动计算的列），或者如果您不想指定所有列名，则必须指定所有列值，包括自动计算的值。

关于您动态查询的格式，您应该使用 %s 而不是 %I 来将相应的参数作为字符串进行插值，%I 将其参数转义为 SQL 标识符，这是您不需要的。

因此，您可以通过替换以下内容来解决问题：

EXECUTE format('
   INSERT INTO commits 
   VALUES(%I,  %I,  %I,  %I, %I)', 
   goo , foo , bla , ma , fgt );

通过

EXECUTE format('
   INSERT INTO commits 
   VALUES(DEFAULT, %L ,  %s ,  %s ,  %L , %s )', 
   goo , foo , bla , ma , fgt );

- Houari

---

谢谢解释！现在我明白了。 - user565447

Answer 3

%I（大写字母i）格式化符号会将参数转义为SQL标识符(参考文档)，这并不是你想要的结果。在这种情况下应该使用%s。

EXECUTE format('INSERT INTO commits VALUES(%L,  %s,  %s,  %L, %s)', goo , foo , bla , ma , fgt );

- Roberto Damian Alfonso

---

感谢您的解释！ - user565447