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