CODEWITHSUNDEEP
postgresql
vinod hy
vinod hy

Reputation: 895

Issue while forming regex string in a stored procedure

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.

Upvotes: 0

Views: 45

Answers (2)

vinod hy
vinod hy

Reputation: 895

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}' ) || ') ... .. ...

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247545

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)) || ')';

Upvotes: 1

Related Questions