Returning aggregates over CASE expressions from a function

Question

I want to return four conditional aggregates from a function. I tried using CASE expressions. My 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

When I use:

select * from get_grade(22);

it doesn't work as expected.

Also tried:

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;

Then I execute:

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

Error:

ERROR: function return row and query-specified return row do not match

How to get this right?

Erwin Brandstetter · Accepted Answer · 2022-11-02T04:47:29.113

Try this:

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$;

You can write a simple query like this with LANGUAGE plpgsql. You can also just use LANGUAGE sql. Either has pros and cons. Showing an SQL function. Don't quote the language name. The manual:

Enclosing the name in single quotes is deprecated and requires matching case.

Column aliases inside the function body are not visible outside. They only serve as documentation in this case. Use named OUT parameters or they get default names. RETURNS TABLE makes it a set-returning function and requires columns names in any case.

OUT parameters are visible inside every DML SQL statement inside the function body. Table-qualify column names that would otherwise conflict with OUT parameter names. Don't use aliases that would conflict. I commented out your aliases to be clear (though modern versions of Postgres wouldn't conflict there).

Data types of returned columns have to match the declaration in the header exactly. sum() or count() return bigint. You have to cast to integer explicitly to match the return type.

Unquoted upper case identifiers are folded to lower case in Postgres anyway, and only serve to confuse.

I used shorter (and a bit faster) expressions for your conditional count. In Postgres 9.4 or later use an aggregate FILTER instead. See:

score 0 · Answer 2 · edited Nov 20 '13 at 05:03

I think you want to use RETURN QUERY and a setof record return type:

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;

You could do that as a plain SQL function as well:

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;

BTW, is t.pirority supposed to be t.priority or is that the real column name?

score 0 · Answer 3 · edited Oct 31 '11 at 09:20

I am not sure If I undstand what you said very well. The follwing is just a test.

--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)

Returning aggregates over CASE expressions from a function

3 Answers3