Issue while forming regex string in a stored procedure

Question

I am trying to run the below query in a stored procedure and its not working. We tried to print the query using NOTICE and we saw E gets appended to the regex and thats the reason the query doesnt show any output.

Not working

select order,version from mytable
where substring(version  from quote_literal('[0-9]+\.[0-9]{1}'))
IN ('6.2') and order= 'ABC';

But the same query if i run from pgadmin query tool, it works fine.

Working

select order,version  from mytable
where substring(version from '[0-9]+\.[0-9]{1}')
IN ('6.2') and order= 'ABC';

My requirement is to form the reqex dynamically in the stored procedure. Please guide on how to achieve this.

Below is the line of code in my stored procedure,

sql_query = sql_query || ' AND substring(version from ' || quote_literal( '[0-9]+\.[0-9]{1}' ) || ') IN (' || quote_literal(compatibleVersions) || ')';
raise notice 'Value: %', sql_query;                                        
EXECUTE sql_query  INTO query_result ;

and from notice i am getting the below output,

AND substring(version from E'[0-9]+\\.[0-9]{1}') IN ('{6.2}')

My requirement is to make this regex work.

I narrowed down to this query,

working

select substring(version from '[0-9]+\.[0-9]{1}') from mytable ;

not working

select substring(version from quote_literal('[0-9]+\.[0-9]{1}')) from mytable ;

Now i think its easy to fix it. You can try at your end also running this above queries.

quote_literal() is only needed in dynamic SQL. You don't need it in static SQL. So just use the working version. — user330315
– user330315, Commented Mar 14, 2019 at 14:53
Thanks for the reply. i intended to form the query dynamically inside the stored procedure by appending dynamic where conditions. — vinod hy
– vinod hy, Commented Mar 14, 2019 at 14:56
If I see that correctly, [0-9]{1} is the same as [0-9]. Also, you can use \d as a shorthand for [0-9]. See this link in Table 9-17. — 0xCAFEBABE
– 0xCAFEBABE, Commented Mar 14, 2019 at 14:58
What's wrong with using a string constant that starts with E? I guess you should show some of the failing code. — Laurenz Albe
– Laurenz Albe, Commented Mar 14, 2019 at 14:59

Laurenz Albe · Accepted Answer · 2019-03-15 07:48:46Z

1

Since your problem is not really the extended string literal syntax using E, but the string representation of the array in the IN list, your PL/pgSQL should look somewhat like this:

sql_query = sql_query ||
            ' AND substring(version from ' || quote_literal( '[0-9]+\.[0-9]{1}' ) ||
                ') IN (' || (SELECT string_agg(quote_literal(x), ', ')
                             FROM unnest(compatibleVersions
                            ) AS x(x)) || ')';

answered Mar 15, 2019 at 7:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

vinod hy · Accepted Answer · 2019-03-16 08:06:57Z

quote_literal should be used in situations where u want to dynamically construct queries. In such situation quote_literal will be replaced by E in the final constructed query.

right way to use

select * from config_support_module where substring(firmware from '[0-9]+\.[0-9]{1}') IN ('6.2');


select * from config_support_module where substring(firmware from E'[0-9]+\.[0-9]{1}') IN ('6.2') ;

wrong usage of quote_literal in static queries

select * from config_support_module where substring(firmware from quote_literal('[0-9]+\.[0-9]{1}')) IN ('6.2') ;
 This doesnt give you any errors/output.

quote_literal usage in dynamic queries

sql_query = sql_query || ' AND substring(version from ' || quote_literal( '[0-9]+\.[0-9]{1}' ) || ') ... .. ...

Collectives™ on Stack Overflow

Issue while forming regex string in a stored procedure

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