我可以使用postgres plpgsql函数返回变量列记录吗？

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:

我想创建一个postgres函数来构建它即时返回的列集;简而言之，它应该包含一个键列表，每个键构建一个列，并返回一个由列组成的记录组成的记录。简而言之，这是代码：

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

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

当我尝试调用get_agent_activity_breakdown（）函数时，我收到此错误：

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

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?

当然，诀窍是标记为“标签1”和“标签2”的列依赖于活动表内容中定义的活动集，这在调用函数时无法预测。如何创建访问此信息的功能？

4 个解决方案

#1

If you really want to create such table dynamically, maybe just create a temporary table within the function so it can have any columns you want. Let the function insert all rows into the table instead of returning them. The function can return only the name of the table or you can just have one exact table name that you know. After running that function you can just select data from the table. The function should also check if the temporary table exists so it should delete or truncate it.

如果你真的想动态创建这样的表，可能只是在函数中创建一个临时表，这样它就可以有你想要的任何列。让函数将所有行插入表中而不是返回它们。该函数只能返回表的名称，或者您可以只知道一个确切的表名。运行该功能后，您只需从表中选择数据即可。该函数还应检查临时表是否存在，以便删除或截断它。

#2

Simon's answer might be better overall in the end, I'm just telling you how to do it without changing what you've got.

西蒙的答案总体上可能会更好，我只是告诉你如何在不改变你所拥有的情况下做到这一点。

From the docs:

来自文档：

from_item can be one of:

from_item可以是以下之一：

...
function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )

In other words, later it says:

换句话说，后来它说：

If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ] ). The column definition list must match the actual number and types of columns returned by the function.

如果函数已被定义为返回记录数据类型，则必须存在别名或关键字AS，然后是表单中的列定义列表（column_name data_type [，...]）。列定义列表必须与函数返回的实际数量和列类型相匹配。

I think the alias thing is only an option if you've predefined a type somewhere (like if you're mimicing the output of a predefined table, or have actually used CREATE TYPE...don't quote me on that, though.)

我认为别名只是一个选项，如果你已经在某个地方预定了一个类型（比如你是在模仿预定义表的输出，或者实际上已经使用过CREATE TYPE ......但是不要引用我的话。）

So, I think you would need something like:

所以，我认为你需要这样的东西：

SELECT *
  FROM reports.get_agent_activity_breakdown('percentage', NULL, NULL)
    AS (agent_id integer, portal_user_id integer, total something, ...)

The problem for you lies in the .... You'll need to know before you execute the query the names and types of all the columns--so you'll end up selecting on public.activity twice.

你遇到的问题在于....在执行查询之前你需要知道所有列的名称和类型 - 所以你最终会选择public.activity两次。

#3

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:

Simon和Kev的答案都很好，但我最终做的是将对数据库的调用分成两个查询：

Build the query using the query constructor methods I included in the question, returning that to the application.
使用我在问题中包含的查询构造函数方法构建查询，并将其返回给应用程序。
Call the query directly, and return that data.
直接调用查询，并返回该数据。

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.

这在我的情况下是安全的，因为动态列列表不会经常更改，因此我不需要担心查询的目标数据在这些调用之间发生变化。否则，我的方法可能无效。

#4

you cannot change number of output columns, but you can to use refcursor, and you can return opened cursor.

你不能改变输出列的数量，但你可以使用refcursor，并且可以返回打开的游标。

more on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

更多信息，请访问http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

#1

#2