Postgres function to return regexp_matches result

Question

I am trying to write some postgresql functions to help me parse a string value like "30Gi" or "25Ti". I tried this, but can't get the syntax right, and can't figure out what the return type should be for the first function.

CREATE FUNCTION get_matches(VARCHAR) RETURNS ARRAY(VARCHAR) AS 
SELECT (regexp_matches ($1, '(\d+)([KGTM]i)'));

CREATE FUNCTION get_amount(ARRAY(VARCHAR)) RETURNS VARCHAR AS 
SELECT get_matches($1)[1];

CREATE FUNCTION get_units(ARRAY(VARCHAR)) RETURNS VARCHAR AS 
SELECT get_matches($1)[2];

Postgres version: PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit Testing my functions using pgAdmin4.

Belayer · Accepted Answer · 2022-05-23 20:06:42Z

1

You can get both returned in a single query. The following returns a table of 2 columns size and units for valid expressions. After validating the parameter is correctly formatted (via regex) it uses basically the same with regexp_replace to return each component.

create or replace function size_units(p_size_units text)
     returns table ( size  integer
                   , units text
                   )
    language sql
as $$; 
   select regexp_replace(p_size_units, '^(\d+)([KGTM]i)$', '\1')::integer
        , regexp_replace(p_size_units, '^(\d+)([KGTM]i)$', '\2')
    where p_size_units ~ '^\d+[KGTM]i$';
$$;

You can use the results in a select statement if desired. (see demo).

answered May 23, 2022 at 20:06

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Postgres function to return regexp_matches result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related