pgAdmin中PostgreSQL搜索函数SELECT语句动态选择返回列实现问询

可以实现动态调整返回列，你可以根据实际需求选择以下两种方案，另外先修正原代码的笔误：原SELECT语句中subject_timetime字段名书写错误，需改为表中实际存在的subject_time。

方案1：保留原函数返回结构，仅按需隐藏列（无需改函数语言，改动最小） #

如果可以接受返回结构仍为原来的7列，不需要展示的列返回空值，直接新增参数控制返回逻辑即可，修改后代码如下：

CREATE OR REPLACE FUNCTION functions.search_function(
    student text[] DEFAULT NULL,
    professors text[] DEFAULT NULL,
    -- 新增参数：是否仅返回精简列（student、professor_name、subject、room_number）
    return_minimal_columns boolean DEFAULT false
)
RETURNS TABLE(
    student text,
    student_id int,
    professor_name text,
    subject text,
    subject_time timestamp without time zone,
    room_number int,
    building text
)
LANGUAGE 'sql'
AS $BODY$
SELECT 
    student,
    CASE WHEN return_minimal_columns THEN NULL ELSE student_id END AS student_id,
    professor_name,
    subject,
    CASE WHEN return_minimal_columns THEN NULL ELSE subject_time END AS subject_time,
    room_number,
    CASE WHEN return_minimal_columns THEN NULL ELSE building END AS building
FROM "school_records_table"
WHERE (LOWER("student") = any($1) OR $1 IS NULL)
AND ((LOWER("professor_name") = any($2)) OR $2 IS NULL)
$BODY$;

调用时如果要返回你举例的4列，只需要传入return_minimal_columns => true即可，冗余的列会返回空值，上层调用直接取需要的4列就行。

方案2：真实动态返回自定义列结构 #

如果需要函数实际返回的列数、列名随需求变动，需要将函数改为PL/pgSQL语言，通过动态SQL实现，修改后代码如下：

CREATE OR REPLACE FUNCTION functions.search_function(
    student text[] DEFAULT NULL,
    professors text[] DEFAULT NULL,
    -- 传入需要返回的列名，多个用英文逗号分隔，比如 'student,professor_name,subject,room_number'
    select_columns text DEFAULT 'student,student_id,professor_name,subject,subject_time,room_number,building'
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $BODY$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT %s FROM "school_records_table"
        WHERE (LOWER("student") = any($1) OR $1 IS NULL)
        AND ((LOWER("professor_name") = any($2)) OR $2 IS NULL)',
        select_columns
    ) USING student, professors;
END
$BODY$;

调用该函数时需要显式指定返回的列结构，比如要返回你举例的4列，调用语句为：

SELECT * FROM functions.search_function(
    student => '{"张三","李四"}',
    select_columns => 'student,professor_name,subject,room_number'
) AS t(student text, professor_name text, subject text, room_number int);

内容的提问来源于stack exchange，提问作者driedbananas