将 refcursor 提取到临时表中

问题描述

我需要将 refcursor 提取到临时表中.每个 refcursor 列应该匹配适当的表列 + 一个键(枚举)列应该在临时表中.例如 refcursor 返回以下数据:

I need fetch refcursor into temporary table. Each refcursor column should match appropriate table column + one key (enumerate) column should be in temp table. For example refcursor return below data:

'one' 'Monday'
'two' 'Friday'

以及应该存储在表中的数据:

And the data which should store in table:

 1 'one' 'Monday'
 2 'two' 'Friday'

这个refcursor是在其他函数中打开的.所以我不知道结果集中应该有哪些列.我如何实现诸如 FETCH ALL curs INTO temp_table 之类的东西?

This refcursor is opened in other functions. So I does not know what columns should be in result set.How I can implement something like FETCH ALL curs INTO temp_table ?

我写了下面的函数，但它抛出了 (V_CURS_Rec) 的错误.*

I wrote below function but it throws the error for (V_CURS_Rec).*

CREATE OR REPLACE FUNCTION FN_TEST()
RETURNS VOID LANGUAGE plpgsql
   AS $$
   DECLARE
   V_CURS REFCURSOR;
   V_CURS_Rec RECORD;
   ITER INTEGER;
BEGIN
   create temporary table if not exists TMP_TBL
   (
  INDX INTEGER NOT NULL,
  CNAME VARCHAR(20),
  CDAY VARCHAR(20),
   );
   DELETE FROM TMP_TBL;
  SELECT * FROM FN_RET_REFCURSOR() INTO V_CURS;
  ITER := 1;
   LOOP
     FETCH V_CURS INTO V_CURS_Rec;
     EXIT WHEN NOT FOUND;
     INSERT INTO TMP_TBL SELECT ITER, (V_CURS_Rec).*;
    ITER := ITER + 1;
   END LOOP;
  RETURN;
END; $$;

推荐答案

作为我在下面完成的解决方法

As a workaround I have done below

CREATE OR REPLACE FUNCTION FN_TEST()
RETURNS VOID LANGUAGE plpgsql
   AS $$
   DECLARE
   V_CURS REFCURSOR;
   V_Rec_CNAME VARCHAR(20);
   V_Rec_CDAY VARCHAR(20);
   ITER INTEGER;
BEGIN
   create temporary table if not exists TMP_TBL
   (
  INDX INTEGER NOT NULL,
  CNAME VARCHAR(20),
  CDAY VARCHAR(20)
   );
   DELETE FROM TMP_TBL;
  SELECT * FROM FN_RET_REFCURSOR() INTO V_CURS;
  ITER := 1;
   LOOP
     FETCH V_CURS INTO V_Rec_CNAME, V_Rec_CDAY;
     EXIT WHEN NOT FOUND;
     INSERT INTO TMP_TBL VALUES (ITER, V_Rec_CNAME, V_Rec_CDAY);
     ITER := ITER + 1;
   END LOOP;
  RETURN;
END; $$;

这篇关于将 refcursor 提取到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！