我在PostgreSQL 9.3.10中编写一个PL / pgSQL函数，以返回谁参加了下表中的某些课程/会议：
Attendance +-------+---------+---------+ | Class | Section | Name | +-------+---------+---------+ | 1 | 1 | Amy | | 1 | 1 | Bill | | 1 | 2 | Charlie | | 1 | 2 | Dan | | 2 | 1 | Emily | | 2 | 1 | Fred | | 2 | 2 | George | +-------+---------+---------+
我想做的是，给定一组类/部分ID对（int[][]），返回所有在那些类/部分中的人。例如my_func(ARRAY[[1,1],[2,2]])返回：
int[][]
my_func(ARRAY[[1,1],[2,2]])
+-------+---------+---------+ | Class | Section | Name | +-------+---------+---------+ | 1 | 1 | Amy | | 1 | 1 | Bill | | 2 | 2 | George | +-------+---------+---------+
如果我事先知道这对，那就很简单了：
SELECT * FROM attendance WHERE ((class = 1 AND section = 1) OR (class = 2 AND section = 2));
而是，对将成为函数的参数。
现在，我能想到的唯一方法是让函数本质上通过在查询末尾附加一堆WHERE子句然后调用来构建SQL查询字符串EXECUTE。有没有更好的方法来得到我的结果？
WHERE
EXECUTE
编辑：我实现了建议@Erwin的建议，目前能够得到我想要的结果。不幸的是，它看起来好像很慢。这是我正在运行的功能：
CREATE OR REPLACE FUNCTION public.get_attendance(int[]) RETURNS TABLE( class_c int, section_c int ) AS $BODY$ BEGIN RETURN QUERY SELECT class, section FROM generate_subscripts($1, 1) as i INNER JOIN attendance ON attendance.class = $1[i][1] AND attendance.section = $1[i][2]; END; $BODY$ LANGUAGE plpgsql VOLATILE;
像这样查询它：
SELECT * FROM get_attendance(ARRAY[[1,15],[2,15],[3,8]]);
我得到以下EXPLAIN ANALYZE输出
Merge Join (cost=60.26..50139.72 rows=30840 width=8) (actual time=44.174..142.100 rows=25290 loops=1) Merge Cond: ((attendance.class = (('{{1,15},{2,15},{3,8}}'::integer[])[i.i][1])) AND (attendance.section = (('{{1,15},{2,15},{3,8}}'::integer[])[i.i][2]))) -> Index Only Scan using class_section_idx on attendance (cost=0.43..43372.25 rows=1233588 width=8) (actual time=0.009..86.625 rows=1145046 loops=1) Heap Fetches: 0 -> Sort (cost=59.83..62.33 rows=1000 width=4) (actual time=0.010..0.757 rows=10031 loops=1) Sort Key: (('{{1,15},{2,15},{3,8}}'::integer[])[i.i][1]), (('{{1,15},{2,15},{3,8}}'::integer[])[i.i][2]) Sort Method: quicksort Memory: 25kB -> Function Scan on generate_subscripts i (cost=0.00..10.00 rows=1000 width=4) (actual time=0.006..0.007 rows=3 loops=1)
问题在于查询正在扫描考勤表中的所有考勤，而没有过滤它们直到加入。有没有什么办法解决这一问题？
您可以使用简单的SQL函数来实现。主要功能是功能 generate_subscripts() ：
generate_subscripts()
CREATE OR REPLACE FUNCTION f_attendance(_arr2d int[]) RETURNS SETOF attendance AS $func$ **SELECT a.* FROM generate_subscripts($1, 1) i JOIN attendance a ON a.class = $1[i][1] AND a.section = $1[i][2]** $func$ LANGUAGE ROWS 10 sql STABLE;
称呼：
SELECT * FROM f_attendance(ARRAY[[1,1],[2,2]]);
或与数组 文字 相同-在某些情况下，尤其是在准备好的语句中，这更方便：
SELECT * FROM f_attendance('{{1,1},{2,2}}');
该函数 始终 期望使用2D数组。即使您传递一对，也要嵌套：
SELECT * FROM f_attendance('{{1,1}}');
VOLATILE
STABLE
由于这种快照行为，SELECT可以安全地标记仅包含命令的功能STABLE。
SELECT
有关的：
* [如何将参数传递给日期函数](https://stackoverflow.com/questions/30758691/how-to-pass-a-parameter-into-a-date-function/30763347#30763347)
LANGUAGE plpgsql
sql
STABLE和IMMUTABLE函数使用在调用查询开始时建立的快照，而VOLATILE函数在执行的每个查询开始时获取一个新的快照。
IMMUTABLE
您的EXPLAIN输出显示“ 仅索引扫描” ，而不是您在注释中怀疑的顺序扫描。
EXPLAIN
您的EXPLAIN输出中还有一个排序步骤，与您显示的代码不匹配。您确定复制了正确的EXPLAIN输出吗？无论如何，您是如何获得它的？PL / pgSQL函数是的黑匣子EXPLAIN。你用了auto_explain吗？细节：
auto_explain
ROWS 10
