CODEWITHSUNDEEP
postgresql-9.5
lowdegeneration
lowdegeneration

Reputation: 379

Using LIKE pattern dynamically in execute format query in postgresql--9.5

I want to insert footext into LIKE pattern dynamically, but it gives error saying

ERROR: unrecognized conversion type specifier "'"........

This is my query:

   RETURN QUERY EXECUTE format ('SELECT foocolumn
             FROM footable  
                WHERE foocolumnother LIKE ''%L%''  ', footext);

Upvotes: 0

Views: 3675

Answers (3)

Bob Whitley
Bob Whitley

Reputation: 1

Another late entry. Doubling both the single-quotes and percents works for me in Postgres 11.x:

l_query text := FORMAT('
    SELECT e.gid, e.sessionname, e.shared, e.created, e.created_by 
    FROM exercise e
    WHERE LOWER(e.created_by) LIKE ''%%%s%%'';'
    , LOWER(p_user));

RETURN QUERY EXECUTE l_query;

Upvotes: 0

slevin
slevin

Reputation: 3886

Little late to the party, but according to Erwin's answer and the document, you can also try the ~ character of regex. So test out this 

RETURN QUERY EXECUTE format ('SELECT foocolumn
             FROM footable  
                WHERE foocolumnother ~ %L  ', footext);

Upvotes: 3

user330315
user330315

Reputation:

If you want to add a % into the format string you need to double it e.g. %F%%

%Lalso correctly adds single quotes so you don't need to put them in the format string.

However: using %F%% would generate 'bla'% - which is incorrect.

You need to add the wildcard to the variable not the format string:

RETURN QUERY EXECUTE format ('SELECT foocolumn
         FROM footable  
         HERE foocolumnother LIKE %L'  ', concat(footext, '%'));

Upvotes: 1

Related Questions