gpt4 book ai didi

postgresql - pl/pgsql block 上的准备语句调用失败(语法错误和函数不存在)

转载 作者:行者123 更新时间:2023-11-29 11:36:54 27 4
gpt4 key购买 nike

原始的 PL/pgSQL 匿名 block ,要执行的代码如下:

do $$
declare
tt integer[];
minRowNum integer;
maxRowNum integer;
MIN_TEMS constant integer := 1;
MAX_TEMS constant integer := 15;
LAST_ARR_IDX constant integer := MAX_TEMS * 2;
NUM_FILAS constant integer := 1000;
begin
create temp table NTematica(rownum, tematica_id) as
select
S.n, (S.n * 841)::integer
from
generate_series(1,357) S(n);
select
min(X.rownum), max(X.rownum) into minRowNum, maxRowNum
from
NTematica X;
prepare selectTematicasPlan(integer, integer, integer, integer) as
select
array_agg(X.tematica_id)
from
NTematica X
where
X.rownum in
(
select
trunc(random() * ($2 - $1 + 1) + $1) :: integer
from
generate_series($3, trunc(random() * ($4 - $3 + 1) + $3) :: integer)
);
for i in 1..NUM_FILAS loop
execute selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, MAX_TEMS);
raise notice 'First is % and % are the others', tt[1], tt[2:LAST_ARR_IDX];
end loop;
drop table NTematica cascade;
deallocate selectTematicasPlan;
end$$;

然后,执行失败并出现错误:

ERROR:  syntax error at or near "("
LINE 34: tt := execute selectTematicasPlan(minRowNum, maxRowNum...

然后,为了进行测试,我删除了“tt :=”并再次运行,结果如下:

ERROR:  function selecttematicasplan(integer, integer, integer, integer) does not exist
LINE 1: SELECT selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, M...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

更新:

在此先感谢大家。我要澄清一些问题:

我的初始代码是“从选择中插入”(包括 CTE),用于用 1000 万条记录填充表。我可以确认它非常慢。因此,我决定选择一个命令式解决方案:创建一个索引未记录表(像 map 数据结构一样工作)并循环 1000 万次以“执行”从“ map ”中“选择”数据,然后“插入”数据.我将循环的“选择”和“插入”拉出到准备好的语句中,因为这避免了对 PG 的 1000 万次解析工作。发布的代码仅与我在准备好的语句中遇到的问题有关。是否可以在 pl/pgsql 代码块中使用“准备”+“执行”? PG doc ( http://www.postgresql.org/docs/9.4/static/sql-prepare.html , http://www.postgresql.org/docs/9.4/static/sql-execute.html ) 没有谈论(反对或赞成)pl/pgsql。

注意:我的数据库版本(“select version()”)是: x86_64-unknown-linux-gnu 上的 PostgreSQL 9.4.4,由 gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2 编译,64 位

最佳答案

在 PL/pgSQL 函数中,您不使用准备好的语句。实际上,函数中的每个语句都是“准备好的”,就像带有 PREPARE 命令的语句一样。在一个匿名代码块(PL/pgSQL)中看起来有点奇怪,因为该 block 只使用一次然后被丢弃,但实际上它的工作原理是一样的。 The principle is documented here并且引用了 PREPARE 语句,这是合乎逻辑的因为行为本质上是相同的:代码块中的语句被解析并缓存以备将来使用。也许有点多余,但考虑到这个问题的各种答案中的不知情陈述,我想强调上面链接的文档中的一句话:

As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement

简而言之:在 plpgsql 代码块中显式创建准备语句,然后动态执行该准备语句是无用且低效的,并且在概念上存在缺陷。

您正在尝试做的是准备一条语句,然后由后端准备该准备,然后您动态地执行执行准备好的语句的语句。这里没有讽刺意味:这就是发生的事情。还要注意 PL/pgSQL EXECUTE 语句没有任何缓存:SQL 语句 EXECUTE selectTematicasPlan(minRowNum, maxRowNum, MIN_TEMS, MAX_TEMS) 每次都被解析调用。这是一个相当简单的语句,但是(如在 Klin's answer 中)每次都必须传入参数值,即使它们在每次调用中都相同。我希望你能看到这种方法的低效(如果没有,我就搁置了)。

回到你的例子,你的函数应该有点像这样:

DO $$
DECLARE
tt integer[];
minRowNum integer := 1;
maxRowNum integer := 357;
MIN_TEMS constant integer := 1;
MAX_TEMS constant integer := 15;
NUM_FILAS constant integer := 1000;
BEGIN
CREATE TEMP TABLE NTematica(rownum, tematica_id) AS
SELECT S.n, (S.n * 841)::integer
FROM generate_series(minRowNum, maxRowNum) S(n);

-- generate_series() produces numbers from the first parameter to the last, inclusive
-- no need to query for those values
<strike> select
min(X.rownum), max(X.rownum) into minRowNum, maxRowNum
from
NTematica X;</strike>

FOR i IN 1..NUM_FILAS LOOP
SELECT array_agg(X.tematica_id) INTO tt
FROM NTematica X
WHERE X.rownum IN (
SELECT trunc(random() * (maxRowNum - minRowNum + 1) + minRowNum)::integer
FROM generate_series(MIN_TEMS, trunc(random() * (MAX_TEMS - MIN_TEMS + 1) + MIN_TEMS)::integer)
);
RAISE NOTICE 'First is % and % are the others', tt[1], tt[2:array_upper(tt)];
END LOOP;
DROP TABLE NTematica;
END; $$;

您还可以将匿名代码块编写为单个 SQL 语句,此处使用 CTE 以提高可读性:

WITH params(minRowNum integer, maxRowNum integer, MIN_TEMS integer, MAX_TEMS integer) AS
SELECT 1, 357, 1, 15
), rowNums(rwNum integer, tematica_id integer) AS (
SELECT S.n, (S.n * 841)::integer
FROM params, generate_series(params.minRowNum, params.maxRowNum) S(n)
)
SELECT tt[1] AS first, tt[2:array_upper(tt)] AS rest
FROM generate_series(1, 1000) ON true
JOIN (
SELECT array_agg(rw.tematica_id) AS tt
FROM params p, rowNums rw
WHERE rw.rwNum IN (
SELECT trunc(random() * (p.maxRowNum - p.minRowNum + 1) + p.minRowNum)::integer
FROM generate_series(p.MIN_TEMS, trunc(random() * (p.MAX_TEMS - p.MIN_TEMS + 1) + p.MIN_TEMS)::integer)
) agg ON true;

多次使用的参数都在顶行,方便修改,没有不一致的风险。这应该比匿名代码块快得多,因为您会损失很多开销,尤其是在 TEMP TABLE 上。显然,您将获得作为常规表格数据的结果,而不是 1,000 条通知。

关于postgresql - pl/pgsql block 上的准备语句调用失败(语法错误和函数不存在),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32943370/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com