我可以让 postgres plpgsql 函数返回变量列记录吗? [英] Can I have a postgres plpgsql function return variable-column records?

问题描述

我想创建一个 postgres 函数来构建它的列集即时返回；简而言之，它应该包含一个键列表，构建每个键一列，并返回包含任何设置的记录列是.简而言之，代码如下:

I want to create a postgres function that builds the set of columns it returns on-the-fly; in short, it should take in a list of keys, build one column per-key, and return a record consisting of whatever that set of columns was. Briefly, here's the code:

CREATE OR REPLACE FUNCTION reports.get_activities_for_report() RETURNS int[] AS $F$
BEGIN
    RETURN ARRAY(SELECT activity_id FROM public.activity WHERE activity_id NOT IN (1, 2));
END;
$F$
LANGUAGE plpgsql
STABLE;

CREATE OR REPLACE FUNCTION reports.get_amount_of_time_query(format TEXT, _activity_id INTEGER) RETURNS TEXT AS $F$
DECLARE
    _label TEXT;
BEGIN
    SELECT label INTO _label FROM public.activity WHERE activity_id = _activity_id;
    IF _label IS NOT NULL THEN
        IF lower(format) = 'percentage' THEN
            RETURN $$TO_CHAR(100.0 *$$ ||
            $$ (SUM(CASE WHEN activity_id = $$ || _activity_id || $$ THEN EXTRACT(EPOCH FROM ended - started) END) /$$ ||
            $$ SUM(EXTRACT(EPOCH FROM ended - started))),$$ ||
            $$ '990.99 %') AS $$ || quote_ident(_label);
        ELSE
            RETURN $$SUM(CASE WHEN activity_id = $$ || _activity_id || $$ THEN ended - started END)$$ ||
            $$ AS $$ || quote_ident(_label);
        END IF;
    END IF;
END;
$F$
LANGUAGE plpgsql
STABLE;

CREATE OR REPLACE FUNCTION reports.build_activity_query(format TEXT, activities int[]) RETURNS TEXT AS $F$
DECLARE
    _activity_id INT;
    query TEXT;
    _activity_count INT;
BEGIN
    _activity_count := array_upper(activities, 1);
    query := $$SELECT agent_id, portal_user_id, SUM(ended - started) AS total$$;
    FOR i IN 1.._activity_count LOOP
        _activity_id := activities[i];

        query := query || ', ' || reports.get_amount_of_time_query(format, _activity_id);
    END LOOP;
    query := query || $$ FROM public.activity_log_final$$ ||
    $$ LEFT JOIN agent USING (agent_id)$$ ||
    $$ WHERE started::DATE BETWEEN actual_start_date AND actual_end_date$$ ||
    $$ GROUP BY agent_id, portal_user_id$$ ||
    $$ ORDER BY agent_id$$;
    RETURN query;
END;
$F$
LANGUAGE plpgsql
STABLE;

CREATE OR REPLACE FUNCTION reports.get_agent_activity_breakdown(format TEXT, start_date DATE, end_date DATE) RETURNS SETOF RECORD AS $F$
DECLARE
    actual_end_date DATE;
    actual_start_date DATE;
    query TEXT;
    _rec RECORD;
BEGIN
    actual_start_date := COALESCE(start_date, '1970-01-01'::DATE);
    actual_end_date := COALESCE(end_date, now()::DATE);
    query := reports.build_activity_query(format, reports.get_activities_for_report());

    FOR _rec IN EXECUTE query LOOP
        RETURN NEXT _rec;
    END LOOP;
END
$F$
LANGUAGE plpgsql;

这将构建看起来(大致)如下所示的查询:

This builds queries that look (roughly) like this:

SELECT agent_id, 
    portal_user_id, 
    SUM(ended - started) AS total, 
    SUM(CASE WHEN activity_id = 3 THEN ended - started END) AS "Label 1"
    SUM(CASE WHEN activity_id = 4 THEN ended - started END) AS "Label 2"
FROM public.activity_log_final 
    LEFT JOIN agent USING (agent_id) 
WHERE started::DATE BETWEEN actual_start_date AND actual_end_date 
GROUP BY agent_id, portal_user_id 
ORDER BY agent_id

当我尝试调用 get_agent_activity_breakdown() 函数时，出现此错误:

When I try to call the get_agent_activity_breakdown() function, I get this error:

psql:2009-10-22_agent_activity_report_test.sql:179: ERROR:  a column definition list is required for functions returning "record"
CONTEXT:  SQL statement "SELECT * FROM reports.get_agent_activity_breakdown('percentage', NULL, NULL)"
PL/pgSQL function "test_agent_activity" line 92 at SQL statement

当然，诀窍是标记为标签 1"和标签"的列2' 取决于在内容中定义的活动集活动表，在调用函数时我无法预测.如何我可以创建一个函数来访问这些信息吗?

The trick is, of course, that the columns labeled 'Label 1' and 'Label 2' are dependent on the set of activities defined in the contents of the activity table, which I cannot predict when calling the function. How can I create a function to access this information?

推荐答案

Simon 和 Kev 的回答都很好，但我最终做的是将数据库调用拆分为两个查询:

Both Simon's and Kev's answers are good ones, but what I ended up doing was splitting the calls to the database into two queries:

1. 使用我在问题中包含的查询构造函数方法构建查询，并将其返回给应用程序.
2. 直接调用查询，并返回该数据.

这在我的情况下是安全的，因为动态列列表不会频繁更改，所以我不需要担心查询的目标数据在这些调用之间发生变化.否则，我的方法可能不起作用.

This is safe in my case because the dynamic column list is not subject to frequent change, so I don't need to worry about the query's target data changing in between these calls. Otherwise, though, my method might not work.

这篇关于我可以让 postgres plpgsql 函数返回变量列记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！