How to use the format function in PostgreSQL when I have % in the string?

Question

I have a function which combine multiple parameters in a string if they're not null or empty. On specific parameters I use the % character for LIKE conditions. My problem is the format function which try to perform a transformation on it like if it was a parameter. How can I avoid this ?

I have 10+ tests on parameters and multiple LIKE. In the example below the second FORMAT function try to convert the % in TEST%

_nom_personne_like := 'TEST%';
_type_activite_personne := 'SOMETHING';
IF _nom_personne_like != '' THEN
    _query := _query || ' AND personne_planning.nom_personne LIKE %L ';
    _query := FORMAT(_query,_nom_personne_like);
END IF; 
IF _type_activite_personne != '' THEN 
    _query := _query || ' AND personne_planning.type_activite = %L ';
    _query := FORMAT(_query,_type_activite_personne);
END IF;`

The message returned by the server is : Conversion type specifier « ' » unrecognized I have already try to double the %, it doesn't work.

klin · Accepted Answer · 2016-06-15 19:25:03Z

3

I prefer format() over string concatenation due to better readability. I follow the rule not to use a variable as the first parameter of the function. Your code could look like this:

_nom_personne_like := 'TEST%';
_type_activite_personne := 'SOMETHING';
IF _nom_personne_like != '' THEN
    _query := 
        format(
            '%s AND personne_planning.nom_personne LIKE %L ', 
            _query,
            _nom_personne_like);
END IF; 
IF _type_activite_personne != '' THEN 
    _query := 
        format(
            '%s AND personne_planning.type_activite = %L ',
            _query,
            _type_activite_personne);
END IF;`

answered Jun 15, 2016 at 19:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Bidjes Over a year ago

I prefer the use of FORMAT() for query construction over || and it's working like a charm, thanks.

Patrick · Accepted Answer · 2016-06-15 16:34:16Z

2

You can just forget about the format() function like so:

_nom_personne_like := 'TEST%';
_type_activite_personne := 'SOMETHING';
IF _nom_personne_like != '' THEN
    _query := _query || ' AND personne_planning.nom_personne LIKE ' ||
              quote_literal(_nom_personne_like);
END IF; 
IF _type_activite_personne != '' THEN 
    _query := _query || ' AND personne_planning.type_activite = ' ||
              quote_literal(_type_activite_personne);
END IF;

answered Jun 15, 2016 at 16:34

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to use the format function in PostgreSQL when I have % in the string?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related