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$
;

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.

select * from raster_data_extraction (128, 1.28, 300000, 5690220);

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$
;

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.

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