Postgres Function Parameter in RegEx Where Clause

Question

I'm wondering what the heck is wrong with this?

CREATE OR REPLACE FUNCTION maxNumber(int4)
RETURNS int4 AS $$
DECLARE
  v_year ALIAS FOR $1; 
BEGIN
  RETURN (
    SELECT count(*)
    FROM table
    WHERE stringcol ~ '[0-9]{2,3}.[0-9]{1,3}.v_year.[0-9]{1,3}'
  );
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

It always returns 0. The SQL itself seems to work. Thanks.

You should escape . as \\.

vks
– vks

2014-12-10 09:50:59 +00:00
Commented Dec 10, 2014 at 9:50 — vks
– vks, Commented Dec 10, 2014 at 9:50

dforce · Accepted Answer · 2014-12-10 11:56:47Z

1

CREATE OR REPLACE FUNCTION maxNumber(int4)
RETURNS int4 AS $$
DECLARE
BEGIN
    RETURN (SELECT 
    coalesce(
      MAX(
        CAST(
          substring(stringcol, '[0-9]{1,3}$')
        AS int)
      )
    , 0)
    FROM table 
    WHERE stringcol ~ (E'\\' || '.' || to_char($1, 'FM09') || E'\\' || '.[0-9]{1,3}$'));
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

answered Dec 10, 2014 at 11:56

dforce

2,2543 gold badges21 silver badges36 bronze badges

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

Comments

Denis de Bernardy · Accepted Answer · 2014-12-10 11:31:34Z

0

Shouldn't that be more like:

(E'[0-9]{2,3}\\.[0-9]{1,3}\\.' || v_year || E'\\.[0-9]{1,3}')

... as in, you're using a literal v_year as is, rather than concatenating its value with the two other sides of the expression.

answered Dec 10, 2014 at 11:31

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

Postgres Function Parameter in RegEx Where Clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related