我正在创建一个函数来从数据库中删除产品类别。首先,它选择从要从中删除的表继承的所有子表,如果子类别依赖于它,它将返回表名的 JSON 对象。用 SELECT 查询中的值填充数组的最有效方法是什么?
CREATE OR REPLACE FUNCTION delete_category(catid INT)
RETURNS json AS $$
DECLARE
depend "catalog";
dependlist "catalog"[];
BEGIN
FOR depend IN SELECT * FROM catalog LOOP
dependlist:=dependlist || depend;
END LOOP;
END;
$$ LANGUAGE plpgsql;
使用追加||
运算符非常慢 - 在某些较旧的PostgreSQL版本上,它非常慢。您可以使用两种可能性 -array_agg
或ARRAY(SUBSELECT)
构造函数中提到:
dependlist := ARRAY(SELECT depend FROM catalog);
或
dependlist := (SELECT array_agg(depend) FROM catalog);
或
SELECT array_agg(depend) FROM catalog INTO dependlist;
对于所有可能性,性能都应该相同。
我使用了 array_agg(( 函数,这工作正常。我唯一不确定的是为什么我在 FROM 中的表名后添加了依赖?
CREATE OR REPLACE FUNCTION delete_category(catid INT)
RETURNS jsonb AS $$
DECLARE
--depend "catalog";
dependlist "catalog"[];
cnt INT;
BEGIN
--Check no other tables dependent
SELECT array_agg(depend) INTO dependlist FROM catalog depend
WHERE inherit_from=catid;
IF array_length(dependlist,1) IS NOT NULL THEN
RETURN jsonb_build_object('error',
'children','tables',to_jsonb(dependlist));
END IF;
--Check table is empty of products
SELECT COUNT(*) INTO cnt FROM (SELECT tablename
FROM catalog WHERE catalogid=catid) AS tname;
RETURN jsonb_build_object('error','none');
END;
$$ LANGUAGE plpgsql;