I'm trying to find a way to automatically call a function on a specific column, without actually calling it in the sql statement.
I'm using PostGIS with types for different kinds of geometry. I need to insert and select said geometric columns in the Well-Known Binary (WKB) format, which requires calling ST_GeomFromWKB() for inserting WKB, and ST_AsBinary() to retrieve data in the same format.
Suppose I have a table as defined:
CREATE TABLE IF NOT EXISTS geomtest (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
geom geometry(POLYGON, 4326) NOT NULL
);
I would like to define a way to call the appropriate functions on the geom column, while referencing the column on its own, for instance, if I were to call:
SELECT * FROM geomtest;
Behind the scenes, I would like to call:
SELECT id, name, ST_AsBinary(geom) FROM geomtest;
Same thing for inserts, but instead calling ST_GeomFromWKB() behind the scenes.
Note that the type returned by default is geometry (in hex format) but after calling ST_AsBinary(), the type is bytea.
I've tried to use views to get this to work. It worked for selecting, but not inserting. I've also looked into Rules and Triggers, but I'm not sure if it's possible to do what I'm hoping for.
Is there an sql solution to this problem?
Thanks