如何在PL/pgSQL中查询所述自定义数据类型的数组中的自定义数据类型对象

Question

假设我有：CREATE TYPE compfoo AS (f1 int, f2 text);我创建了一个包含两列的表foo：fooid和fooname，对应于compfoo的字段，稍后我插入一些记录1, aa、2, bb、3, cc

然后，我定义了一个PL/pgSQL函数(大致如下：(

create or replace function foo_query()
returns text
language plpgsql
as $$
declare
r compfoo;
arr compfoo [];
footemp compfoo;
result text;
begin
for r in
select * from foo where fooid = 1 OR fooid = 2
loop
arr := array_append(arr, r);
end loop;
foreach footemp in array arr
loop
select footemp.f1 into result where footemp.f1 = 1;
end loop;
return result;
end;
$$

在这里，我按列名查询第一个foo，并将结果保存到一个compfoo数组arr中。稍后，我对arr进行迭代，并尝试按照compfoo中定义的字段名查询元素。

我在Postgres中没有得到错误，但我的函数的结果是null。

我做错了什么？

Answer 1

RAISE NOTICE应该是你最好的朋友。您可以在代码的某些点打印一些变量的结果。基本问题是没有很好地初始化值。arr变量由NULL值初始化，对NULL的任何操作都是NULL。

另一个问题出现在select footemp.f1 into result where footemp.f1 = 1;语句中。当结果为空时，Postgres中的SELECT INTO会用NULL值覆盖目标变量。在第二次迭代中，该查询的结果为空集，result变量设置在NULL上。

您的示例中最大的问题是编程风格。您使用ISAM样式，并且您的代码可能非常慢。

• 循环中不使用array_append，当您可以在查询中使用array_agg函数时，您不需要循环
• 当您不从表中读取数据时，不要使用SELECT INTO
• 不要试图重复Oracle的模式BULK COLLECT和FOREACHread-over collection。PostgreSQL不是Oracle，它使用非常不同的体系结构，这种模式不会提高性能(就像在Oracle上一样(，但可能会失去一些性能

您的固定代码可能看起来像：

CREATE OR REPLACE FUNCTION public.foo_query()
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
r compfoo;
arr compfoo [] default '{}'; --<<<
footemp compfoo;
result text;
begin
for r in
select * from foo where fooid = 1 or fooid = 2
loop
arr := array_append(arr, r);
end loop;

foreach footemp in array arr
loop
if footemp.f1 = 1 then --<<<
result := footemp.f1;
end if;
end loop;

return result;
end;
$function$
postgres-# ;

它返回预期的结果。但这是一个完美的例子，说明如何不编写存储过程。不要试图替换存储过程中的SQL。此过程的所有代码都可以仅由一个查询替换。最后，这段代码在处理更大的数据时可能会非常慢。