3

I'm writing a PL/pgSQL function to take a geography column and output a fishnet that bounds the extent of the shapes.

I've tried to emulate the examples in docs and tutorials, but the function returns a pseudo-type result, not a usable table.

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;

But while my output should have two distinct columns, ids and the geom, I'm getting pseudo-type record that looks like: (id, geom)

Very much appreciate any pointers as to what I'm missing.

Thanks.

2
2

This PostgreSQL function is called a set-returning function, which has 1 or more columns of different data types. To get to the data in the columns, use one of either:

  • SELECT * FROM ST_Fishnet(<...>) to return all columns of the set; or
  • SELECT (ST_Fishnet(<...>)).geom to return only one column (e.g., geom) of the set

(Note <...> are the parameters to the set-returning function).

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.