PL/pgSQL 中的“$$"有什么用 [英] What are '$$' used for in PL/pgSQL

问题描述

对 PL/pgSQL 完全陌生，this 中的双美元符号是什么意思功能:

Being completely new to PL/pgSQL , what is the meaning of double dollar signs in this function:

CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS $$
BEGIN
  IF NOT $1 ~  e'^\+\d{3}\ \d{3} \d{3} \d{3}$' THEN
    RAISE EXCEPTION 'Wrong formated string "%". Expected format is +999 999';
  END IF;
  RETURN true; 
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

我猜，在 RETURNS boolean AS $$ 中，$$ 是一个占位符.

I'm guessing that, in RETURNS boolean AS $$, $$ is a placeholder.

最后一行有点神秘:$$ LANGUAGE plpgsql STRICT IMMUTABLE;

顺便问一下，最后一行是什么意思?

By the way, what does the last line mean?

推荐答案

这些美元符号 ($$) 用于 美元引用，这绝非特定于函数定义.它可用于替换 SQL 脚本中任何地方包含字符串文字(常量)的单引号.

These dollar signs ($$) are used for dollar quoting, which is in no way specific to function definitions. It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts.

函数体恰好是这样的字符串文字.Dollar-quoting 是 PostgreSQL 特定的单引号替代品，以避免转义嵌套的单引号(递归).您也可以将函数体用单引号括起来.但是你必须转义正文中的所有单引号:

The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to avoid escaping of nested single quotes (recursively). You could enclose the function body in single-quotes just as well. But then you'd have to escape all single-quotes in the body:

CREATE OR REPLACE FUNCTION check_phone_number(text)
  RETURNS boolean
  LANGUAGE plpgsql STRICT IMMUTABLE AS
'
BEGIN
  IF NOT $1 ~  e''^\+\d{3}\ \d{3} \d{3} \d{3}$'' THEN
    RAISE EXCEPTION ''Malformed string "%". Expected format is +999 999'';
  END IF;
  RETURN true; 
END
';

这不是个好主意.改用美元报价.更具体地说，还在 $$ 之间放置一个标记以使每对唯一 - 您可能希望在函数体内使用嵌套的美元引号.实际上，我经常这样做.

This isn't such a good idea. Use dollar-quoting instead. More specifically, also put a token between the $$ to make each pair unique - you might want to use nested dollar-quotes inside the function body. I do that a lot, actually.

CREATE OR REPLACE FUNCTION check_phone_number(text)
  RETURNS boolean  
  LANGUAGE plpgsql STRICT IMMUTABLE AS
$func$
BEGIN
 ...
END
$func$;

见:

• 在 PostgreSQL 中插入带单引号的文本

关于你的第二个问题:
阅读最优秀的关于CREATE FUNCTION的手册理解示例的最后一行.

As to your second question:
Read the most excellent manual on CREATE FUNCTION to understand the last line of your example.

这篇关于PL/pgSQL 中的“$$"有什么用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！