文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >PostgresSQL上的处理功能

问PostgresSQL上的处理功能
EN

Stack Overflow用户
提问于 2020-11-10 12:24:41
回答 1查看 45关注 0票数 1

我有一个2 select语句，它根据一些标准(一个日期参数)从不同的表中提取数据。用第三个，我得到了结果。

查询1 -提取每个claim_file -参数'2020-10-31'的付款

代码语言：javascript
复制
select
    p."PSN" 
    , p."PNR" 
    , d_p."Index" as claim_file_index
    , d_p."DAMFileNr" as claim_file
    , d_p."DAMDate" as claim_date
    , d_p."DAMAvizDate" as claim_notif_date
    , ROUND(CAST(SUM(CASE WHEN d_pay."EID"='10' THEN d_pay."PAYVal"
                    ELSE d_pay."PAYVal"*exch_p."EXCValue" end) as NUMERIC),2) as plata_LEI
    , pr."Index" as index_pr
    , r."Index" as index_risk
into claims_paid
from "DAM" as d_p
JOIN "DAMPay" as d_pay on d_p."Index"=d_pay."DID"
JOIN "Pols" as p on p."Index" = d_p."PID"
JOIN "PolsRisc" as pr on d_pay."PRID" = pr."Index"
JOIN "Riscs" as r on r."Index" = pr."RID"
JOIN "EXCValues" exch_p ON exch_p."EID"=d_pay."EID"
where (d_pay."EID"='10' or exch_p."AtDate"='2020-10-31') and d_pay."PAYDate" <= '2020-10-31'
group by 
    p."PSN" 
    , p."PNR" 
    , d_p."Index" 
    , d_p."DAMFileNr" 
    , d_p."DAMDate"
    , d_p."DAMAvizDate" 
    , pr."Index"
    , r."Index";

Ouery 2 -按claim_file 参数'2020-10-31'构成的储量

代码语言：javascript
复制
select
    p."PSN" 
    , p."PNR" 
    , d_r."Index" as claim_file_index
    , d_r."DAMFileNr" as claim_file
    , d_r."DAMDate" as claim_date
    , d_r."DAMAvizDate" as claim_notif_date
    , ROUND(CAST(SUM(CASE WHEN d_rez."EID"='10' THEN d_rez."RDAM"
                    ELSE d_rez."RDAM"*exch_r."EXCValue" end) as NUMERIC),2) as rez_const_LEI
    , pr."Index" as index_pr
    , r."Index" as index_risk
into claims_rez_const
from "DAM" as d_r
JOIN "DAMRez" as d_rez on d_r."Index"=d_rez."DID"
JOIN "Pols" as p on p."Index" = d_r."PID"
JOIN "PolsRisc" as pr on d_rez."PRID" = pr."Index"
JOIN "Riscs" as r on r."Index" = pr."RID"
JOIN "EXCValues" exch_r ON exch_r."EID"=d_rez."EID"
where (d_rez."EID"='10' or exch_r."AtDate"='2020-10-31') and d_rez."RDAMDate" <= '2020-10-31'
and (d_r."IsClass" = 'false' or (d_r."IsClass" = 'true' and d_r."ClassDate" >='2020-11-01'))
and (d_r."IsClosed" = 'false' or (d_r."IsClosed" = 'true' and d_r."ClosedDate" >='2020-11-01')) 
group by 
    p."PSN" 
    , p."PNR"
    , d_r."Index" 
    , d_r."DAMFileNr" 
    , d_r."DAMDate"
    , d_r."DAMAvizDate" 
    , pr."Index"
    , r."Index";

查询3 -获取结果(const -)

代码语言：javascript
复制
select 
    r_c."PSN" 
    , r_c."PNR" 
    , r_c."claim_file_index"
    , r_c."claim_file"
    , r_c."claim_date"
    , r_c."claim_notif_date"
    , r_c."rez_const_lei" as rez_const_Lei
    , coalesce(r_p."plata_lei",0) as plata_Lei
    , coalesce(r_c."rez_const_lei" - coalesce(r_p."plata_lei",0),0) as rez_ram_Lei
from claims_rez_const r_c
left join claims_paid r_p on r_c."claim_file_index"=r_p."claim_file_index"
where coalesce(r_c."rez_const_lei" - coalesce(r_p."plata_lei",0),0) != 0 ;

我想要创建一个函数来完成这三个步骤，并根据引入的参数(calc_date日期)返回结果。我试图创建一个这样的函数：

