CODEWITHSUNDEEP
postgrest
Elrot
Elrot

Reputation: 273

postgREST inline function in select

Is there any way to use inline functions in postgREST?

Something similar to:

https://api.example.com/mytable?select=id,geom:ST_AsEWKT(geom)

in this notation i recieve an error:

Searched for a foreign key relationship between 'mytable' and 'ST_AsEWKT' in the schema 'public', but no matches were found.

I need quite a simple query:

SELECT id, ST_AsEWKT(geom) AS geom FROM mytable

ST_AsEWKT returns different representation of geom column.

Clean workaround is to use VIEWS with predefined columns:

CREATE VIEW vmytable AS SELECT id, ST_AsEWKT(geom) AS geom FROM mytable;

And then use posgREST api call on View table

https://api.example.com/vmytable?select=id,geom

Upvotes: 2

Views: 140

Answers (2)

Steve Chavez
Steve Chavez

Reputation: 1176

You can use a computed field for this case:

CREATE FUNCTION ewkt_geom(anyelement)
RETURNS text AS $$
  SELECT $1.geom;
$$ LANGUAGE SQL;

Then do:

https://api.example.com/vmytable?select=id,ewkt_geom

Upvotes: 3

Ishtiyak
Ishtiyak

Reputation: 91

you can use subquery

SELECT id, (SELECT ST_AsEWKT(geom) FROM mytable) AS geom

You can also use a JSONPath expression to achieve the same result.

SELECT id, $.geom FROM mytable

Upvotes: 1

Related Questions