postgREST inline function in select

Question

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

Answer 1

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

Answer 2

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