PostgreSQL :如何将请求传递给函数

Question

我有一个图像表，需要使用C图像处理库从其中提取n个最相关的图像。我尝试通过在SQL中原型化该函数(Dummy)来确定将传递哪些参数：

CREATE OR REPLACE FUNCTION clusterize(anyarray,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT * FROM $1
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL; -- will be C 

WITH subreq AS (
  SELECT decode(encode(thumbnail,'escape'), 'base64') "data",
    otherfields
  FROM images -- which actually is another WITH subrequest ...
)

SELECT clusterize(* FROM subreq,12)

生成接近$1的语法错误。如何将"subreq“结果传递给函数？在这种情况下，是否意味着所有图像都将在调用函数之前加载到内存中，或者是否存在迭代器机制？

分解成两个函数是不是更简单，一个"addimage“函数调用每个图像，然后"clusterize(12)”返回一个图像表？

Answer 1

您的函数接受anyarray，但bytea不是通常PG意义上的“数组”。既然您返回的表是bytea的，并且您没有执行任何类型的强制转换或转换，那么有什么理由不将实际类型作为参数呢？

无论如何，如果您想从数组中执行SELECT *操作，则需要对其执行UNNEST操作以将其转换为表格形式。

因此，您的clusterize函数现在如下所示：

CREATE OR REPLACE FUNCTION clusterize(anyarray,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT * FROM UNNEST($1)
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL

现在从bytea数据创建一个实际的数组。bytea函数看起来并不好用，所以需要一些技巧才能做到：

WITH subreq AS (
        SELECT decode(encode(E'Th\\000omas'::bytea, 'escape'), 'escape') d
)
SELECT clusterize(ARRAY_AGG(SUBSTRING(d FROM g FOR 1)), 4)
FROM subreq, generate_series(1, LENGTH(d)) g

结果：

​

​

现在，如果我们可以将clusterize更改为一个bytea，我们就可以将这些繁琐的代码隐藏在其中：

CREATE OR REPLACE FUNCTION clusterize(bytea,integer) 
RETURNS TABLE(image bytea) AS $$
    SELECT SUBSTRING($1 FROM g FOR 1)
    FROM generate_series(1, LENGTH($1)) g
    ORDER BY random()
    LIMIT $2
$$ LANGUAGE SQL

WITH subreq AS (
        SELECT decode(encode(E'Th\\000omas'::bytea, 'escape'), 'escape') d
)
SELECT clusterize(d, 4)
FROM subreq

结果：

​

​