CODEWITHSUNDEEP
postgresql
sanwall
sanwall

Reputation: 63

Error PostgreSQL: RETURN must specify a record or row variable in function returning row

I am trying to run these lines: 

create type _stats_agg_result_type AS (
    count bigint,
    min double precision,
    max double precision,
    mean double precision,
    variance double precision,
    skewness double precision,
    kurtosis double precision
);

create or replace function _stats_agg_finalizer(_stats_agg_accum_type)
returns _stats_agg_result_type AS '
BEGIN
    RETURN row(
        $1.n, 
        $1.min,
        $1.max,
        $1.m1,
        $1.m2 / nullif(($1.n - 1.0), 0), 
        case when $1.m2 = 0 then null else sqrt($1.n) * $1.m3 / nullif(($1.m2 ^ 1.5), 0) end, 
        case when $1.m2 = 0 then null else $1.n * $1.m4 / nullif(($1.m2 * $1.m2) - 3.0, 0) end
    );
END;
'
language plpgsql;

Unfortunately I get following error (w.r.t. the _stats_agg_finalizer function):

RETURN must specify a record or row variable in function returning row

Version I'm running:

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

I am new to PostgreSQL and I have not been able to fix this error. Appreciate any help, thanks!

Upvotes: 0

Views: 244

Answers (1)

jjanes
jjanes

Reputation: 44393

This was enabled in 9.3. I assume it corresponds to this release note:

Allow PL/pgSQL to use RETURN with a composite-type expression (Asif Rehman)

Previously, in a function returning a composite type, RETURN could only reference a variable of that type.

So you should be able to rewrite it this way:

create or replace function _stats_agg_finalizer(_stats_agg_result_type)
returns _stats_agg_result_type AS '
declare f _stats_agg_result_type ;
BEGIN
    f:=row(
        $1.n, 
        $1.min,
        $1.max,
        $1.m1,
        $1.m2 / nullif(($1.n - 1.0), 0), 
        case when $1.m2 = 0 then null else sqrt($1.n) * $1.m3 / nullif(($1.m2 ^ 1.5), 0) end, 
        case when $1.m2 = 0 then null else $1.n * $1.m4 / nullif(($1.m2 * $1.m2) - 3.0, 0) end
    ); 
    return f;
END;
'
language plpgsql;

Note that I changed the input type, since you didn't show use the definition of the original type.

Upvotes: 1

Related Questions