代码语言：javascript
复制
CREATE OR REPLACE FUNCTION claims_rez_ram(calc_date date)
RETURNS TABLE (
                Serie varchar(50)
                , Numar varchar(50)
                , Claim_File_Index integer
                , Claim_File varchar(30)
                , Claim_Date date
                , Claim_Notif_Date date
                , Rez_Const_Lei double precision
                , Plata_Lei double precision
                , Rez_Ram double precision) as $func$ 
declare
begin
    
with claims_paid as (
select
    p."PSN" 
    , p."PNR" 
    , d_p."Index" as claim_file_index
    , d_p."DAMFileNr" as claim_file
    , d_p."DAMDate" as claim_date
    , d_p."DAMAvizDate" as claim_notif_date
    , ROUND(CAST(SUM(CASE WHEN d_pay."EID"='10' THEN d_pay."PAYVal"
                    ELSE d_pay."PAYVal"*exch_p."EXCValue" end) as NUMERIC),2) as plata_LEI
    , pr."Index" as index_pr
    , r."Index" as index_risk
from "DAM" as d_p
JOIN "DAMPay" as d_pay on d_p."Index"=d_pay."DID"
JOIN "Pols" as p on p."Index" = d_p."PID"
JOIN "PolsRisc" as pr on d_pay."PRID" = pr."Index"
JOIN "Riscs" as r on r."Index" = pr."RID"
JOIN "EXCValues" exch_p ON exch_p."EID"=d_pay."EID"
where (d_pay."EID"='10' or exch_p."AtDate"=calc_date) and d_pay."PAYDate" <= calc_date
group by 
    p."PSN" 
    , p."PNR" 
    , d_p."Index" 
    , d_p."DAMFileNr" 
    , d_p."DAMDate"
    , d_p."DAMAvizDate" 
    , pr."Index"
    , r."Index"
 ),
 claims_rez_const as (
 select
    p."PSN" 
    , p."PNR" 
    , d_r."Index" as claim_file_index
    , d_r."DAMFileNr" as claim_file
    , d_r."DAMDate" as claim_date
    , d_r."DAMAvizDate" as claim_notif_date
    , ROUND(CAST(SUM(CASE WHEN d_rez."EID"='10' THEN d_rez."RDAM"
                    ELSE d_rez."RDAM"*exch_r."EXCValue" end) as NUMERIC),2) as rez_const_LEI
    , pr."Index" as index_pr
    , r."Index" as index_risk

from "DAM" as d_r
JOIN "DAMRez" as d_rez on d_r."Index"=d_rez."DID"
JOIN "Pols" as p on p."Index" = d_r."PID"
JOIN "PolsRisc" as pr on d_rez."PRID" = pr."Index"
JOIN "Riscs" as r on r."Index" = pr."RID"
JOIN "EXCValues" exch_r ON exch_r."EID"=d_rez."EID"
where (d_rez."EID"='10' or exch_r."AtDate"=calc_date) and d_rez."RDAMDate" <= calc_date
and (d_r."IsClass" = 'false' or (d_r."IsClass" = 'true' and d_r."ClassDate" >=(calc_date + interval '1 day')))
and (d_r."IsClosed" = 'false' or (d_r."IsClosed" = 'true' and d_r."ClosedDate" >=(calc_date + interval '1 day'))) 
group by 
    p."PSN" 
    , p."PNR"
    , d_r."Index" 
    , d_r."DAMFileNr" 
    , d_r."DAMDate"
    , d_r."DAMAvizDate" 
    , pr."Index"
    , r."Index"
)

 select 
    r_c."PSN" 
    , r_c."PNR" 
    , r_c."claim_file_index"
    , r_c."claim_file"
    , r_c."claim_date"
    , r_c."claim_notif_date"
    , r_c."rez_const_lei" as rez_const_Lei
    , coalesce(r_p."plata_lei",0) as plata_Lei
    , coalesce(r_c."rez_const_lei" - coalesce(r_p."plata_lei",0),0) as rez_ram_Lei
from claims_rez_const r_c
left join claims_paid r_p on r_c."claim_file_index"=r_p."claim_file_index"
where coalesce(r_c."rez_const_lei" - coalesce(r_p."plata_lei",0),0) != 0 ;

end;

$func$  LANGUAGE plpgsql

当我使用以下方法运行此函数时：

代码语言：javascript
复制
select * from claims_rez_ram('2020-10-31')

我有个错误：

SQL 42601: ERROR: query没有结果数据提示的目标:如果要放弃选择的结果，请使用“执行”。其中: PL/pgSQL函数claims_rez_ram(日期)第5行在SQL语句中

谢谢。

sql
postgresql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-11-10 18:40:41

在Postgresql函数中，无论何时返回类型为table，在select查询之前，函数体中必须有一个return query语句。因此，在函数体中在return query之后添加begin，您的错误将得到解决。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/64768900

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档