3

I'm trying to escape a '%' character inside a format() function in PostgreSQL. The function replaces columns values based on a list of columns, deleting returns (\n) and trimming the strings.

CREATE OR REPLACE FUNCTION eliminar_retornos(text) RETURNS VOID
AS $$
        declare
            i text;
            fields text[] := ARRAY['direccion', 'localidad', 'calle', 'esq1', 'esq2', 'obs'];
        BEGIN
            FOREACH i IN ARRAY fields
            loop
                EXECUTE format(
                    'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' '')))', $1, i)
                ;
            END LOOP;
        RAISE NOTICE 'Se actualizó la capa %', $1;
        END
        ;
$$ LANGUAGE plpgsql;

I want to change this function to only replace those strings that have returns, using something like;

'update %1$s set %2$s = trim(upper(replace(%2$s, E''\n'', '' ''))) where %2$s LIKE E''%\n%''', $1, i) 

The problem is that I don't know how to correctly escape the '%' for the 'LIKE' inside the format() function.

Sorry for posting something not reproducible, my knowledge is limited to achieve that.

1 Answer 1

6

The manual clearly says:

the special sequence %% may be used to output a literal % character

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.