From: | Steven Lembark <lembark(at)wrkhors(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Cc: | lembark(at)wrkhors(dot)com |
Subject: | Selecting strict, immutable text for a composite type. |
Date: | 2018-05-10 15:30:41 |
Message-ID: | 20180510103041.472408bb@wrkhors.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)wrkhors(dot)com +1 888 359 3508
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2018-05-10 15:33:56 | Re: Domain based on TIMEZONE WITH TIME ZONE |
Previous Message | Steven Lembark | 2018-05-10 14:46:40 | Re: Enhancement to psql command, feedback. |