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 ...

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...