使用複合類型的數組作為函式參數並訪問它

我Books在 Postgres 中創建了一個類型，它有 2 個numeric欄位和 2 個varchar欄位。我想將一個數組發送Books到一個函式到INSERT匹配表中的這些值。

這是我的類型：

CREATE TYPE Books AS (
V_Book_ID NUMERIC,
V_Row_Num NUMERIC,
V_Book_OWNER TEXT,
V_Book_OWNER_ID TEXT
);

這是我的功能：

CREATE OR REPLACE FUNCTION Update_Table(row_book Books[]) RETURNS TEXT AS $$
DECLARE
  Status TEXT;
  I_Max integer := array_length(row_book, 1);
BEGIN
FOR I in 1..I_Max
 LOOP
  INSERT INTO books_table(Book_ID,
  Row_Num,
  Book_OWNER,
  Book_OWNER_ID)
  values
  (row_book[I].V_Book_ID,
  row_book[I].V_Row_Num,
  row_book[I].V_Book_OWNER,
  row_book[I].V_Book_OWNER_ID);
END LOOP;

  STATUS:='Saved';
exception when others then
  STATUS:='failure';
  RETURN STATUS;

END;
$$ language plpgsql;

如何向函式發送數據或如何使用數據呼叫函式？

我前段時間在 SO 上回答了一個類似的問題，以提出與unnest()@a_horse 類似的解決方案：

• 複合類型數組作為儲存過程輸入

如果books_table與復合類型具有相同的行類型books，則根本不需要創建附加類型，只需使用表的行類型即可：

CREATE TABLE books_table (
 book_id numeric
, row_num numeric
, book_owner text
, book_owner_id text
);

PL/pgSQL 函式

如果由於某些未聲明的原因需要plpgsql 函式：

CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
 RETURNS TEXT
 LANGUAGE plpgsql AS
$func$
DECLARE
  b books_table;
BEGIN
  FOREACH b IN ARRAY _books_arr
  LOOP
     INSERT INTO books_table  -- rare case where column list is no improvement
     SELECT b.*;
  END LOOP;

  RETURN 'Saved';

EXCEPTION WHEN others THEN
  RETURN 'Failure';
END
$func$;

帶有行值列表的範例呼叫：

SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

如果不使用VARIADIC，函式呼叫將需要一個數組參數。

數組****字面量（可選顯式轉換）：

SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

看：

• 如何從Java呼叫具有行類型數組作為參數的PostgreSQL函式
• 如何將自定義類型數組傳遞給 Postgres 函式

或者您可以使用像@a_horse 展示的數組建構子。數組字面量通常更容易提供。

要點：

使用更簡單**FOREACH**的循環數組。看：

• Postgres - 循環數組

除非您知道自己在做什麼，否則請避免在 Postgres 中使用 CaMeL 大小寫名稱。

（可選）使用**VARIADIC**參數來簡化函式呼叫的語法。然後，您可以提供行值列表。如果您使用以下命令，請注意函式參數的最大數量（預設為 100）VARIADIC：

• 選擇行，使得名稱與 pgsql 函式的輸入數組的元素匹配

SQL 函式

如果您不需要擷取異常，並且您也不需要返回字元串 ‘Saved’ / ‘Failure’，請簡化：

CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
 RETURNS void
 LANGUAGE sql AS
$func$
  INSERT INTO books_table
  SELECT * FROM unnest(_books_arr) b;
$func$;

db<>fiddle here

舊sqlfiddle

要創建一個類型的數組，請使用顯式數組建構子：

array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[]

因此，要呼叫您的函式，您需要使用：

select update_table(array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[])

但是你的函式有一個錯誤：在循環之後你缺少一個 return 語句，因為異常塊中的那個只有在發生異常時才會執行。

所以你需要這樣的東西：

begin 

 .... 

 STATUS:='Saved';
 return status;  ---<<< this is missing

 exception when others then
   STATUS:='failure';
   RETURN STATUS; --<<< this is only execute if an exception occurs

END;

或者，您需要另一個begin .. end;塊：

begin 

 begin    
   for ... 
   end loop;

   STATUS:='Saved';

 exception when others then
   STATUS:='failure';
 end;

 RETURN STATUS; 
END;

---

不相關，但是：您不需要循環遍歷數組。您可以使用單個語句更有效地做到這一點：

INSERT INTO books_table 
  (Book_ID, Row_Num, Book_OWNER, Book_OWNER_ID)
select *
from unnest(row_book);

引用自：https://dba.stackexchange.com/questions/131505