如何在“psql”中将替换变量嵌入到字符串中(How to embed substituion variable into string in “psql”)

如何在“psql”中将替换变量嵌入到字符串中(How to embed substituion variable into string in “psql”)

我正在调用一个bash脚本来准备一些目录等。在某些时候它还会调用这样的psql脚本：

psql $PSQL_LOGIN -v SERVER=$SERVER

到现在为止还挺好。

我想要执行的sql是这样的：

select 'wget :SERVER.xxxx.com?geoX=id1&geoY=id2' from table1 where id1 > zzz;

我期待的是一个看起来像的字符串

wget test.xxx.com?geoX=yyy&geoY=qqq

在oracle我可以做类似的事情

select 'wget' || :SERVER '.xxx.com?geoX='|| id1 || etc etc

但是我不能让它在postgres中工作，因为它应该嵌入一个字符串我试图逃避'并且withoud但是到目前为止没有运气。 有人有任何想法吗？

I'm calling a bash script which prepares some directories etc. At some point it also calls a psql script something like this:

psql $PSQL_LOGIN -v SERVER=$SERVER

So far so good.

The sql that I want to execute is something like this:

select 'wget :SERVER.xxxx.com?geoX=id1&geoY=id2' from table1 where id1 > zzz;

What I expect out of this is a string that looks like

wget test.xxx.com?geoX=yyy&geoY=qqq

In oracle I could do something like

select 'wget' || :SERVER '.xxx.com?geoX='|| id1 || etc etc

But I cannot make it work in postgres when it should be embedded in a string I've tried to escape the ' and withoud but so far no luck. Anybody got any ideas?

最满意答案

我找到了这个问题的答案。 从shell脚本调用时，它在shell脚本本身中必须完成转义。 所以不是上面我应该做这样的调用：

psql $PSQL_LOGIN -V SERVER="'$SERVER'"

这是双引号和单引号。 然后sql文件可以像在Oracle中一样完成：

select 'wget' || :SERVER '.example.com?geoX='|| id1 || ' etc

I've found the answer to this question. When calling from the shell script, its in the shell script itself that the escaping must be done. So instead of the above i should do the calling like this :

psql $PSQL_LOGIN -V SERVER="'$SERVER'"

that is double quote and single quote. Then the sql file can be done the same way as in Oracle :

select 'wget' || :SERVER '.example.com?geoX='|| id1 || ' etc