postgresql PL/pgSQL 中的“$$”有什么用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12144284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are '$$' used for in PL/pgSQL
提问by vector
Being completely new to PL/pgSQL , what is the meaning of double dollar signs in this function:
作为 PL/pgSQL 的新手，这个函数中双美元符号的含义是什么：
CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS $$
BEGIN
IF NOT ~ 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;
I'm guessing that, in RETURNS boolean AS $$, $$is a placeholder.
我猜，在RETURNS boolean AS $$，$$是一个占位符。
The last line is a bit of a mystery: $$ LANGUAGE plpgsql STRICT IMMUTABLE;
最后一行有点神秘： $$ LANGUAGE plpgsql STRICT IMMUTABLE;
By the way, what does the last line mean?
顺便问一下，最后一行是什么意思？
回答by Erwin Brandstetter
The dollar signs are used for dollar quotingand are in no way specific to function definitions. It can be used to replace single quotes practically anywhere in SQL scripts.
美元符号用于美元引用，并不特定于函数定义。它几乎可以用来替换 SQL 脚本中任何地方的单引号。
The body of a function happens to be a string literal which has to be enclosed in single quotes. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to avoid quoting issues inside the function body. You could write your function definition with single-quotes just as well. But then you'd have to escape all single-quotes in the body:
函数体恰好是一个字符串文字，必须用单引号括起来。Dollar-quoting 是 PostgreSQL 特定的单引号替代品，以避免在函数体内出现引用问题。您也可以使用单引号编写函数定义。但是你必须转义正文中的所有单引号：
CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS
'
BEGIN
IF NOT ~ e''^\+\d{3}\ \d{3} \d{3} \d{3}$'' THEN
RAISE EXCEPTION ''Malformed string "%". Expected format is +999 999'';
END IF;
RETURN true;
END
' LANGUAGE plpgsql STRICT IMMUTABLE;
This isn't such a good idea. Use dollar-quoting instead, more specifically also put a token between the $$to make it unique - you might want to use $-quotes inside the function body, too. I do that a lot, actually.
这不是个好主意。改用美元引用，更具体地说，还在 之间放置一个标记$$以使其唯一 - 您可能也想在函数体内使用 $-quotes。事实上，我经常这样做。
CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean
AS
$func$
BEGIN
...
END
$func$ LANGUAGE plpgsql STRICT IMMUTABLE;
Details:
细节：
As to your second question:
Read the most excellent manual on CREATE FUNCTIONto understand the last line of your example.
至于你的第二个问题：
阅读最优秀的手册CREATE FUNCTION来理解你例子的最后一行。
回答by Captain Coder
The $$ is a delimiter you use to to indicate where the function definition starts and ends. Consider the following,
$$ 是一个分隔符，用于指示函数定义的开始和结束位置。考虑以下，
CREATE TABLE <name> <definition goes here> <options go here, eg: WITH OIDS>
The create function syntax is similar, but because you are going to use all sorts of SQL in your function (especially the end of statement; character), the parser would trip if you didn't delimit it. So you should read your statement as:
create 函数语法是相似的，但是因为您将在函数中使用各种 SQL（尤其是语句结尾；字符），如果您没有分隔它，解析器就会跳闸。所以你应该把你的陈述读成：
CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS <code delimited by $$> LANGUAGE plpgsql STRICT IMMUTABLE;
The stuff after the actual definition are options to give the database more information about your function, so it can optimize its usage.
实际定义之后的内容是为数据库提供有关您的函数的更多信息的选项，因此它可以优化其使用。
In fact if you look under "4.1.2.2. Dollar-Quoted String Constants" in the manual, you will see that you can even use characters in between the dollar symbols and it will all count as one delimiter.
实际上，如果您查看手册中的“4.1.2.2. Dollar-Quoted String Constants”，您会发现您甚至可以在美元符号之间使用字符，并且它们都将被视为一个分隔符。
