0

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.

  • why not SELECT *, ST_AsText(geom) AS wkt_geom FROM ...? Not supported? – ThingumaBob Oct 1 '18 at 14:05
  • I wasn't sure you could use SELECT , ST_AsText(geom) syntax. Somehow I though I tried that and thought the "' could only be used independently.... – user9491577 Oct 1 '18 at 14:09
  • PostgreSQL should allow for that syntax, even without an alias (as opposd to e.g. Oracle). Have you tried? not sure about your application, but I don´t see why not... – ThingumaBob Oct 1 '18 at 14:12
  • I just tried... So my function current returns SETOF <table_name> AS...so the error I get now "structure of query does not match function return type". So I'm thinking I'll have to now change the return type to something like "records" and declare a record definition for each column ....so I'm back to my situation of naming cols. – user9491577 Oct 1 '18 at 14:24
  • I didn´t realize you are going to use this in a function...yes, a function needs a well defined return type that you will have to specify somehow: you can use RETURNS TABLE (<columns COLTYPES>), define IN and OUT columns in the function signature and use RETURNS SETOF RECORD AS (or you will have to specify columns each time you call the function -> large pain in the) or you can even define your own composite type with those columns and use your initial return statement with its name...either way, you will have to write them down once. I recommend the RETURN TABLE ... syntax. – ThingumaBob Oct 1 '18 at 15:00
0

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))

  • 1
    not quite sure what you're trying to get at with that search_path...or, honestly, with that whole function ,). but, if you need access to e.g. the ST_ functions in public, just write public.ST_AsText(). your function has a couple of typos, and you should use the REGCLASS type to access relations and and possibly format() to format the sql. also the DEFAULT NULLs don´t really make sense. as a hack to not list columns: you could CREATE TABLE __blueprint(wkt_geomety TEXT) INHERITS (<your_table>); and use ... RETURNS SETOF __blueprint AS ... with the extra column then – ThingumaBob Oct 2 '18 at 12:11
  • Typos due to the fact the code could not be cut and pasted. Could you point to an example to illustrate REGCLASS, format(), What I am doing here is return a table subset (less columns than the schema table) which satisfy the spatial query string injected via the variable "clause". The primary table is WKB so I return the spatial results (3rd column) in WKT and finally parse that into our vb .net spatial geometry along with two other pieces of ancillary information (system_id, city). I would be most interested in seeing how I could tweak my functions. – user9491577 Oct 19 '18 at 18:31
  • Continuing from previous comment.... In your "__blueprint" example you are suggesting I CREATE a TABLE of type WKT Text inheriting from the primary table and returns the results of this TABLE. I would be most interested in seeing an example. Thanks for your feed back. – user9491577 Oct 19 '18 at 18:32

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.