HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Escape % inside FORMAT() function in Postgres

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
formatescapepostgresfunctioninside

Problem

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.

Solution

The manual clearly says:

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

Context

StackExchange Database Administrators Q#299259, answer score: 6

Revisions (0)

No revisions yet.