CODEWITHSUNDEEP
postgresqlpostgis
hgl
hgl

Reputation: 2214

How to specify PostGIS geography value in a composite type literal?

I have a custom composite type:

CREATE TYPE place AS (
    name text,
    location geography(point, 4326)
);

I want to create a value of that type using a literal:

SELECT $$("name", "ST_GeogFromText('POINT(121.560800 29.901200)')")$$::place;

This fails with:

HINT:  "ST" <-- parse error at position 2 within geometry
ERROR:  parse error - invalid geometry

But this executes just fine:

SELECT ST_GeogFromText('POINT(121.560800 29.901200)');

I wonder what's the correct way to specify PostGIS geography value in a composite type literal?

Upvotes: 0

Views: 94

Answers (1)

Darafei Praliaskouski
Darafei Praliaskouski

Reputation: 977

You are trying to push a function call ST_GeogFromText into a text string. This will not be allowed, as it creates a possibility for SQL injection.

In second call you need ST_GeogFromText to mark the type of input. For a composite type, you did that already in type definition, so you can skip that part:

[local] gis@gis=# SELECT $$("name", "POINT(121.560800 29.901200)")$$::place;
┌───────────────────────────────────────────────────────────┐
│                           place                           │
├───────────────────────────────────────────────────────────┤
│ (name,0101000020E610000032E6AE25E4635E40BB270F0BB5E63D40) │
└───────────────────────────────────────────────────────────┘
(1 row)
Time: 0,208 ms

Another option would be to use non-literal form, which allows function calls:

[local] gis@gis=# SELECT ('name', ST_GeogFromText('POINT(121.560800 29.901200)'))::place;;
┌───────────────────────────────────────────────────────────┐
│                            row                            │
├───────────────────────────────────────────────────────────┤
│ (name,0101000020E610000032E6AE25E4635E40BB270F0BB5E63D40) │
└───────────────────────────────────────────────────────────┘
(1 row)

Time: 5,004 ms

Upvotes: 2

Related Questions