creating input and output functions for postgresql custom type using postgis types

Question

I have created a custom type in my postgresql database with this composite structure:

CREATE TYPE circle_geo AS (
       box geography(POLYGON,4326), 
       center geography(POINT,4326), 
       radius float8
);

so it is using some postgis types (POINT and POLYGON). Now I would like to be able to create an INPUT and OUTPUT function for this custom type so that I can pass a simple string representation ( that would be (x,y),c like for the postgresql native geometry circle type) to create an instance of it

my input pseudo-function would look like this:

input_function(cstring) where cstring is of the form (x,y),r:

radius = r;
center = ST_MakePoint(x,y);
pdist = sqrt(2*radius*radius);
box = ST_MakePolygon(ST_MakeLine(ARRAY[
                ST_Project(center, pdist, radians(45.0)),
                ST_Project(center, pdist, radians(135.0)),
                ST_Project(center, pdist, radians(-135.0)),
                ST_Project(center, pdist, radians(-45.0)),
                ST_Project(center, pdist, radians(45.0))
    ]))

and my output_function should simply return a cstring composed of

(center.x_lon,center.y_lat),radius

My problem is that I don't know how to writte these input and output functions. Can they be written in PLPgSQL or should they necessary be written in C? And if so, how can I made calls to postgis functions (like ST_MakePoint, ST_MakePolygon, ST_MakeLine, ST_Project...) inside them? Any help would be greatly appreciated ;)

Laurenz Albe · Accepted Answer · 2017-06-01 08:52:55Z

2

The type input and output functions have to be written in C, because they need to accept or return the type cstring, which is a plain C string that cannot be handled in SQL.

See the documentation which shows an example.

You can call PostGIS functions from your function either via the Server Programming Interface (SPI), which allows you to execute an SQL statement, or faster by directly calling C functions exported from PostGIS (you have to link with PostGIS in that case).

answered Jun 1, 2017 at 8:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

creating input and output functions for postgresql custom type using postgis types

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