The function with WITH is throwing an error in PostgreSQL

Question

I am trying to put some statements into the following function.

CREATE OR REPLACE FUNCTION public.raster_data_extraction(
    resolution integer, 
    pixel_size double precision, 
    upper_x double precision, 
    upper_y double precision,
    out centroid geometry, 
    out b1 double precision,
    out b2 double precision,
    out b3 double precision,
    out b4 double precision,
    out b5 double precision,
    out b6 double precision,
    out b7 double precision)
RETURNS SETOF record
 LANGUAGE sql
 volatile STRICT
AS $function$


--- create fishnet
DROP TABLE IF EXISTS public.grid_function;
CREATE TABLE public.grid_function AS
SELECT *
--                    nrow, ncol, xsize (km), ysize (km), x0, y0
FROM ST_CreateFishnet(resolution, resolution, pixel_size, pixel_size, upper_x, upper_y) AS cells;
-- set srid
ALTER TABLE public.grid_function ALTER COLUMN geom TYPE geometry(Polygon,32632) USING ST_SetSRID(geom,32632);
-- drop old and create new index
drop index if exists grid_function_id; 
CREATE INDEX grid_function_id ON public.grid_function USING gist (geom);

-- raster to vector data
WITH raster_data AS (
  SELECT ST_PixelAsPolygons(rast.rast,1) band1, ST_PixelAsPolygons(rast.rast,2) band2,
         ST_PixelAsPolygons(rast.rast,3) band3, ST_PixelAsPolygons(rast.rast,4) band4,
         ST_PixelAsPolygons(rast.rast,5) band5, ST_PixelAsPolygons(rast.rast,6) band6,
         ST_PixelAsPolygons(rast.rast,7) band7
  
  FROM grid_function g , public.t32ulc_entire_tile rast
  WHERE ST_Intersects(rast.rast, g.geom)),

raster_bands as
(select  ST_Centroid((band1).geom) as centroid, 
        (band1).val as b1, 
        (band2).val as b2,
        (band3).val as b3,
        (band4).val as b4,
        (band5).val as b5,
        (band6).val as b6,
        (band7).val as b7

from raster_data)

select * from raster_bands;

$function$
;

What function does:

1. Creates grid table with the specified parameters
2. Using the created grid table it should extract data from raster table using the ST_Intersect rule. The grid table and raster data geometrically intersects.

The function created with no error. But execution of the following SQL query returns error.

SELECT * FROM raster_data_extraction (128, 1.28, 300000, 5690220);

the error is

SQL Error [42P01]: ERROR: relation with OID 76694653 does not exist
  Where: SQL function "raster_data_extraction" statement 6

Execution of statements with same parameters outside the function works perfectly. What is wrong here?

Answer 1

PostgreSQL parses and prepares the body of a Query Language function (LANGUAGE SQL) as a whole when called - meaning that all statements of the body are validated before actual execution.

So while the system reserves an OID for the CREATE TABLE statement, during preparation (on function call) that table does not exist for subsequent statements!

You will have to write a Procedural Language function (e.g. LANGUAGE 'plpgsql') in order to get transaction level access to volatile statements (like CREATE TABLE) inside the function.

---

As an aside: for intermediate result relations, better use CREATE TEMPORARY TABLE ... [ON COMMIT DROP].