From: | Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | User-defined function with anyrange[] |
Date: | 2018-07-05 23:44:39 |
Message-ID: | CA+renyVOjb4xQZGjdCnA54-1nzVSY+47-h4nkM-EP5J=1z=b9w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I want to make an aggregate function range_agg(anyrange) that returns
anyrange[]. But when I try to define it, Postgres tells me it doesn't
know what an anyrange[] is. I get this error:
ERROR: type anyrange[] does not exist
I also tried taking an anyrange and returning an anyarray, which does
let me define the function, but upon calling it I get an error. For
example:
paul=# CREATE OR REPLACE FUNCTION range_agg4(anyrange)
RETURNS anyarray
AS $$
BEGIN
RETURN ARRAY[$1];
END;
$$
LANGUAGE plpgsql;
paul=# select range_agg4(daterange('2016-05-04', '2016-07-01'));
ERROR: invalid input syntax for type date: "[2016-05-04,2016-07-01)"
CONTEXT: PL/pgSQL function range_agg4(anyrange) while casting
return value to function's return type
So I guess it thinks that daterange in means date[] out (not daterange[] out).
The best I can think of is to define range_agg(anyelement) that
returns anyarray. That actually works how I hope:
paul=# CREATE OR REPLACE FUNCTION range_agg3(anyelement)
RETURNS anyarray
AS $$
BEGIN
RETURN ARRAY[$1];
END;
$$
LANGUAGE plpgsql;
paul=# select range_agg3(daterange('2016-05-04', '2016-07-01'));
range_agg3
-----------------------------
{"[2016-05-04,2016-07-01)"}
But of course that is not as restricted as I'd like. Are there any
better options?
Thanks,
Paul
From | Date | Subject | |
---|---|---|---|
Next Message | Brahmam Eswar | 2018-07-06 07:49:44 | How to remove elements from array . |
Previous Message | David G. Johnston | 2018-07-05 21:55:48 | Re: FK v.s unique indexes |