Selecting strict, immutable text for a composite type.
Trying to find specific doc's. Lots of people asking similar quesitons
told to work around it different ways. Is there any specific doc on
how to create a default operator class for a given composite type
with any examples of minimal working code to access the pieces and
convert them to text?
I have produced quite a variety of botched syntax on this trying to
see which shit sticks to the wall... just one working example would
be most apprecated.
As an example: Say I wanted to use domains for latitude and longitude
that enforce +/- 180 and +/- 90 and let's say for the moment that
float is an approprite base type (though it might be real or numeric,
that is a seprate question). I can combine the domains into a single
type with a lat and lng elemnt.
So far so good.
Q: What is the syntax for a strict, immutable function in
SQL that returns text suitable for use with either
creating a "select *" view or a GIST index?
I believe my mistake is somewhere in the number and placement
of paren's but I have tried quite a few combinations based on
various postings without success. Rather than detail them I'm
just asking for a reference to one working example for any
roughly similar composite type.
thanks
Example:
/*
* this much works.
*/
create domain longitude_d
as
float
not null
check
(
VALUE >= -180.0::float
and
VALUE <= +180.0::float
);
create domain latitude_d
as
float
not null
check
(
value >= -90.0::float
and
value <= +90.0::float
);
create type lat_lng_t
as
(
lng longitude_d,
lat latitude_d
);
/*
* someting about this does not work.
* the signiture seems reasonable,
* as does the returns ... as.
*
* Q: What is the correct syntax for
* select ... ?
*/
create or replace function
lat_lng_text
(
lat_lng_t
)
returns text language sql strict immutable as
$$
select ($1).lng::text || '-' || ($1).lat::text
$$
--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark@wrkhors.com +1 888 359 3508
Steven Lembark <lembark@wrkhors.com> writes:
Q: What is the syntax for a strict, immutable function in
SQL that returns text suitable for use with either
creating a "select *" view or a GIST index?
Your example works just fine for me ...
regression=# select lat_lng_text('(42,54.5)');
lat_lng_text
--------------
42-54.5
(1 row)
Maybe you should show a more concrete example of what's not working.
Side comment: I think you need to rethink that text representation,
because it'll be at best ugly with a negative longitude. Is there a
reason not to just use the default record representation (with parens
and a comma)?
regards, tom lane
[ please keep the list cc'd ]
Steven Lembark <lembark@wrkhors.com> writes:
On Thu, 10 May 2018 11:52:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:Maybe you should show a more concrete example of what's not working.
The problem is with gists telling me that they cannot index
the type. This works for enums, just not the composite type.
Oh, well, they can't. There's no GiST opclass covering arbitrary
composite types. This doesn't seem very surprising to me given
the lack of operators that such an opclass might accelerate.
What are you expecting an index on such a column to do for you?
If you just want a uniqueness constraint, plain btree can handle it.
regards, tom lane
Import Notes
Reply to msg id not found: 20180510132910.2fec9600@wrkhors.com
On Thu, 10 May 2018 14:41:26 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
[ please keep the list cc'd ]
Steven Lembark <lembark@wrkhors.com> writes:
On Thu, 10 May 2018 11:52:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:Maybe you should show a more concrete example of what's not
working.The problem is with gists telling me that they cannot index
the type. This works for enums, just not the composite type.Oh, well, they can't. There's no GiST opclass covering arbitrary
composite types. This doesn't seem very surprising to me given
the lack of operators that such an opclass might accelerate.
But I thought that they could include functions of composite
types that were indexable (e.g., text)?
e.g., enums.
What are you expecting an index on such a column to do for you?
If you just want a uniqueness constraint, plain btree can handle it.
The composite participates in an exclusion constraint:
location
lat_lng_t
not null
,
effective
tstzrange
not null
defualt tstzrange( now(), 'infinity', '(]' )
,
exclude using gist
(
location using =,
effective using &&
)
i.e., the time series can have only one effective set of
data for any one period.
So far as I knew it was possible to have a function on the
type that produced an indexable type (e.g., text). This worked
for the enums, I thought it would work for a composite: produce
a text value that is indexable.
Or is the declaration of the exclusion with a function rather
than the column? Or a function rather than '='? Or a separate
declaration that describes comparing the composite type that
allows the gist to work?
--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark@wrkhors.com +1 888 359 3508
Steven Lembark <lembark@wrkhors.com> writes:
On Thu, 10 May 2018 14:41:26 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:Steven Lembark <lembark@wrkhors.com> writes:
The problem is with gists telling me that they cannot index
the type. This works for enums, just not the composite type.
Oh, well, they can't. There's no GiST opclass covering arbitrary
composite types. This doesn't seem very surprising to me given
the lack of operators that such an opclass might accelerate.
But I thought that they could include functions of composite
types that were indexable (e.g., text)?
Yeah, but that's not what you did.
I think you could make that work with
exclude using gist (
lat_lng_text(location) with =,
effective with &&
)
but it's not going to apply the function without you telling it to.
regards, tom lane
On Thu, May 10, 2018 at 2:16 PM, Steven Lembark <lembark@wrkhors.com> wrote:
exclude using gist
(
location using =,
effective using &&
)
Have you installed the btree-gist extension?
https://www.postgresql.org/docs/10/static/btree-gist.html
Not sure about composites but if you do store a text representation it will
work.
David J.
On Thu, 10 May 2018 17:38:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yeah, but that's not what you did.
I think you could make that work with
exclude using gist (
lat_lng_text(location) with =,
effective with &&
)but it's not going to apply the function without you telling it to.
Q: Why does it work with enums?
e.g., If I create a type foo_t as enum (...) and install the function
foo_text on foo_t the gist works. Why would the presence of a text
verison of the composite not get used the same way?
If the function works I'm happy, I just don't see the reasoning
behind having the enum's supported automatically and the composite
not handled.
enjoi
--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark@wrkhors.com +1 888 359 3508
On Thursday, May 10, 2018, Steven Lembark <lembark@wrkhors.com> wrote:
Q: Why does it work with enums?
Guessing because enums are not composites; they are scalar and most scalar
types in core seem to be covered by the extension.
e.g., If I create a type foo_t as enum (...) and install the function
foo_text on foo_t the gist works.
You should ifnd it works even without the existence of the foo_text
function.
David J.