0

I am learning about triggers.

Generally it seems triggers are employed on the backend to perform some action on a schema. I was interested if it was possible for a trigger to return data on a SELECT statement to the main application.

In the table I'm working with we have several string columns followed with a geom (geometry::polygon) column which would contain the WKBT LAT/LONG values.

I'd like to return the rows which meet some WHERE clause AND also return the geom [LAT/LON] in WKBT format. By return I am interested in returning data to the main application.

I seek any examples.

Currently I build a list of all our column names {colA, colB, colC, colD} and then append the following as the last entry: public.ST_ASText(geom).

So I obtain a SELECT as follows.... SELECT colA, colB, colC colD, public.ST_AsText(geom) FROM schemaA WHERE .

So the crux of the problem here I cannot use SELECT * since I must convert the geom (LAT/LON pairs) to WKBT and this must be in the select along with every other column name defined, whether I use that column data or not. I definitely require the geom column in WKBT format.

This is passed via our DEVART interface. I was wondering if there is a better way to do this,

1

No, there is no "select" trigger in Postgres.

What you can do is to implement a view that transform the data for you.

CREATE OR REPLACE VIEW myView AS
    SELECT 
      mycol1,
      mycol2,
      geom,
      st_astext(geom) WKT
    FROM myTable;

If you need to be able to update the data, you would then put an insert/update/delete trigger on the view, that will manipulate the base table.

  • Thanks for the feed back. JGH -- again! I noticed you included "WKT" after the last select column function call. Is that required? I have been omitting that qualifier from my select clauses. – user9491577 Aug 16 '18 at 9:45
  • @user9491577 it is just an alias for the output column name. You can omit it or set it to (almost) whatever single word you want – JGH Aug 16 '18 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.