Postgresql - Escape single qoutes in dynamic queries

Question

Version: PG-13.3 with pgAdmin4

How do I escape single quotes in dynamic SQL in a function?

Here is my code

DO $$
DECLARE
    uuid text;
BEGIN
        uuid := 'X-2132135671';
        -- Create a function using above id
        EXECUTE format(E'CREATE FUNCTION system_uid() RETURNS text AS \'SELECT %L\' LANGUAGE SQL IMMUTABLE;', uuid);
END;
$$ LANGUAGE plpgsql;

here when I try to execute the code I am getting error as below

ERROR:  syntax error at or near "X"
LINE 1: ...UNCTION system_uid() RETURNS text AS 'SELECT 'X-21321356...
                                                             ^
QUERY:  CREATE FUNCTION system_uid() RETURNS text AS 'SELECT 'X-2132135671'' LANGUAGE SQL IMMUTABLE;
CONTEXT:  PL/pgSQL function inline_code_block line 7 at EXECUTE
SQL state: 42601

Please suggest correct way of doing this.

Basics: https://stackoverflow.com/a/12320729/939860 – Erwin Brandstetter Aug 06 '21 at 02:32 — Erwin Brandstetter, Aug 06 '21 at 02:32

Jim Jones · Accepted Answer · 2021-08-05T14:12:04.680

1

Use multiple "dollar quotes":

DO $$
DECLARE
    uuid text;
BEGIN
  uuid := 'X-2132135671';
  -- Create a function using above id
  EXECUTE format($f$CREATE FUNCTION system_uid() RETURNS text AS $s$SELECT %L$s$ LANGUAGE SQL IMMUTABLE;$f$, uuid);
END;
$$ LANGUAGE plpgsql;

Demo: db<>fiddle

edited Aug 05 '21 at 14:12

answered Aug 05 '21 at 09:48

Jim Jones

18,404
3
35
44

score 1 · Answer 2 · answered Aug 07 '21 at 03:38

In addition to nested "dollar quotes" I recommend building the SQL statement you want prior to the Execute statement itself and doing a "raise notice/log"on that statement. Doing so gives you the advantage of seeing the actual statement you actually run. That way when a error occurs you can see the exact statement you ran. Something like:

do $do$
declare
    k_base constant text :=
           $stmt$
               create function system_uid() 
                 returns text 
                 language sql immutable                      
               as $$ select %L; $$;
           $stmt$;
     
    l_uuid text;
    l_stmt text; 

begin
        l_uuid := 'X-2132135671';
        -- create a function using above id
        l_stmt = format (k_base, l_uuid);
        raise notice e'Running Statement:\n%',l_stmt; 
        execute l_stmt;  
end;
$do$;

Caution: UUID is a defined data type in Postgres so you should avoid using it a a column/variable name. It has a predefined structure (32hex digits). Be careful that you do not cause confusion.

Postgresql - Escape single qoutes in dynamic queries

2 Answers2