How to create function postgresql join table in Hasura

Question

I want to create a function to calculate the distance and sort between user and geolayer. I do the following:

CREATE OR REPLACE FUNCTION search_geolayer_near_user(location text) 
RETURNS TABLE(id int, layer_name text, gps_to_center_layer numeric, gps_belong_province boolean) AS $func$
    SELECT gl.id, gl.layer_name, ST_Distance(
            ST_AsText(ST_Centroid(ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(gl.minx, gl.miny, gl.maxx, gl.maxy, 4326))))),
            location
        ) AS gps_to_center_layer,
        ST_AsText(ST_Centroid(ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(r.minx, r.miny, r.maxx, r.maxy, 4326))))),
    ST_contains(
    ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(r.minx, r.miny, r.maxx, r.maxy, 4326))),
    ST_GeometryFromText(location)) 
     AS gps_belong_province
    --   
    FROM "GeoLayers" gl JOIN "Regions" r ON gl.province_id = r.id
    WHERE gl.deleted_flg = false 
    GROUP BY gl.id , gl.layer_name, r.minx, r.miny, r.maxx, r.maxy, gl.minx, gl.miny, gl.maxx, gl.maxy
    ORDER BY gps_belong_province DESC, gps_to_center_layer ASC 
$func$ LANGUAGE sql STABLE;

But error

"SQL Execution Failed postgres-error : return type mismatch in function declared to return record"

The normal query would look like this:

select gl.id , gl.layer_name, ST_Distance(
        ST_AsText(ST_Centroid(ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(gl.minx, gl.miny, gl.maxx, gl.maxy, 4326))))),
        'POINT(109.189083 12.272397)'
    ) as "GPS to center Layer",
    ST_AsText(ST_Centroid(ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(r.minx, r.miny, r.maxx, r.maxy, 4326))))),
ST_contains(
ST_GeometryFromText(ST_AsText(ST_MakeEnvelope(r.minx, r.miny, r.maxx, r.maxy, 4326))),
ST_GeometryFromText('POINT(109.189083 12.272397)')) 
 as "GPS belong Province"
--   
from "GeoLayers" gl join "Regions" r ON gl.province_id = r.id
where gl.deleted_flg = false 
group by gl.id , gl.layer_name, r.minx, r.miny, r.maxx, r.maxy, gl.minx, gl.miny, gl.maxx, gl.maxy
-- having st_contains = false 
order by "GPS belong Province" desc, "GPS to center Layer" asc 
-- limit 10
;

Data return:

 id    layer_name   GPS to center Layer st_astext   GPS belong Province
"539"  Bản đồ địa chính VIP - Phường Vĩnh Phước, Thành phố Nha Trang, Tỉnh Khánh Hòa"   "0.009276001515402381"  "POINT(112.2530484505 10.3798475845001)"    "t"

Jim Jones · Answer 1 · 2021-02-24T09:29:26.147

Your error message is saying that declared columns and query result do not match. The important thing here is that 1) the declared table in the RETURNS TABLE contains exactly the same columns, 2) they are in the same order and 3) their data types match. See this examples:

CREATE OR REPLACE FUNCTION myfunc(text,text) 
RETURNS TABLE(r1 text, r2 text) AS $$
BEGIN
  RETURN QUERY SELECT $1,$2;
END; $$ LANGUAGE plpgsql;

SELECT * FROM myfunc('foo','bar');

 r1  | r2  
-----+-----
 foo | bar

.. or using SQL:

CREATE OR REPLACE FUNCTION myfunc(text,text) 
RETURNS TABLE(r1 text, r2 text) AS $$
SELECT $1,$2;
$$ LANGUAGE SQL;

SELECT * FROM myfunc('foo','bar');

 r1  | r2  
-----+-----
 foo | bar

In your function the declared columns and query do not match. After a quick mapping I get:

id int: 539
layer_name text: Bản đồ địa chính VIP - Phường Vĩnh Phước, Thành phố -Nha Trang, Tỉnh Khánh Hòa
gps_to_center_layer numeric: 0.009276001515402381
gps_belong_province boolean: POINT(112.2530484505 10.3798475845001) (this is no boolean)
In the end of your SELECT there is also a boolean t that was not declared.

How to create function postgresql join table in Hasura

1 Answers1