postgresql 如何使用 plpgsql 函数插入数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10421051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert data using a plpgsql function?
提问by Mats
I'm trying to insert data into a table using a plpgsql function or stored procedure. However, I want to insert multiple records at once. I now these will all be VARCHAR so I thought I could use a function formed like function(tablename VARCHAR, records VARCHAR[][]). But then I found out multidimensional array support in plpgsql isn.t quite so fantastic.
我正在尝试使用 plpgsql 函数或存储过程将数据插入表中。但是，我想一次插入多条记录。我现在这些都将是 VARCHAR，所以我想我可以使用像function(tablename VARCHAR, records VARCHAR[][]). 但后来我发现 plpgsql 中的多维数组支持并不是那么棒。
This is what my function looks like at the moment. This doesn't produce the result I'm looking for. When I ask
这就是我的功能目前的样子。这不会产生我正在寻找的结果。当我问
SELECT insert_data('tennis', ARRAY[ARRAY['1','2'], ARRAY['3','4']])
I get the following error
我收到以下错误
ERROR: syntax error at or near "{"
LINE 1: INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
^
QUERY: INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
CONTEXT: PL/pgSQL function "insert_data" line 26 at EXECUTE statement
However I am expecting a query like
但是我期待像这样的查询
INSERT INTO tennis VALUES (null, '1', '2'), (null, '3', '4');
which would work because table tennis has this structure.
这是可行的，因为乒乓球有这种结构。
CREATE OR REPLACE FUNCTION insert_data (dsetname_in VARCHAR, records VARCHAR[][])
RETURNS BOOLEAN AS $PROC$
DECLARE
insertquery TEXT;
val VARCHAR;
i INT;
j INT;
BEGIN
insertquery = $$INSERT INTO $$ || dsetname_in || $$ VALUES $$;
FOR i IN array_lower(records, 1)..array_upper(records, 1)
LOOP
insertquery = insertquery || $$(null, $$;
FOR j IN array_lower(records[i:i], 1)..array_upper(records[i:i], 1)
LOOP
val = records[i:i][j:j];
insertquery = insertquery || val;
IF j <> array_upper(records[i:i], 1) THEN
insertquery = insertquery || $$, $$;
END IF;
END LOOP;
insertquery = insertquery || $$)$$;
IF i <> array_upper(records, 1) THEN
insertquery = insertquery || $$, $$;
END IF;
END LOOP;
insertquery = insertquery || $$;$$;
EXECUTE insertquery;
RETURN TRUE;
END;$PROC$ LANGUAGE 'plpgsql';
采纳答案by kgrittn
I'm dubious about the value of this whole approach because I don't see that it adds any useful level of abstraction; but if you must do it, and all your values are character strings, I think the cleanest approach is this:
我怀疑整个方法的价值，因为我没有看到它增加了任何有用的抽象级别；但是如果你必须这样做，并且你所有的值都是字符串，我认为最干净的方法是这样的：
CREATE OR REPLACE FUNCTION insert_data(dsetname_in text, records text[])
RETURNS VOID LANGUAGE plpgsql AS $PROC$
DECLARE
insertquery text;
i int;
BEGIN
insertquery := 'INSERT INTO ' || dsetname_in || ' VALUES ';
FOR i IN array_lower(records, 1)..array_upper(records, 1)
LOOP
insertquery := insertquery || '(null, ' || records[i] || '),';
END LOOP;
insertquery := left(insertquery, char_length(insertquery) - 1);
EXECUTE insertquery;
END;
$PROC$;
You can then call it like this, which seems cleaner than what you showed for the nested arrays:
然后你可以这样调用它，这看起来比你为嵌套数组显示的更清晰：
SELECT insert_data('tennis',
ARRAY[$$'1','2'$$,
$$'3','4'$$]);
