postgres dblink escape单引号 [英] postgres dblink escape single quote

问题描述

相关链接：

• postgresql中的字符串文字和转义字符

• String literals and escape characters in postgresql

这是我的错误： / p>

Here is my error:

ERROR:  type "e" does not exist

这是我的查询：

SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
    E'SELECT field_1, 
    CASE WHEN field_2 IS NOT NULL 
    THEN \'inactive\' ELSE \'active\' 
    END AS field_status 
    FROM the_table 
     ') 
AS linkresults(field_1 varchar(20),field_2 varchar(8))

如果我使用双引号，删除单引号的反斜杠转义，并在SELECT语句之前删除E

If I use double quotes, remove the backslash escape for the single quotes and remove the E before the SELECT statement

SELECT *
FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
    "SELECT field_1, 
    CASE WHEN field_2 IS NOT NULL 
    THEN 'inactive' ELSE 'active' 
    END AS field_status 
    FROM the_table 
     ") 
AS linkresults(field_1 varchar(20),field_2 varchar(8))

我得到这个：

NOTICE:  identifier "SELECT ..." will be truncated

而且我也会收到错误，因为我的查询已被截断。

And the I also get the ERROR as my query has been truncated.

我以前用dblink转义了，所以有一个服务器设置或需要配置的东西？

I have escaped with dblink like this before, so is there a server setting or something I need to configure?

我知道如果我在sql服务器本身运行它，而不是使用dblink，这个查询工作正常。任何想法？

I know the query works just fine if I run it on the sql server itself, but not with dblink. Any thoughts?

Postgres版本8.4

Postgres version 8.4

推荐答案

尝试替换\ 'inactive'\\'与''inactive'' - 注意：两个单引号

Try replacing \'inactive\' with ''inactive'' -- caution: two single quotes

   SELECT *
    FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
        'SELECT field_1, 
        CASE WHEN field_2 IS NOT NULL 
        THEN ''inactive'' ELSE ''active'' 
        END AS field_status 
        FROM the_table 
         ') 

AS linkresults(field_1 varchar(20),field_2 varchar(8))

替代（上一个）解决方案

Alternative (previous) solution

   SELECT *
    FROM dblink('host=theHostName port=1234 dbname=theDBName user=theUser password=thePassword',
        'SELECT field_1, 
        CASE WHEN field_2 IS NOT NULL 
        THEN E\'inactive\' ELSE E\'active\' 
        END AS field_status 
        FROM the_table 
         ') 

AS linkresults(field_1 varchar(20),field_2 varchar(8))

这篇关于postgres dblink escape单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！