postgresql - 如何正确编写 PSQL 函数以将列名放入 postgresql 函数结果

Question

我想为 Postgresql/PostGIS 函数编写一个快捷方式，但无法从函数中获取结果的列名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;

我得到了一些运行，但实际上我不需要“匿名”查询结果。

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)

相反，我想获得一条记录：

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

什么是正确的表达方式？protox如果字段包含不同的区域偏移（贝塞尔/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;  

标签： postgresqlpostgis

Answer 1

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