英文:
PostgreSQL - how build a where clause from nested array
问题
我想创建一个具有所需元组数组的函数，其中元组是用户类型（文件文本，索引小整数），用于生成 WHERE 子句。我不知道如何实现（我试图避免使用 UNION）。
英文:
I have a query:
SELECT …
FROM …
WHERE project = 123
AND (
file = file1 and fieldidx = idx1
OR file = file2 and fieldidx = idx2
OR …)
… I want to create the where clause in a function that has the required array of tuples, where the tuple is a user type (file text, idx smallint).
I don’t know how (I’m trying to avoid using a union).
答案1
得分: 1
你不需要一个函数来做这个。只需检查提供的数组中是否存在该元组：
SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) IN ((file1, idx1), (file2, idx2), …);
或者
SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) = ANY( ARRAY[(file1, idx1), (file2, idx2), …] );
英文:
You don't need a function to do that. Just check whether the tuple is in the provided array:
SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) IN ((file1, idx1), (file2, idx2), …);
or
SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) = ANY( ARRAY[(file1, idx1), (file2, idx2), …] );
答案2
得分: 0
创建一个 PostgreSQL 函数，该函数接受用户定义的元组数组，并使用 "unnest" 函数来比较值并构建所需的 WHERE 子句。
- 创建用户定义的类型
CREATE TYPE file_idx_type AS (
file text,
idx smallint
);
- 在此之后，创建以下 PostgreSQL 函数：
CREATE OR REPLACE FUNCTION your_function_name(p_data file_idx_type[])
RETURNS TABLE (...) -- 用适当的返回类型替换
AS $$
BEGIN
RETURN QUERY
SELECT ...
FROM ...
WHERE project = 123 AND
(file, fieldidx) IN (SELECT (file, idx) FROM unnest(p_data) AS t(file text, idx smallint));
END;
$$ LANGUAGE plpgsql;
通过这个函数，你可以将 "file_idx_type" 元组的数组作为参数传递，它将用于构建主查询中的 WHERE 子句，而不使用 UNION。
希望这能正常工作。
英文:
Create a PostgreSQL function that takes an array of user-defined tuples and uses the "unnest" function to compare values and construct the desired WHERE clause.
1. Create a user-defined type
CREATE TYPE file_idx_type AS (
file text,
idx smallint
);
2.After it, Create the PostgreSQL function like below:
CREATE OR REPLACE FUNCTION your_function_name(p_data file_idx_type[])
RETURNS TABLE (...) -- Replace with the appropriate return type
AS $$
BEGIN
RETURN QUERY
SELECT ...
FROM ...
WHERE project = 123 AND
(file, fieldidx) IN (SELECT (file, idx) FROM unnest(p_data) AS t(file text, idx smallint));
END;
$$ LANGUAGE plpgsql;
This function, you can pass an array of "file_idx_type"
tuples as an argument,
and it will be used to construct the WHERE clause in your main query without using UNION.
I hope this gonna work..
#Apache-Age #postgrsql #psql
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论