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

Question

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!

Probably unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Feb 24, 2020 at 9:27
9.2 is so old that I don't have a running installation. The code runs on v12. How about playing around with it, like casting the ROW() expression to _stats_agg_result_type or declaring a record variable, assigning to it and returning that? — Laurenz Albe
– Laurenz Albe, Commented Feb 24, 2020 at 9:37
Thanks. Would you mind sharing some code snippets please? I mainly interact with PostgreSQL through Python using basic queries - my knowledge is therefore rather limited. — sanwall
– sanwall, Commented Feb 24, 2020 at 9:41
@sanwall: you might want to show the "powers to be" (who refuse the upgrade) this link: why-upgrade.depesz.com/show?from=9.2.24&to=12.2 and ask them why security isn't important to them ;) (SCNR) — user330315
– user330315, Commented Feb 24, 2020 at 10:10

jjanes · Accepted Answer · 2020-02-24 19:51:40Z

1

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.

answered Feb 24, 2020 at 19:51

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sanwall Over a year ago

Many thanks! Unfortunately this raises another error. (If this is related to your last remark, I'm sorry!) I am trying to get following function to work: link. The error I get is following function _stats_agg_finalizer(_stats_agg_accum_type) does not exist. Any suggestions why? How can I correct this? Thanks!

jjanes Over a year ago

Just change the input argument type of the function back to _stats_agg_accum_type. You will probably have to delete it first, as you can't use OR REPLACE to change the signature.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related