Selecting strict, immutable text for a composite type.

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

Responses

Browse pgsql-general by date

  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.