CODEWITHSUNDEEP
postgresqlpostgis
huckfinn
huckfinn

Reputation: 685

How to write a PSQL function correctly to get column names into postgresql function results

I want to write a shortcut for the Postgresql/PostGIS function but cant get the column names of the result from the function XY2LL.

CREATE TABLE test_coord (
  pk SERIAL PRIMARY KEY,
  name VARCHAR(16),
  protox FLOAT8,
  protoy FLOAT8);

TRUNCATE test_coord;
INSERT INTO test_coord (name, protox, protoy) VALUES
( 'A',32547500.80, 6018256.35),
( 'B', 3547492.32, 6018251.12),
( 'C', 3547549.55, 6018205.50);

DROP FUNCTION XY2LL(INT, FLOAT8, FLOAT8, INT);

CREATE OR REPLACE 
FUNCTION XY2LL(rid INT, x FLOAT8, y FLOAT8, ssrid INT)
RETURNS TABLE (id  INT, lon FLOAT8, lat FLOAT8) AS $$
DECLARE
  geo GEOMETRY;
BEGIN
      geo := ST_Transform(ST_SetSRID(ST_MakePoint(X, Y), SSRID), 4326);
      RETURN QUERY SELECT RID AS ID, ST_X(geo) AS LON, ST_Y(geo) AS LAT;
END;
$$ LANGUAGE plpgsql;

I get something running but in fact I don't need a 'anonymous' query result.

SELECT (GK).* FROM (SELECT XY2LL(pk, protox, protoy,31467)
FROM test_coord WHERE protox < 32E6) AS GK;

                  xy2ll                 
---------------------------------------
 (8,9.72828064272417,54.2922508362509)
 (9,9.72915222443543,54.2918357099192)

Instead I want to get one record with:

SELECT GK.id, GK.lon, GK.lat  FROM (SELECT XY2LL(pk, protox, protoy,31467)
FROM test_coord WHERE protox < 32E6) AS GK;

What is the right expression? At least I want to perform something to correct mixed coordinate system entries if the field protox contains different Zone offsets (Bessel/ UTM):

ALTER TABLE test_coord ADD column lon FLOAT8;
ALTER TABLE test_coord ADD column lat FLOAT8;

UPDATE test_coord SET lon = GK.LON, lat = GK.lat 
FROM  (SELECT XY2LL(pk, protox, protoy,31467)
       FROM test_coord WHERE protox < 32E6) AS GK
WHERE pk = GK.ID;  

UPDATE test_coord SET lon = UTM.LON, lat = UTM.lat 
FROM  (SELECT XY2LL(pk, protox, protoy,4647)
       FROM test_coord WHERE protox >= 32E6) AS UTM
WHERE pk = UTM.ID;

Upvotes: 1

Views: 75

Answers (2)

Jim Jones
Jim Jones

Reputation: 19623

There are many ways to achieve it. One option is to use parenthesis around the record with a dot to access the columns, e.g. (rec).id. You can use a CTE ..

WITH j (rec) AS (
  SELECT XY2LL(pk, protox, protoy,31467)
  FROM test_coord WHERE protox < 32E6)
SELECT (rec).id,(rec).lon,(rec).lat 
FROM j;    

 id |        lon        |        lat        
----+-------------------+-------------------
  2 |   9.7283046990762 | 54.29225507826209
  3 | 9.729176273231237 | 54.29183994510761
(2 rows)

.. or just call the function using a JOIN:

SELECT gk.* FROM test_coord rec
JOIN XY2LL(rec.pk, rec.protox, rec.protoy, 31467) gk ON rec.pk = gk.id 
WHERE protox < 32E6;

 id |        lon        |        lat        
----+-------------------+-------------------
  2 |   9.7283046990762 | 54.29225507826209
  3 | 9.729176273231237 | 54.29183994510761
(2 rows)

Demo: db<>fiddle

Upvotes: 1

user330315
user330315

Reputation:

A set returning function should be used in the FROM clause:

SELECT gk.*
FROM test_coord tc
  cross join XY2LL(tc.pk, tc.protox, tc.protoy, 31467) as gk
WHERE protox < 32E6

Upvotes: 1

Related Questions