Postgresql – How to cast to int array in Postgresql

arraycastinsertplpgsqlpostgresql

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?

Best Answer

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:

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 (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:

Function could look like this:

CREATE OR REPLACE FUNCTION insaft_function() 
    RETURNS TRIGGER 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$ LANGUAGE plpgsql;

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

Related Question