sql - 在存储过程中调用存储过程

Question

我正在尝试在 postgres 9.3 上使用 sql 在一个函数中调用一个函数。

这个问题与another post by me有关.

我写了下面的函数。到目前为止，我未能合并任何类型的保存输出 (COPY) 语句，因此我试图通过创建嵌套函数打印输出函数来解决此问题。

CREATE FUNCTION retrieve_info(TEXT, TEXT) RETURNS SETOF   
retrieve_info_tbl AS $$
 SELECT tblA.id, tblA.method, tblA.species, tblA.location
 FROM tblA
 WHERE method=$1 AND species=$2
 GROUP BY id, method, species
 ORDER BY location
$$ LANGUAGE 'sql';

上述功能有效。

尝试创建嵌套函数。

CREATE FUNCTION print_out(TEXT, TEXT) RETURNS void AS $$
 COPY (SELECT * FROM retrieve_info($1, $2)) TO 'myfilepath/test.csv'    
 WITH CSV HEADER;
$$ LANGUAGE 'sql';

调用嵌套函数。

SELECT * FROM print_out('mtd1','sp1');

输出

上面给出了这个错误:列“$1”不存在 SQL 状态:42P02 上下文:SQL 函数“print_out”语句 1。但是，当将 print_out() 中的 arg1、arg2 替换为 'mtd1'、'sp1' 时，正确的输出将打印到 test.csv(如下所示)

id | method | ind | location
----------------------------
1a | mtd1   | sp3 | locA
1d | mtd1   | sp3 | locB

如何让 retrieve_info() 的 arg1、arg2 在 print_out() 中正确调用 arg1、arg2？

我完全卡住了。不胜感激任何指点，谢谢

Answer 1

COPY有点奇怪，因为它将其 query 参数视为字符串，即使它没有写成字符串。结果是查询:

SELECT * FROM retrieve_info($1, $2)

不是在函数的上下文中执行的，它是在 COPY 本身的上下文中执行的。即使你说:

copy (select * from t) ...

它更像是你写的:

copy 'select * from t' ...

所以当查询被执行时，函数参数不再有任何意义，COPY 的 query 参数可能看起来像其他语言中的闭包，但它不是，它更像是一个传递给 eval 的字符串。

您可以使用常用的万不得已的 Kludge 来绕过这种奇怪现象:dynamic SQL .如果您编写函数以使用字符串整理和执行，您应该会得到更好的结果:

create or replace function print_out(text, text) returns void as $$
begin
    execute 'copy ('
         || 'select * from retrieve_info'
         ||     '(' || quote_literal($1) || ',' || quote_literal($2) || ')'
         || ') to ''myfilepath/test.csv'' with csv header;';
end;
$$ language plpgsql;