Questions › Use array operators inside postgresql function

I'm trying to create a postgresql. But I keep having an error

ERROR: operator does not exist: integer[] <@ integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

CREATE OR REPLACE FUNCTION accumulate_cs_order(int[][], int)
returns int[][]
language sql
as $f$
SELECT CASE
WHEN array[abs($2)] <@ $1[1] THEN -- If $2 has been closed pass
  $1
WHEN $2 < 0 THEN -- elIf we're closing $2 add it to $1[1]
  array[array_append($1[1], abs($2)), $1[2]]
WHEN $2 < $1[2] THEN -- ElIf we're not closing $2 and it is cheaper than $1[2], this is our new CS
  array[$1[1], array[$2]]
ELSE
  $1
END;
$f$;

I can't figure out why it is not working. I have declared my variables with the right types, array[abs($2)] <@ $1[1] makes sense to me, because $1[1] is an array, but postgresql seems to be thinking it is an int.


1 Answers :
Björn Nilsson answered

It has to do with how to get a specific element vs a slice of the array. A slice

WHEN abs($2) = ANY($1[1:1]) THEN

Should work, but:

select (array[array[1,2,3],array[4,5,6],array[7,8,9]])[1:1];
   array   
-----------
 {{1,2,3}}

It's a rectangular slice of the whole array, not just a 1 dimensional array.

select (array[array[1,2,3],array[4,5,6],array[7,8,9]])[1:1] = array[1,2,3];
 ?column? 
----------
 f
Mehdi GMIRA replied
Thanks, It seems to be working :) but i still don't understand why select (array[array[1,2,3],array[4,5,6],array[7,8,9]])[1]; doesn't return {1,2,3} ??;