Postgresql中$$(双美元符)替换‘(单引号)-摩杜云开发者社区

1.正常情况下使用单引号引用一个字符串

hank=# select 'the world is so beautiful';
         ?column?          
---------------------------
 the world is so beautiful

2.如果字符串中有单引号，可以写两个单引号

hank=# select 'I''m OK';
 ?column? 
----------
 I'm OK (1 row) --如果是老版本的数据库，使用E加\的方式，如下： hank=# select E'I\'m OK'; ?column? ---------- I'm OK
(1 row)

3.如果字符串中间有多个’或者\，那么用上线的方法将会写很多的’或者\，所以这里用$$,语法如下：

$tag$<string_constant>$tag$


如下：只使用$$和在$$之间加tag,效果一样
hank=# select $$I'm a string constant that contains a backslash \$$;
                     ?column?                      
---------------------------------------------------
 I'm a string constant that contains a backslash \ (1 row) hank=# select $big$I'm a string constant that contains a backslash \$big$; 
                     ?column?                      
---------------------------------------------------
 I'm a string constant that contains a backslash \

4.在匿名块中使用$$

如下一张表
hank=# \d bigtable
               Table "hank.bigtable"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 v      | integer |           |          | 

查询最大id,并用notice打印出来
do 
'declare v_id integer; begin select max(id) into v_id from bigtable; raise notice ''The number is: %'', v_id; end;';

NOTICE:  The number is: 1000000
DO


如上，有单引号，那么和之前的处理方法一样，需要写两个单引号
raise notice ''The number of films: %'', film_count;



为了避免单引号使用的混淆和易读性，使用$$代替，代码会更易读
do 
$$
declare
   v_id integer;
begin 
   select max(id) into v_id
   from bigtable;
   raise notice 'The number is: %', v_id;
end;
$$;

NOTICE:  The number is: 1000000
DO

5.函数里面使用’或者$$

语法格式如下：
create function function_name(param_list) 
    returns datatype
language lang_name
as 
 'function_body'

实例：
create function f_bigtable(
   int
) returns bigtable 
language sql
as 
  'select * from bigtable where id = $1';
hank=# select * from f_bigtable(444);
 id  |  v   
-----+------
 444 | 6958

使用$$替换后如下
create function f_bigtable(
   int
) returns bigtable 
language sql
as 
  $$select * from bigtable 
   where id = $1$$;


hank=# select * from f_bigtable(444);
 id  |  v   
-----+------
 444 | 6958

6.另外官方几个例子，4个，6个，10个单引号，使用$$替换

两个单引号，前面演示过
a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';
a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

四个单引号
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

六个单引号，其实就是四个和两个连着
a_output := a_output || '' AND name LIKE ''''foobar''''''
a_output := a_output || $$ AND name LIKE 'foobar'$$

十个单引号使用
a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';

a_output后面的值为 if v_... like ''...'' then return ''...''; end if;

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$'
    then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;