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

Question

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

score 3 · Answer 1 · answered Jan 29 '18 at 18:20

3

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

answered Jan 29 '18 at 18:20

slevin

4,166
20
69
129

nice one, saved my searches! Thanks mate! )) – Ivan Kolyhalov Sep 10 '20 at 18:54
This is gold, and if you want case insensitive searches use ~* – JDawgg Aug 16 '23 at 02:49

score 1 · Accepted Answer · answered Sep 01 '16 at 08:28

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

PG 11 in 2020., this results with the same error "unrecognized conversion type specifier " ' ". The answer by slevin solves it. — Ivan Kolyhalov, Sep 10 '20 at 18:52

score 0 · Answer 3 · answered Aug 28 '19 at 15:21

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;

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

3 Answers3