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

问题描述

对于PL/pgSQL来说是全新的东西，此双美元符号的含义是什么功能:

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 $$中的$$是占位符.

最后一行是一个谜:$$ LANGUAGE plpgsql STRICT IMMUTABLE;

顺便说一句，最后一行是什么意思?

By the way, what does the last line mean?

推荐答案

美元符号用于

The dollar signs are used for dollar quoting and are in no way specific to function definitions. It can be used to replace single quotes practically anywhere in SQL scripts.

函数的主体恰好是字符串文字，必须用单引号引起来.美元报价是PostgreSQL特定于单引号的替代，以避免在函数体内引用问题.您也可以用单引号编写函数定义.但是然后，您必须在体内将所有单引号转义:

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:

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 ''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.

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

详细信息:

• 在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屋！