CODEWITHSUNDEEP
sqlpostgresqlplpgsqlliterals
dorbodwolf
dorbodwolf

Reputation: 320

nested quotes in plpgsql EXECUTE statement

I am writing plpgsql function with table name as parameter to handle my postgis database update tasks, but encounters problem about quotes in quotes in EXECUTE statement.

here is the plain SQL script and it works fine:

UPDATE baoluo SET sfzgjsyd='同时落在总规建设用地内外' 
        FROM lu_plan AS lu
        WHERE ST_Overlaps(lu.geom, baoluo.geom) 
        AND lu.is_construc = '建设用地'

when in plpgsql, I used double quotes for Chinese characters,here is the related part of code:

CREATE or replace FUNCTION process(_tb1 regclass, town_name TEXT, town_id TEXT) 
RETURNS VOID AS
$func$
DECLARE
   city_name text := '文昌市';  -- assign at declaration
   city_code text := '469005';
BEGIN
    --更新字段：
    EXECUTE format('
        UPDATE %s SET sfzgjsyd="同时落在总规建设用地内外" 
        FROM lu_plan 
        WHERE ST_Overlaps(lu_plan.geom, %s.geom) 
        AND lu_plan.is_construc = "建设用地"', _tb1, _tb1); 
END; 
$func$
LANGUAGE plpgsql;
select process('public.baoluo', '保罗镇', '469005000');

I expect it work fine but the log shows error with the field not exists.

Upvotes: 1

Views: 2264

Answers (2)

dorbodwolf
dorbodwolf

Reputation: 320

I also tried with @muistooshort suggestion of double the single quotes, it works fine.

...
EXECUTE format('
    UPDATE %I SET sfzgjsyd=''同时落在总规建设用地内外'' 
    FROM lu_plan 
    WHERE ST_Overlaps(lu_plan.geom, %I.geom) 
    AND lu_plan.is_construc = ''建设用地''', _tb1, _tb1); 
...

Upvotes: 0

klin
klin

Reputation: 121534

In format() strings, single quotes will get wrong with Chinese characters.

There is a problem with nested quotes, not with Chinese characters. Use dollar-quoting and %I for identifiers:

...
EXECUTE format($ex$
    UPDATE %I SET sfzgjsyd= '同时落在总规建设用地内外'
    FROM lu_plan 
    WHERE ST_Overlaps(lu_plan.geom, %I.geom) 
    AND lu_plan.is_construc = '建设用地'
    $ex$, _tb1, _tb1); 
...

Upvotes: 2

Related Questions