postgresql function: Final statement returns bigint instead of integer

Question

I am using PostgreSQL ver 8.4 and have written the following custom grouping function:

CREATE OR REPLACE FUNCTION custom_group(integer)
RETURNS TABLE (
grp INTEGER,
entry_date DATE,
col1 REAL,
col2 REAL,
col3 REAL,
col4 REAL,
col5 INTEGER,
col6 INTEGER) AS
$BODY$
    SELECT ceil(rank() OVER (ORDER BY entry_date) / $1)::int as grp
          ,entry_date, col1, col2, col3, col4, col5, col6
    FROM   table_foo 
    ORDER  BY 1;
$BODY$ LANGUAGE SQL;

When I try to import the function into using psql, I get the following error:

Final statement returns bigint instead of integer

I don't understand the error message, especially since I am expecting a RECORD (well table) type back.

What is causing the error, and how do I fix this?

[[Edit]]

I changed grp data type to BIGINT as suggested in the comments, however I got the same error. Details:

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns integer instead of bigint at column 1.
CONTEXT:  SQL function "custom_group"

My guess is that ceil() returns a bigint and you defined grp as int. Try changing grp to bigint in return table definition. — juergen d
– juergen d, Commented May 12, 2012 at 12:41
@juergend: I tried that, the error still remains though. Please see my edited question. — Homunculus Reticulli
– Homunculus Reticulli, Commented May 12, 2012 at 12:51
Can you modify this example to reproduce the error? (post back the new link) — juergen d
– juergen d, Commented May 12, 2012 at 13:00
ceil() does not return bigint anyway, see the result of \df ceil command in psql. Wasn't the initial error message Final statement returns bigint instead of integer followed by a column number? If it was, please copy-paste the entire message. There's no reason to assume that the problem was at column 1. — Daniel Vérité
– Daniel Vérité, Commented May 12, 2012 at 13:10
@DanielVérité: The full message is Final statement returns integer instead of bigint at column 1 BTW, this was AFTER I had changed grp data type to BIGINT. — Homunculus Reticulli
– Homunculus Reticulli, Commented May 12, 2012 at 13:13

kgrittn · Accepted Answer · 2012-05-12 13:49:30Z

1

After the edit you are now getting a different error. You should get this new error because you explicitly cast column 1 to int and declared it to be bigint. My guess is that your original problem was col5 or col6 in table_foo is a bigint.

answered May 12, 2012 at 13:49

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

postgresql function: Final statement returns bigint instead of integer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related