在PL/PgSQL EXECUTE动态查询中正确插入文字 [英] Correctly inserting literals in PL/PgSQL EXECUTE dynamic queries

问题描述

以下是plpgsql函数的一部分.问题是source_geom和target_geom的结果是character varying数据类型，因此我需要在引号('')中同时包含source_geom和target_geom.问题是，用plpgsql语言我不知道自己能做到这一点.
这是我目前的情况:

The following is part of a plpgsql function. The problem is that the result of source_geom and target_geom is a character varying data type, and therefore I need to surround the both source_geom and target_geom in quotes(' '). The thing is that in plpgsql language how I don't know I can do it.
Here's what I have at the moment:

 EXECUTE 'update ' || quote_ident(geom_table) || 
        ' SET source = ' || source_geom || 
        ', target = ' || target_geom ||
        ' WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

我遇到的错误如下；

ERROR:  syntax error at or near "C03B9E3B66052D400DDEFC2BD0F24140"
LINE 1: ...pdate track_points SET source = 0101000020E6100000C03B9E3B66...
                                                             ^
QUERY:  update track_points SET source = 0101000020E6100000C03B9E3B66052D400DDEFC2BD0F24140, target = 0101000020E610000075690DEF83052D40F88E75CCD4F24140 WHERE ogc_fid =  2
CONTEXT:  PL/pgSQL function "create_network" line 26 at EXECUTE statement

请问我如何解决此问题的任何建议??

Please any suggestions how I can solve this problem.?

推荐答案

使用多余的引号:

EXECUTE 'update ' || quote_ident(geom_table) || 
        ' SET source = ''' || source_geom || ''' 
        , target = ''' || target_geom || '''
        WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

这篇关于在PL/PgSQL EXECUTE动态查询中正确插入文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！