时间戳

首页 > 解决方案 > 将返回的记录类型解析为 PostgreSql 中的多个列

sql - 将返回的记录类型解析为 PostgreSql 中的多个列

问题描述

我有 Postgresql 函数，它返回多个值作为记录。我需要接受记录类型并解析为多列。我在语句内部调用函数，select如下所示：

SELECT bank_id, myfunction(document_id,'DF') FROM timeline_chronicle WHERE document_id = 102;

上面的语句返回如下：

--------------------------------------
bank_id   |   record
----------------------------------------
9006      | (DOCUMENT_IS_ACCEPTED,"2020-07-03 16:37:28","2020-07-03 16:41:58",270)
----------------------------------------------------------------------------------

但我需要以下版本

--------------------------------------
bank_id   |   event_type         | start_time          | end_time            | difference   |
-----------------------------------------------------------------------
9006      | DOCUMENT_IS_ACCEPTED | 2020-07-03 16:37:28 | 2020-07-03 16:41:58 |  270
----------------------------------------

此外，我尝试了以下版本，但无论如何都无法正常工作：

SELECT bank_id, myfunction(document_id,'DF') as (event_type text,start_time TIMESTAMP,end_time TIMESTAMP,difference integer) FROM timeline_chronicle WHERE document_id = 102;

Postgre 版本是：9.6.18。我的 Postgre 函数如下：

CREATE OR REPLACE FUNCTION myfunction(doc_id integer,oper_name2 text) 
RETURNS RECORD AS $$
DECLARE

start_tim               timestamp(0) without time zone;
end_tim                 timestamp(0) without time zone;
event_typo              text;
tim_diff                integer;
ret                     RECORD;

BEGIN
    start_tim := (SELECT to_timestamp(MIN(created_at))::timestamp FROM table WHERE document_id = 62);
    SELECT event_type,created_at INTO event_typo,end_tim FROM (
        SELECT document_id,oper_name,event_type,to_timestamp(created_at)::timestamp as created_at, 
            case when event_type in ('DOCUMENT_IS_DENY','DOCUMENT_IS_ACCEPTED')  then 2 else 1 end as status
        FROM table  
        WHERE document_id = 62 order by created_at asc
        ) s WHERE status = 2 order by created_at asc limit 1;

    tim_diff:= (select extract(epoch from (end_tim-start_tim)));  
    SELECT event_typo,start_tim,end_tim,tim_diff INTO ret;
    RETURN ret;
   
END;
$$ LANGUAGE plpgsql;

标签： sqlpostgresql

解决方案

您需要输入记录才能查看列名。为此，您需要创建一个正确的返回类型：

create type function_result as 
( 
  event_type text,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  difference bigint
);

然后在你的函数中使用：

return (event_typo,start_tim,end_tim,tim_diff)::function_result;

（不需要ret变量）

然后，您可以使用以下方法将查询中的记录扩展为列：

SELECT bank_id, (myfunction(document_id,'DF')).* 
FROM timeline_chronicle 
WHERE document_id = 102;

注意：

tim_diff := (select extract(epoch from (end_tim-start_tim)));

可以简化为：

tim_diff := extract(epoch from (end_tim-start_tim));

推荐阅读