当前位置: 首页 > 面试题库 >

我可以有一个postgres plpgsql函数返回变量列记录吗?

微生城
2023-03-14
问题内容

我想创建一个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;

这将生成看起来(大致)如下的查询:

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()函数时,出现以下错误:

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”的列取决于活动表内容中定义的活动集,我在调用该函数时无法预测这些活动。如何创建访问此信息的功能?


问题答案:

Simon和Kev的​​答案都是很好的答案,但是我最后要做的是将对数据库的调用分为两个查询:

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

就我而言,这是安全的,因为动态列列表不会频繁更改,因此我不必担心查询的目标数据在这些调用之间会发生变化。但是,否则,我的方法可能不起作用。



 类似资料:
  • 问题内容: 我想知道是否存在一种实现类似于map getter的功能的方法:它返回返回值作为第一个参数,(可选地分配)第二个值作为第二个参数。因此,我需要可以通过以下方式调用的函数: 问题答案: 不,它无法完成,唯一的选择是返回一个指针并检查它是否为nil。

  • 我知道Postgres允许返回预定义的复合数据类型、记录和具有列定义的表。 然而,我没有返回一个定义了列的简单记录,所以我总是使用表,即使我知道这只返回一行。 我的函数定义如下: 如果我尝试以同样的方式返回记录,则返回失败: 这在博士后中不可能吗?我知道我可以只声明一个自定义复合数据类型,但我不想为每个返回记录的函数声明一个类型。

  • 我正在处理一个SQL查询,它应该返回每行具有相同或不同列数的数据 例如,我有以下来自 CTE 的数据,如下所示 结果是 在上表中,每个Acc_Num我都有多条记录,我想以相同的Acc_num对所有记录进行分组,并根据每个acc_num的记录数创建其他列,结果应如下所示 我不确定是否可以为单个数据集返回可变列数。 我可以知道解决这个问题的方法吗?

  • 问题内容: 我想要一个可以将变量/对象名称返回为str的函数,如下所示: 它看起来很傻,但是我需要该函数来构造有关变量的表达式,以供以后在’exec()’上使用。有人可以帮忙写“ get_variable_name”吗? 问题答案: 我现在已经在SO上多次见过关于这种问题的一些变体。答案是 否定的 。 学会随时使用名称和对象之间的关联。 稍后您将为此感谢自己。 在回答“我的代码如何发现对象的名称?

  • 问题内容: 我已经写了这个功能: 哪个有效,并在单列记录类型中返回结果。请注意,可能会返回多于一行。 现在的响应是: 我想获得的结果不是记录,而是多列 有没有一种方法可以从PostgreSQL函数返回多列 问题答案: 我可以通过以下查询看到它:

  • 问题内容: 在基本的Postgres函数教程中,有一个带有如下参数的示例: 然后结果看起来像 但是,假设您要在执行连接所产生的列上执行该函数,并且您无权修改该函数或使用替代函数?例如,使用一些玩具数据: 在单列“ hi_lo”中返回具有2元组值的结果。 将查询括在括号中并尝试从中查询不会更改输出的格式。所以 不会影响结果形状。 以下尝试导致错误“子查询必须仅返回一列” 最后,我也尝试过,但是由于元