我有一个Postgres函数:
CREATE OR REPLACE FUNCTION add_new_views(VARIADIC all_view event_view[])
RETURNS void AS
$BODY$
DECLARE
view event_view;
BEGIN
FOREACH view IN ARRAY all_view LOOP
INSERT INTO t_event_views VALUES (view.event_id, view.device_code);
END LOOP;
END;
$BODY$
event_view - 这是我的复合类型:
CREATE TYPE event_view (event_id uuid, device_code text);
我可以通过pgAdmin这样使用我的函数:
我可以通过pgAdmin这样使用我的函数:
SELECT add_new_views(VARIADIC ARRAY [
('b9d78fc3-b55a-452e-b935-8ce4f1e79284', 'asd')::event_view,
('b9d78fc3-b55a-452e-b935-8ce4f1e79284', 'asd')::event_view
]);
但我需要从Node.js中调用它。为了连接PostgreSQL,我使用“ pg”模块的node-postgres 。
我发送以下请求:
query = 'SELECT add_new_views(VARIADIC [$1])';
current_client.query(query, allViews, function(err ,res) {
if(err) console.log(err);
console.log(res)
})
allViews - 这个数组看起来像这样:
[ [ '(b9d78fc3-b55a-452e-b935-8ce4f1e79284)', '(CHUPERM)' ],
[ '(b9d78fc3-b55a-452e-b935-8ce4f1e79284)', '(HUE)' ],
[ '(b9d78fc3-b55a-452e-b935-8ce4f1e79284)', '(HU)' ],
[ '(00ae9a6c-781f-48d1-ab42-bfa87d09bdbb)', '(HITE)' ],
[ '(00ae9a6c-781f-48d1-ab42-bfa87d09bdbb)', '(HIJFIRM)' ],
[ '(00ae9a6c-781f-48d1-ab42-bfa87d09bdbb)', '(DVERMNYEZAPILI)' ],
[ '(00ae9a6c-781f-48d1-ab42-bfa87d09bdbb)', '(TICHTOMENYANEVIDISH)' ],
[ '(00ae9a6c-781f-48d1-ab42-bfa87d09bdbb)', '(HITEBEAD)' ] ]
作为回应,我收到了:
{ [error: malformed record literal: "(b9d78fc3-b55a-452e-b935-8ce4f1e79284)"]
name: 'error',
length: 133,
severity: 'ERROR',
code: '22P02',
detail: 'Too few columns.',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'rowtypes.c',
line: '183',
routine: 'record_in' }
如果从allView数组中删除括号,我会得到“等待括号”的错误。当我尝试传递到postgres简单的一维数组时,一切都很好(当然,在postgre函数中需要改变所有必要的内容)。但是对于这个复合类型数组,我无能为力。请帮忙解决。对我的英语很抱歉。