Search code examples
sqlpostgresqlfunctionreturn-valueaggregate-filter

Returning aggregates over CASE expressions from a function

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?

Solution

  • 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: