时间戳

首页 > 解决方案 > 写入用于将数据插入空表但未插入数据的函数

postgresql - 写入用于将数据插入空表但未插入数据的函数

问题描述

我正在尝试使用该函数将数据添加到空表中。当尝试使用“Raise info”命令显示输出时，它工作正常。当我尝试使用“插入”语句进行相同操作时，它不起作用。没有数据插入到表中。我的代码如下所示。

CREATE OR REPLACE FUNCTION public.fn_upd_invoice5(personid integer)
      RETURNS integer
      LANGUAGE 'plpgsql'
      COST 100.0
      VOLATILE NOT LEAKPROOF 
AS $function$
DECLARE
Begin 
  /* none of the insert is working
  INSERT INTO public.invoice(
      pk_invoice_id, fk_person_id, invoice_no, invoice_date, gross_amount, 
  discount_amount, net_amount, invoice_desc, created_date_time, created_by,             
  modified_date_time, modified_by, active)
      select 999999776, 5,1, to_date('08/11/2018','dd/mm/yyyy'), 9500, 0, 
  7283, 'Rent for the month of november 
  2018',to_date('08/11/2018','dd/mm/yyyy') , 1, 
  to_date('08/11/2018','dd/mm/yyyy'), 1, 'Y';
  */    
  -- PREPARE invoiceprep1 (int) AS
      INSERT INTO tmp1 VALUES($1);
  -- EXECUTE invoiceprep1 (returnsiid);
  /*
  PREPARE invoiceprep1 (int, int, varchar, date, int, int, int, varchar, date, 
  int, date, int, char) AS
      INSERT INTO invoice VALUES($1, $2, $3, 
  $4,$5,$6,$7,$8,$9,$10,$11,$12,$13);
  EXECUTE invoiceprep1 
(returnsiid,returnspi,tmpinvoiceno,tmpstartdate,returnspr,returnsdis,returnspr,tempdesc,tmpstartdate,tmpuserId,tmpstartdate,tmpuserId,tmpActive);
  */
  /*
  INSERT INTO invoice(pk_invoice_id, fk_person_id, invoice_no, invoice_date, gross_amount, discount_amount, net_amount, invoice_desc, created_date_time, created_by, modified_date_time, modified_by, active) 
       VALUES(returnsiid,returnspi,returnsiid,tmpstartdate,returnspr,returnsdis,returnspr,tempdesc,tmpstartdate,1,tmpstartdate,1,'Y');
  -- commit;
  */
  --  update invoice set pk_invoice_id = 99999999;
  EXCEPTION WHEN OTHERS THEN 
          RAISE;
  return 1;
End;  
$function$;

标签： postgresql

解决方案

任何错误都会导致包括你的 s 在内的整个事务INSERT被回滚。这就是为什么你看不到效果。

RETURN错误消息表明函数末尾没有语句，但函数被声明为返回除void.

推荐阅读