postgresql 从函数返回CASE表达式的聚合

uyhoqukh  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(3)|浏览(150)

我想从一个函数返回四个条件聚合。我尝试使用CASE表达式。我的SQL:

CREATE OR REPLACE FUNCTION get_grade(IN integer, out integer,out integer,out      integer,out integer)  AS
$BODY$
begin
    select 
    sum(case when t.pirority = 66 then 1 else 0 end) as I ,
    sum(case when t.pirority = 67 then 1 else 0 end) as II,
    sum(case when t.pirority = 68 then 1 else 0 end) as III,
    sum(case when t.pirority = 225 then 1 else 0 end) as IIII 
    from dt_info t 
    where t.registrant = $1
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE

当我用途:

select * from get_grade(22);

它不能像预期的那样工作。
还试探着:

CREATE OR REPLACE FUNCTION get_grade(IN integer) returns setof record AS
$BODY$
    select
    sum(case when t.pirority = 66 then 1 else 0 end) as I,
    sum(case when t.pirority = 67 then 1 else 0 end) as II,
    sum(case when t.pirority = 68 then 1 else 0 end) as III,
    sum(case when t.pirority = 225 then 1 else 0 end) as IIII
    from dt_info t 
    where t.registrant = $1
$BODY$
LANGUAGE 'sql' VOLATILE;

然后我执行:

select * from get_grade(25) as (v1 integer, v2 integer, v3 integer, v4 integer)

错误:
错误:函数返回行与查询指定的返回行不匹配
如何做好这件事?

aemubtdh

aemubtdh1#

试试看:

CREATE OR REPLACE FUNCTION get_grade(int)
  RETURNS TABLE (i int, ii int, iii int, iiii int)
  LANGUAGE sql AS
$func$
SELECT count(t.priority =  66 OR NULL)::int  -- AS I
     , count(t.priority =  67 OR NULL)::int  -- AS II
     , count(t.priority =  68 OR NULL)::int  -- AS III
     , count(t.priority = 225 OR NULL)::int  -- AS IIII
FROM   dt_info t 
WHERE  t.registrant = $1;
$func$;

你 * 可以 * 用LANGUAGE plpgsql编写一个类似这样的简单查询。你也可以只使用**LANGUAGE sql**。两者都有优缺点。显示一个SQL函数。不要用引号引用语言名称。手册:
不建议使用单引号将名称括起来,并且要求大小写匹配。

函数体内部的列别名在外部不可见。在这种情况下,它们仅用作文档。请使用namedOUT参数,否则它们将获得默认名称。RETURNS TABLE使其成为返回集合的函数,并且在任何情况下都需要列名。

OUT参数在函数体中的每个DML SQL语句中都是可见的。表限定列名,否则会与OUT参数名冲突。不要使用会冲突的别名。为了清楚起见,我注解掉了您的别名(尽管现代版本的Postgres不会在这里冲突)。
返回列的数据类型必须与标头中的声明完全匹配。sum()count()返回bigint。必须**显式转换为integer**以匹配返回类型。

不带引号的大写标识符在Postgres中被折叠成小写,只会造成混淆。

我使用了更短(也更快)的表达式来计算条件计数。在Postgres 9.4或更高版本中,请使用聚合FILTER。请参见:

eqzww0vc

eqzww0vc2#

我不能肯定我是否理解你所说的话。下面只是一个测试。

--create table 
skytf=> create table grade (registrant integer, pirority integer);
CREATE TABLE
skytf=> insert into grade values (1,66);
INSERT 0 1
skytf=> insert into grade values (1,66);
INSERT 0 1
skytf=> insert into grade values (1,67);
INSERT 0 1
skytf=> insert into grade values (1,67);
INSERT 0 1
skytf=> insert into grade values (1,67);
INSERT 0 1
skytf=> insert into grade values (1,68);
INSERT 0 1
skytf=> insert into grade values (1,225);
INSERT 0 1
skytf=> insert into grade values (1,225);
INSERT 0 1
skytf=> insert into grade values (1,225);
INSERT 0 1
skytf=> insert into grade values (1,225);
INSERT 0 1
skytf=> select * from grade;
 registrant | pirority 
------------+----------
          1 |       66
          1 |       66
          1 |       67
          1 |       67
          1 |       67
          1 |       68
          1 |      225
          1 |      225
          1 |      225
          1 |      225

--create function
CREATE OR REPLACE FUNCTION get_grade( in_reg integer ) RETURNS  RECORD AS
$$
DECLARE
    g_user record;
BEGIN
    select 
    sum(case when t.pirority = 66 then 1 else 0 end) as I ,
    sum(case when t.pirority = 67 then 1 else 0 end) as II,
    sum(case when t.pirority = 68 then 1 else 0 end) as III,
    sum(case when t.pirority = 225 then 1 else 0 end) as IIII 
    into g_user
    from grade  t 
    where t.registrant = in_reg;
    return g_user;
END;
$$
LANGUAGE PLPGSQL;          

--execute function
skytf=> select get_grade(1);
 get_grade 
-----------
 (2,3,1,4)
(1 row)
ovfsdjhp

ovfsdjhp3#

我想你应该使用RETURN QUERY和一个setof record返回类型:

CREATE OR REPLACE FUNCTION get_grade(IN integer, out bigint, out bigint, out bigint, out bigint)
returns setof record AS
$BODY$
begin
    return query select 
        sum(case when t.pirority = 66 then 1 else 0 end) as I,
        sum(case when t.pirority = 67 then 1 else 0 end) as II,
        sum(case when t.pirority = 68 then 1 else 0 end) as III,
        sum(case when t.pirority = 225 then 1 else 0 end) as IIII 
        from dt_info t 
        where t.registrant = $1;
end
$BODY$
LANGUAGE plpgsql VOLATILE;

您也可以将其作为一个普通的SQL函数来执行:

CREATE OR REPLACE FUNCTION get_grade(IN integer, out bigint, out bigint, out bigint, out bigint)
returns setof record AS
$BODY$
    select 
        sum(case when t.pirority = 66 then 1 else 0 end) as I,
        sum(case when t.pirority = 67 then 1 else 0 end) as II,
        sum(case when t.pirority = 68 then 1 else 0 end) as III,
        sum(case when t.pirority = 225 then 1 else 0 end) as IIII 
    from dt_info t 
    where t.registrant = $1;
$BODY$
LANGUAGE sql VOLATILE;

顺便说一句,t.pirority应该是t.priority还是真实的的列名?

相关问题