3

Suppose I have a table defined as follows:

CREATE TABLE gtest (name varchar, geom geometry);

To insert, I am able to simply do this:

INSERT INTO gtest
VALUES (
    'Polygon',
    'SRID=4326;POLYGON((0 0,1 0,1 1,0 1,0 0))'
);

I don't have to wrap the WKT string in the function ST_GeomFromText() because PostGIS has an implicit cast that does so. This is nicely explained by @JGH here

By using the Postgres command \dC, the defined casts can be listed, including:

                                     List of casts
     Source type         |         Target type         |      Function      |   Implicit?
-------------------------+-----------------------------+--------------------+---------------
text                     | geometry                    | geometry           | yes
geometry                 | text                        | text               | yes

I'd like to make it so that I can simply do a SELECT * FROM gtest and have the results of the geometry column be implicitly converted into WKT. Currently, it will just display them as WKB.

First, I tried creating a new cast as follows:

CREATE CAST (geometry AS text) WITH FUNCTION st_astext(geometry) AS IMPLICIT;

This returned an error, since a cast from geometry to text already exists (as seen in the table).

I then tried ALTER EXTENSION postgis DROP CAST (geometry as text); and then DROP CAST (geometry as text);, and was then able to create the new cast:

                                     List of casts
     Source type         |         Target type         |      Function      |   Implicit?
-------------------------+-----------------------------+--------------------+---------------
geometry                 | text                        | st_astext          | yes

This still didn't work however, as when I do a select, I still get the results in WKB.

Firstly, is this possible? Am I just doing something wrong? Secondly, would any geometry functions break by adding this implicit cast?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
robbieperry22
  • 1,753
  • 1
  • 18
  • 49

1 Answers1

1

When a datum is converted to a string or sent to the client in text mode, the type output function is called. No casts are applied in that case.

This function is written in C, and you'd have to hack PostGIS in order to change it. Moreover, you'd also have to change the type input function to accept the text format.

I hope you did that experiment in a test machine, because that ALTER EXTENSION has mutilated the PostGIS extension.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263