CODEWITHSUNDEEP
regexpostgresqlfunctionplpgsql
Fred B
Fred B

Reputation: 142

Store regexp_matches result in variables

In a psql function I use regexp_matches. The result should go into 2 var db_datum_von_string and db_datum_von_string. However the second is always null even if the pattern matches 2 strings.

The first value is correct. I guess the issue comes from the res[2] but I can't figure out what it is.

Notice that SELECT regexp_matches('1234ffdsafdsa 4554 ', '[0-9]+', 'g') works perfectly and return 2 rows.

CREATE OR REPLACE FUNCTION ajl_TEST_datum_check(
      arg_datum_id integer,
      req_datum integer,
      flag integer
       ) RETURNS text AS $$
DECLARE
      db_datum text;
      db_datum_von_string text;
      db_datum_bis_string text;
      temp integer;
BEGIN
      SELECT datum.datum  INTO  db_datum
      FROM datum
      WHERE datum_id = arg_datum_id;

--- the issue starts here

    SELECT res[1], res[2] INTO db_datum_von_string, db_datum_bis_string
    FROM (SELECT regexp_matches(db_datum, '[0-9]+', 'g') res) y;

--- end of trouble


    IF db_datum_bis_string IS NULL THEN
        RETURN db_datum_von_string;
    ELSE 
        RETURN TRUE;
    END IF;

END;
$$ LANGUAGE plpgsql;

Upvotes: 2

Views: 714

Answers (1)

klin
klin

Reputation: 121764

Your query returns two rows of one-element array values:

SELECT res[1], res[2]
FROM (
    SELECT regexp_matches('1234ffdsafdsa 4554 ', '[0-9]+', 'g') res
) y;

 res  | res 
------+-----
 1234 | 
 4554 | 
(2 rows)

You should convert elements of the rows to an array:

SELECT res[1], res[2]
FROM (
    SELECT array_agg(res) res
    FROM (
        SELECT unnest(regexp_matches('1234ffdsafdsa 4554 ', '[0-9]+', 'g')) res
        ) y
    ) x;

 res  | res  
------+------
 1234 | 4554
(1 row)

Upvotes: 3

Related Questions