CODEWITHSUNDEEP
postgresqlpostgisplpgsql
JMcClure
JMcClure

Reputation: 711

PL/pgSQL Return Query gives psuedo-type record rather than table

My first PL/pgSQL function is designed to take a geometry column as input and output a fishnet/grid table (a common task for me).

I'm not sure what I'm doing wrong on the return statement, though, as I'm getting back a pseudo-type record instead of a table with distinct columns.

My code:

CREATE OR REPLACE FUNCTION ST_Fishnet(
        nrow integer, 
        ncol integer,
        geo_table varchar,
        geom_column varchar)
RETURNS Table( ids text, geom geometry)
AS $$
DECLARE
    collected_geom geometry;
    xmin numeric;
    xmax numeric;
    ymin numeric;
    ymax numeric;
    xsize numeric;
    ysize numeric;
BEGIN
    EXECUTE 'SELECT st_collect("'||$4||'") FROM "'||$3||'"' INTO collected_geom;
    xmax := st_xmin(collected_geom);
    xmin := st_xmax(collected_geom);
    ymin := st_ymin(collected_geom);
    ymax := st_ymax(collected_geom);
    xsize := abs(xmax - xmin) / $2;
    ysize := abs(ymax - ymin) / $1;
    RETURN QUERY 
        SELECT
            i+1||'-'||j+1 as ids,
            ST_Translate(cell, j * xsize + xmax, i * ysize + ymin) AS geom
        FROM 
                generate_series(0, $1 - 1) AS i,
                generate_series(0, $2 - 1) AS j,
                (SELECT ('POLYGON((0 0, 0 '||ysize||', '||xsize||' '||ysize||', '||xsize||' 0,0 0))')::geometry AS cell) AS temp;
END
$$ 
LANGUAGE plpgsql;

The output should look like a table with ids and geom columns, but comes out, instead as a pseudo type looking like: (1-1,01030000[...]40)

Have read through the docs, and I'm still unsure what I'm doing wrong.

Appreciate any pointers.

Thanks.

Upvotes: 1

Views: 1348

Answers (1)

JMcClure
JMcClure

Reputation: 711

Figured it out.

Calling the function SELECT * FROM ST_Fishnet(rows, columns, geo-table, the_geom)produced the desired table.

Upvotes: 6

Related Questions