如何在 Postgresql 中轉換為 int 數組？

我想從 ARGV 投射

$$ $$這是 PostgreSQL 中的文本到 int 數組，我在程式碼中用TODO標記了虛擬碼。x86_64-unknown-linux-gnu 上的 PostgreSQL 9.4.3 中的程式碼，由 gcc (Debian 4.9.2-10) 4.9.2 64 位編譯：

CREATE TABLE measurements (
       measurement_id SERIAL PRIMARY KEY NOT NULL,
       measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
       event_id SERIAL PRIMARY KEY NOT NULL, 
       measurement_id INTEGER NOT NULL, 
       event_index_start INTEGER NOT NULL,
       event_index_end INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION insaft_function() 
   RETURNS TRIGGER AS 
$func$
BEGIN 
 -- TODO Loop until TG_ARGV[0] empty
INSERT INTO events (measurement_id, event_index_start, event_index_end) 
SELECT NEW.measurement_id, TG_ARGV[0]::int[], TG_ARGV[1]::int[];
 -- END TODO
RETURN NULL; -- result ignored since this is an AFTER trigger
END 
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements 
FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}"); 

我可以從

INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);

我知道錯誤在於將此處TG_ARGV[0]::int[]轉換為 intarray ，我想循環直到 intarray 為空。可能存在更好的方法來執行此類循環插入。

ErwinBrandstetter 的程式碼輸出

程式碼

DROP TABLE IF EXISTS measurements, events, file_headers;

CREATE TABLE measurements (
       measurement_id SERIAL PRIMARY KEY NOT NULL,
       measurement_size_in_bytes INTEGER NOT NULL
);

CREATE TABLE events (
       event_id SERIAL PRIMARY KEY NOT NULL, 
       measurement_id INTEGER NOT NULL, 
       event_index_start INTEGER NOT NULL,
       event_index_end INTEGER NOT NULL
);

DROP TRIGGER IF EXISTS insaft_ids ON measurements;
DROP FUNCTION IF EXISTS insaft_function();

CREATE OR REPLACE FUNCTION insaft_function() 
   RETURNS TRIGGER AS 
$func$
DECLARE
  m int[];
BEGIN 
  FOREACH m SLICE 1 IN ARRAY TG_ARGV::int[]
  LOOP
     INSERT INTO events (measurement_id, event_index_start, event_index_end) 
     SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
  END LOOP;
  RETURN NULL; -- result ignored since this is an AFTER trigger
END 
$func$ LANGUAGE plpgsql;

CREATE TRIGGER insaft_ids
AFTER INSERT ON measurements 
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{201,300}}'); 

跑步

sudo -u postgres psql detector -c "INSERT INTO measurements (measurement_size_in_bytes) VALUES (77777);"

得到

ERROR:  invalid input syntax for integer: "{{101,201},{201,300}}"
CONTEXT:  PL/pgSQL function insaft_function() line 5 at FOREACH over array

您如何從 ARGV 投射

$$ $$哪個是 PostgreSQL 中的文本到 int 數組？

在傳遞整數時，您可以轉換整個數組：

TG_ARGV::int[]

或者你可以強制轉換一個元素，那麼它必須是元素類型：

TG_ARGV[0]::int

我在回答您之前的問題時就是這樣使用的：

• SELECT 在兩個表中的觸發器函式中

但是，您傳遞的不是整數，而是整數數組的文本表示：整數數組文字 - 也具有非法語法：值必須用單引號括起來，雙引號用於標識符：

FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}"); 

FOR EACH ROW EXECUTE PROCEDURE insaft_function('{101, 111, 121}', '{101, 111, 121}'); 

由於您沒有傳遞integer整數數組文字，因此您不能在程式碼中執行任何操作。考慮一下（使用具有不同數字的更清晰範例）：

SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::int[];
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])::text[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])::int[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])[1][1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1]::int[];

@Chris 為此提供了更多資訊。

適合您的功能的解決方案

我建議您傳遞所有整數或**二維數組文字。後者的展示程式碼：

'{{101,111,121},{201,211,221}}'

或者更確切地說（數組旋轉，基於我有根據的猜測）：

'{{101,201},{111,211},{121,221}}'

所以，這是一個參數：

CREATE TRIGGER ...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{111,211},{121,221}}'); 

至於循環，使用FOREACH m SLICE 1 IN ARRAY. 看：

• Postgres - 循環數組

函式可能如下所示：

CREATE OR REPLACE FUNCTION insaft_function() 
 RETURNS TRIGGER
 LANGUAGE plpgsql AS 
$func$
DECLARE
  m int[];
BEGIN 
  FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]  -- reference 1st param
  LOOP
     INSERT INTO events (measurement_id, event_index_start, event_index_end) 
     SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
  END LOOP;
  RETURN NULL; -- result ignored since this is an AFTER trigger
END 
$func$;

但我懷疑可能有一個更簡單的整體方法。這僅在您有許多需要相同觸發器的表時才有意義，只是具有不同的整數……

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