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?

Erwin Brandstetter · Accepted Answer · 2021-08-06 22:37:14Z

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

@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
– Erwin Brandstetter, Commented Jul 16, 2015 at 12:04
@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
– Erwin Brandstetter, Commented Jul 16, 2015 at 22:22
events.event_index_start and events.event_index_end must be defined as bigint accordingly. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 27, 2015 at 18:53
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/… — Léo Léopold Hertz 준영
– Léo Léopold Hertz 준영, Commented Jul 28, 2015 at 6:16

Chris · Accepted Answer · 2015-07-15 17:05:45Z

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

Stack Exchange Network

How to cast to int array in Postgresql?

ErwinBrandstetter's code output

2 Answers 2

Solution for your function

Add `DECLARE`

Linked

Hot Network Questions

How to cast to int array in Postgresql?

ErwinBrandstetter's code output

2 Answers 2

Solution for your function

Add DECLARE

Linked

Related

Hot Network Questions

Add `DECLARE`