PostgreSQL Query which returns geom column as WKT set in function

Question

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[] (a byte array). I can convert the Byte array to the WKB format but converting the WBK to WKT format appears more complex. Hence, I was thinking I could return the geom col results as WKT.

My issue with this is I cannot use a SELECT * in the query to the DB. It seems I will need to parameterize ALL the columns in the SELECT command since I want the geom column in a DIFFERENT format.

Obvious Query:

SELECT * FROM temporary.summmarytable WHERE conditions = 'xxxxx'

has to become:

SELECT col1, col2 , col3. col4....ST_AsText(geom) WHERE condition = 'xxxxx'

I'm not a fan of long paramterized column lists as shown above. Is there a way to reset the geom output within a PostgreSQL function.

Answer 1

Here is a solution suggestion:

   CREATE OR REPLACE _fn_querygeomWKT(tablename character varying DEFAULT 
   NULL::varchar, clause text DEFAULT NULL::varchar) 
   RETURNS TABLE (systemid varchar, city varchar, wkt text) AS 
   $BODY$
   DECLARE sql text DEFAULT NULL;
   BEGIN
   sql := 'SELECT system_id, city, ST_AsText(geom) WHERE myschema.' || tablename !! ' WHERE ' || clause;
   RETURN QUERY EXECUTE sql;
   END;$BODY$
   LANGUAGE plpgsql STABLE;
   ALTER FUNCTION _fn_querygeomWKT(character varying, text) Set search_path=public;
   ALTER FUNCTION _fn_querygeomWKT(character varying, text) owner to anowner;

The only issue with this solution I'm seeing is the lose of decimal precision. My polygon coordinates only show 2 decimal places. Any suggestions??

i.e. POLYGON((37.98 7.51, 38.51 7.51, 38.51 6.98, 37.98 6.98, 37.98 7.51))