如何使用模块节点 pg-migrate 为 postgresql 中的函数编写迁移脚本

Question

我试图使用 node-pg-migrate 模块编写一个迁移脚本，但无法成功，因为我得到以下错误。

Error: Can't get migration files: //dbMigration/migrations/1534615332847_new_test_function.js:11
},'DECLARE
^^^^^^^^^
SyntaxError: Invalid or unexpected token

我的迁移脚本如下：

exports.shorthands = undefined
exports.up = (pgm) => {
pgm.createFunction('mew_test_function', [
{ mode: 'IN', name: 'soldtocode', type: 'text', default: null }
],
{
return: 'json',
language: 'plpgsql',
replace: true
},'DECLARE 
customer_list json;
final_response json;
begin
if not exists(select 1 from company_info where supplier_location_id = soldtocode) then
final_response:=json_build_object("status","fail","message","No Record Found","customer_list", customer_list);
else 
SELECT array_to_json(array_agg(row_to_json(t))) FROM 
(select distinct ci."b2x_registration_id",ci.supplier_location_id,ci."name",mo.id,mo.name "customerName"
from public.company_info ci 
join public.company_oem_mapping com on ci.id = com.company_info_id
join mst_oem mo on mo.id = com.oem_id
and ci.supplier_location_id = soldtocode) t 
INTO customer_list;
final_response:=json_build_object("status","pass","message","Record Found Successfully","customer_list", customer_list);
end if;
RETURN final_response;
end 
')
} 
exports.down = (pgm) => {
pgm.dropFunction('mew_test_function', [
{ mode: 'IN', name: 'soldtocode', type: 'text', default: null }
],{
ifExists : true,
cascade : false
})
}

下面是我的实际PostgreSQL函数：

CREATE OR REPLACE FUNCTION public.get_customer_name(soldtocode text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE 
customer_list json;
final_response json;
begin
if not exists(select 1 from company_info where supplier_location_id = soldtocode) then
final_response:=json_build_object("status","fail","message","No Record Found","customer_list", customer_list);
else 
SELECT array_to_json(array_agg(row_to_json(t))) FROM 
(select distinct ci."b2x_registration_id",ci.supplier_location_id,ci."name",mo.id,mo.name "customerName"
from public.company_info ci 
join public.company_oem_mapping com on ci.id = com.company_info_id
join mst_oem mo on mo.id = com.oem_id
and ci.supplier_location_id = soldtocode) t 
INTO customer_list;
final_response:=json_build_object("status","pass","message","Record Found Successfully","customer_list", customer_list);
end if;
RETURN final_response;
end 
$function$

谁能给我一个用node-pg-migrate 模块编写的函数的适当示例。我能够编写创建表和其他脚本，但它为添加函数迁移提供了问题。提前谢谢。

Answer 1

找到了解决此问题的方法。 PGM 还提供了可以直接将原始 SQL 查询添加到迁移文件中的方法。

找到下面的代码，如下所示：

exports.shorthands = undefined
exports.up = (pgm) => {
pgm.sql(`CREATE OR REPLACE FUNCTION public.get_customer_name(soldtocode text)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE 
customer_list json;
final_response json;
begin
if not exists(select 1 from company_info where supplier_location_id = soldtocode) then
final_response:=json_build_object("status","fail","message","No Record Found","customer_list", customer_list);
else 
SELECT array_to_json(array_agg(row_to_json(t))) FROM 
(select distinct ci."b2x_registration_id",ci.supplier_location_id,ci."name",mo.id,mo.name "customerName"
from public.company_info ci 
join public.company_oem_mapping com on ci.id = com.company_info_id
join mst_oem mo on mo.id = com.oem_id
and ci.supplier_location_id = soldtocode) t 
INTO customer_list;
final_response:=json_build_object("status","pass","message","Record Found Successfully","customer_list", customer_list);
end if;
RETURN final_response;
end 
$function$`)
}
exports.down = (pgm) => {
pgm.sql(`DROP FUNCTION IF EXISTS public.get_customer_name(soldtocode text)`)
}