plpgsql function to search and move index in array

Question

I'm trying to write a function to find elements ahead from given positions of given elements in a two dimensional array. basically it involves steps of finding all the positions in array for given element and move the positions a few steps back.

I have created a function to return all matches of the element. But need to modify the function to be able to move the index a few positions back from the position of given element.

see details below:

dev=# \d+ test
                          Table "public.test"
 Column  |  Type   | Modifiers | Storage  | Stats target | Description
---------+---------+-----------+----------+--------------+-------------
 id      | integer |           | plain    |              |
 actions | text[]  |           | extended |              |

dev=# select * from test;
 id |                                                actions
----+-------------------------------------------------------------------------------------------------------
  1 | {{a,1,1,1},{b,3,2,3},{c,2,3,4},{d,5,6,7},{e,3,4,5},{f,6,7,8},{g,0,9,0},{a,2,3,4},{k,3,4,5},{a,2,3,7}}
(1 row)

--function to get element of current position
CREATE OR REPLACE FUNCTION find_in_array(needle ANYELEMENT, haystack ANYARRAY,OUT cname text,out val1 numeric,out val2 numeric,out val3 numeric)
RETURNS setof record
  AS $$   
SELECT $2[i][1],$2[i][2]::numeric,$2[i][3]::numeric,$2[i][4]::numeric
    FROM generate_series(array_lower($2,1), array_upper($2,1)) AS i
     WHERE $2[i][1] = $1;   
$$ LANGUAGE sql STABLE;


dev=# select find_in_array('a',actions) from test;
 find_in_array
---------------
 (a,1,1,1)
 (a,2,3,4)
 (a,2,3,7)
(3 rows)

Now, given element a, I'd like to find the elements about 2-3 positions ahead from letter 'a' (if find the element with same letter, skip it). in the example, it would be: (e,3,4,5) (f,6,7,8) (a,2,3,4) --skipped, because it's got same letter a. (g,0,9,0)

based on answer from @Ziggy, I modified the above function to make it move back a few positions. Further from the current code, if there's a match positioned at the first element of the array, I hope to return a row like 'direct',0,0,0

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
    SELECT case when i=1 then 'direct' else $2[i-i_move][1] end,
    case when i=1 then 0 else $2[i-i_move][2]::numeric end,
    case when i=1 then 0 else $2[i-i_move][3]::numeric end,
    case when i=1 then 0 else $2[i-i_move][4]::numeric end
    FROM generate_series(2, 3) i_move,
         generate_series(array_lower($2,1), array_upper($2,1)) AS i
    WHERE $2[i][1] = $1
$$ LANGUAGE SQL STABLE;

The result is:

 dev=# select find_in_array_move('a',actions) from test;
 find_in_array_move
--------------------
 (direct,0,0,0)
 (direct,0,0,0)
 (f,6,7,8)
 (e,3,4,5)
 (a,2,3,4)
 (g,0,9,0)
 (,,,)
 (,,,)
 (,,,)
 (,,,)
(10 rows)

the expected result is (note that I cannot do a distinct as I would allow duplicated rows of "non direct" records):

 find_in_array_move
--------------------
 (direct,0,0,0)
 (f,6,7,8)
 (e,3,4,5)
 (a,2,3,4)
 (g,0,9,0)

any suggestion?

Thank you Rémy. I changed the variable name, now the error is: dev=# select find_in_array_move('a',actions) from test; ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function find_in_array_move(anyelement,anyarray) line 6 at SQL statement` — fairybetty
– fairybetty, Commented Jun 27, 2016 at 23:08

Ezequiel Tolnay · Accepted Answer · 2016-06-28 03:37:28Z

0

You can't SELECT to results from a plpgsql function. You must use RETURN QUERY:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY,OUT cname text,out val1 numeric,out val2 numeric,out val3 numeric)
 RETURNS setof record AS $$
declare 
  i_move int;
begin
  for i_move in 2..5 loop
    RETURN QUERY
      SELECT $2[i-i_move][1],$2[i-i_move][2]::numeric,$2[i-i_move][3]::numeric,$2[i-i_move][4]::numeric
      FROM generate_series(array_lower($2,1), array_upper($2,1)) AS i
      WHERE $2[i][1] = $1;
  end loop;
  RETURN;
end;
$$ LANGUAGE plpgsql STABLE;

Also, depending on your PostgreSQL version, you might be able to use the equivalent RETURNS TABLE, which ismuch more elegant and easier to maintain than using OUT parameters:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
...

Finally, a plpgsql LOOP is not needed if you simply use generate_series instead:

CREATE OR REPLACE FUNCTION find_in_array_move(needle ANYELEMENT, haystack ANYARRAY)
  RETURNS TABLE (cname text, val1 numeric, val2 numeric, val3 numeric)
  AS $$
    SELECT $2[i-i_move][1],$2[i-i_move][2]::numeric,$2[i-i_move][3]::numeric,$2[i-i_move][4]::numeric
    FROM generate_series(2, 5) i_move,
         generate_series(array_lower($2,1), array_upper($2,1)) AS i
    WHERE $2[i][1] = $1
    ORDER BY i_move, i;
$$ LANGUAGE SQL STABLE;

answered Jun 28, 2016 at 3:37

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

fairybetty Over a year ago

Thank you Ziggy. I guess it's probably because my postgresql version is too old, 8.2. as it throws error. I updated the original post with the error message.

Ezequiel Tolnay Over a year ago

@fairybetty Yes, it's a new feature in version 9+. You can still mix your original interface (with OUT parameters) and the SQL function (the last one) for better performance than using a LOOP.

fairybetty Over a year ago

Hi Ziggy, the last version of the function you provided works perfectly on my database, no errors thrown out. I'm checking the result and will let you know how it goes.

fairybetty Over a year ago

Hi Ziggy, is there a better way to remove the calculated subscript (i-i_move) of array that not exist?

fairybetty Over a year ago

I've created the desired function based on Ziggy's suggestion. Thus, I'll accept the answer. Thanks a lot Ziggy!

|

Collectives™ on Stack Overflow

plpgsql function to search and move index in array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related