CODEWITHSUNDEEP
postgresqlescapingquotes
Jeff Kyzer
Jeff Kyzer

Reputation: 646

escaping quotes in execute insert statment

I have a function that has an insert inside within loop. See the function below.

        create temp table temp2 (id serial, other_value uuid);
        CREATE OR REPLACE function verify_uuid() returns varchar AS $$
        declare uu RECORD;
        BEGIN
            FOR uu IN  select * from temp1 
            loop
            execute 'INSERT INTO temp2 values ''' || uu ||''':uuid';
            END LOOP;
        END
        $$
        LANGUAGE 'plpgsql' ;
        select verify_uuid();

The problem that I am having is the values part. With its present setup, I am getting the error:

QUERY: INSERT INTO temp2 values '(1,6f32e71c-9aad-48a9-a72c-bdec2f4548a2)':uuid

The quotes are in the wrong place, and I am not sure how to get them in the right place.

Upvotes: 2

Views: 111

Answers (2)

Jeff Kyzer
Jeff Kyzer

Reputation: 646

So in the end, I went with the following. It got me to this point:

EXECUTE 'INSERT INTO temp2 values ('||uu.id||','''|| uu.some_value||''')';

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

execute
    'insert into temp2 (other_value) values ($1)'
    using uu.the_column::uuid;

http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Upvotes: 0

Related Questions