How to write migration script for a function in postgresql using a module node-pg-migrate

Question

I am tring to write a migration script using node-pg-migrate module, but not able to succeed in it, as I am getting below error.

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

My migration script is as follows :

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
  })
}

& below is my actual postgresql function :

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$

Can anyone give me a proper example of function written in node-pg-migrate module. I am able to write create table & other scripts but it gives issue for adding migrations of functions. Thanks in advance.

score 0 · Accepted Answer · answered May 08 '19 at 07:36

Found the workaround to solve this issue. pgm also provides way by which we can directly add raw sql queries into migration file.

find below code, which will be as follows :

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)`)
}

How to write migration script for a function in postgresql using a module node-pg-migrate

1 Answers1