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

一尘不染

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

sql

我试图在postgres 9.3上使用sql调用函数内的函数。

我写了下面的函数。到目前为止，我还没有合并任何类型的save-output（COPY）语句，因此我试图通过创建嵌套函数print-out函数来解决此问题。

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');

输出

上面给出了这个ERROR: column "$1" does not exist SQL state: 42P02 Context: SQL function "print_out" statement 1。但是，当在print_out（）中将arg1，arg2替换为’mtd1’，’sp1’时，正确的输出将打印到test.csv（如下所示）

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

我如何才能在print_out（）中正确地获取retrieve_info（）的arg1，arg2来正确调用arg1，arg2？

我完全被困住了。希望有任何指示，谢谢

阅读 162

2021-03-10

共1个答案

一尘不染

COPY有点奇怪，因为query即使它不是写为字符串，它也会将其参数视为字符串。结果是query：

SELECT * FROM retrieve_info($1, $2)

不是在函数的上下文中执行，而是在COPY本身的上下文中执行。即使您说：

copy (select * from t) ...

就像您写的那样：

copy 'select * from t' ...

因此，在执行查询时，函数参数不再具有任何意义，queryCOPY的参数可能看起来像是其他语言中的闭包，但实际上并没有，它的作用更像是将字符串传递给eval。

您可以使用最后的手段Kludge：动态SQL来解决这种奇怪的情况。如果您编写函数以使用字符串wrangling和EXECUTE，则应该获得更好的结果：

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;

2021-03-10