1----------------------------------------------------------------------------------------------------------------------
2-- ST_SplitTable
3-- Split a table into a series of table which names are composed of the concatenation of a prefix
4-- and the value of a column. This function is usefull when loading many raster in one operation but
5-- still wanting to split them in different tables afterward. They must have been loaded with the -F
6-- raster2pgsql option so that different rasters are identifiable by a column.
7--
8-- sourcetablename   - The name of the table to split into multiple table
9-- targettableschema - The schema in which to create the new set of table
10-- targettableprefix - The prefix of the set of table names to create.
11-- suffixcolumnname  - The name of the column providing the suffix to each table name.
12--
13-- Example to split the table 'test' into a set of table starting with 't_' and
14-- ending with the value of the column 'rid' to be created in the 'public' schema.
15--
16-- SELECT ST_SplitTable('test', 'public', 't_', 'rid');;
17----------------------------------------------------------------------------------------------------------------------
18CREATE OR REPLACE FUNCTION ST_SplitTable(sourcetablename text, targettableschema text, targettableprefix text, suffixcolumnname text)
19RETURNS int AS
20$BODY$
21DECLARE
22    newtablename text;
23    uniqueid RECORD;
24BEGIN
25    FOR uniqueid IN EXECUTE 'SELECT DISTINCT ' || quote_ident(suffixcolumnname) || ' AS xyz123  FROM ' || sourcetablename LOOP
26        newtablename := targettableschema || '.' || targettableprefix || uniqueid.xyz123;
27    EXECUTE 'CREATE TABLE ' || quote_ident(newtablename) || ' AS SELECT * FROM ' || sourcetablename || ' WHERE ' || suffixcolumnname || ' = ' || uniqueid.xyz123;
28    END LOOP;
29    RETURN 1;
30END;
31$BODY$
32LANGUAGE plpgsql VOLATILE STRICT;
33
34---------------------------------------
35-- test
36CREATE TABLE test AS
37SELECT 1 AS rid, ST_MakeEmptyRaster(2,2,0,0,1,1,1,1,4326) AS rast
38UNION ALL
39SELECT 2 AS rid, ST_MakeEmptyRaster(2,2,0,0,1,1,1,1,4326) AS rast
40UNION ALL
41SELECT 1 AS rid, ST_MakeEmptyRaster(2,2,0,0,1,1,1,1,4326) AS rast
42UNION ALL
43SELECT 2 AS rid, ST_MakeEmptyRaster(2,2,0,0,1,1,1,1,4326) AS rast
44
45SELECT * FROM test;
46
47SELECT ST_SplitTable('test', 'public', 't_', 'rid');
48