How to cast to int array in Postgresql?

Question

I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code. Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

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

which I can start by

INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);

I know that the mistake is in casting here TG_ARGV[0]::int[] into intarray which I would like loop through until the intarray is empty. There may exists better way to do such loop inserts.

ErwinBrandstetter's code output

Code

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

Running

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

getting

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

How can you cast from ARGV[] which is text to int array in PostgreSQL?

Answer 1

While passing integer numbers, you can either cast the whole array:

TG_ARGV::int[]

Or you can cast an element, then it must be the element type:

TG_ARGV[0]::int

I used it that way in my answer to your previous question:

• SELECT in trigger function in two tables

However, you are not passing integer numbers, but the text representation of integer arrays: an integer array literal - with illegal syntax, too: values must be enclosed in single quotes, double quotes are for identifiers:

(削除) 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}'); 

Since you are not passing integer but integer array literals, you cannot do either in your code. Consider this (using a clearer example with distinct numbers):

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 provided more for that.

Solution for your function

I suggest you either pass all integer numbers or a 2-dimensional array literal. Demonstrating code for the latter:

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

Or rather (the array pivoted, based on my educated guess):

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

So, this is one parameter:

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

As for the loop, use FOREACH m SLICE 1 IN ARRAY. See:

• Postgres - array for loop

Function could look like this:

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$;

But I have my suspicions there might be a simpler overall approach. This only makes sense if you have many tables that need the same trigger, just with different integer numbers ...

• Erwin Brandstetter: @Masi: I actually tested the function I provided and it works for me. Are you aware that int denotes the integer type and int[] denotes an array of integer? Some basics here: stackoverflow.com/a/12414884/939860
• Erwin Brandstetter: @Masi: Sorry, it must be TG_ARGV [0] ::int[]. I tested two versions (either pass all integer numbers or a 2-dimensional array literal) and mixed trigger and function definition from both by accident. The syntax TG_ARGV::int[] only works "all integer numbers" - multiple parameters like: insaft_function(101,201,201,300). Since you are passing a text literal for a (2-dimentional) int array as one parameter, you must refer to TG_ARGV[0]. I adapted the code above.
• Erwin Brandstetter: events.event_index_start and events.event_index_end must be defined as bigint accordingly.
• Léo Léopold Hertz 준영: Yes, so use bigint if unsigned int but here signed int so keep to use just the int. Followup thread here stackoverflow.com/questions/31533496/…

Answer 2

To me, it looks like you're on the right track, if I'm understanding your question clearly (which I'm not sure that I am. :P )

To me, it looks like you simply need a DECLARE block, where you are declaring variable values which will persist throughout the function block.

Add DECLARE

CREATE OR REPLACE FUNCTION insaft_function() 
RETURNS TRIGGER AS
DECLARE
myarray_0 int[] := TG_ARGV[0]::int[]
myarray_1 int[] := TG_ARGV[1]::int[]
$func$
BEGIN 
-- TODO Loop until myarray_0 empty
-- Decide what you really want to do here!!
-- Is it a parallel loop through myarray_0 and myarray_1, or a Cartesian, or...
-- END TODO
RETURN NULL; -- result ignored since this is an AFTER trigger
END 
$func$ LANGUAGE plpgsql;

You've got the casting right, but if you want to loop through them easily in the function, you just need to do it upfront with a DECLARE block.

We can leave the debate for another time, however, if this is the best approach for achieving the inserts you are hoping to do...