在存储过程中调用存储过程 [英] Calling a stored procedure within a stored procedure

问题描述

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

I am trying to call a function within a function using sql on postgres 9.3.

此问题与我的另一篇文章。

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

I have written the below function. So far I have failed to incorporate any kind of save-output (COPY) statement, so I am trying to work around this by creating a nested function print-out function.

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

以上功能有效。

An尝试创建嵌套函数。

An attempt to create a nested function.

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（如下所示）

The above gives this ERROR: column "$1" does not exist SQL state: 42P02 Context: SQL function "print_out" statement 1. However, when substituting the arg1, arg2 in print_out() with 'mtd1','sp1' the correct output is printed to test.csv (as seen below)

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

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

How would I get the arg1, arg2 of retrieve_info() to call arg1, arg2 properly within print_out()?

我完全被困住了。不胜感激任何指针，谢谢

I am completely stuck. Would appreciate any pointers, thanks

推荐答案

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

COPY is a bit odd as it sort of treats its query argument as a string even though it isn't written as a string. The result is that the query:

SELECT * FROM retrieve_info($1, $2)

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

isn't executed in the context of the function, it is executed in the context of COPY itself. Even though you say:

copy (select * from t) ...

它就像您写过的一样：

copy 'select * from t' ...

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

so by the time the query is executed, the function parameters no longer have any meaning, the query argument to COPY may look like it would behave like a closure in other languages but it doesn't, it acts more like a string that gets passed to eval.

您可以使用通常的不得已而来的克拉吉来解决这种奇怪的情况：< a href = http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN rel = nofollow>动态SQL 。如果编写函数以使用字符串整理和执行，应该会得到更好的结果。

You can get around this strangeness by using the usual Kludge of Last Resort: dynamic SQL. You should get better results if you write your function to use string wrangling and 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;

这篇关于在存储过程中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！