postgresql postgres dblink 转义单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6615732/
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
postgres dblink escape single quote
提问by Phill Pafford
Related Link:
相关链接：
Here is my error:
这是我的错误：
ERROR: type "e" does not exist
Here is my query:
这是我的查询：
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))
If I use double quotes, remove the backslash escape for the single quotes and remove the E before the SELECT statement
如果我使用双引号，请删除单引号的反斜杠转义并删除 SELECT 语句之前的 E
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))
I get this:
我明白了：
NOTICE: identifier "SELECT ..." will be truncated
And the I also get the ERROR as my query has been truncated.
由于我的查询已被截断，因此我也收到了 ERROR。
I have escaped with dblink like this before, so is there a server setting or something I need to configure?
我以前用过这样的 dblink 转义过，那么是否有服务器设置或我需要配置的东西？
I know the query works just fine if I run it on the sql server itself, but not with dblink. Any thoughts?
我知道如果我在 sql server 本身上运行查询，但不是在 dblink 上运行，查询工作得很好。有什么想法吗？
Postgres version 8.4
Postgres 8.4 版
回答by niktrs
Try replacing \'inactive\'with ''inactive''-- caution: two single quotes
尝试替换\'inactive\'为''inactive''-- 小心：两个单引号
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))
回答by kaushik
Try this query:
试试这个查询：
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))
