Created
August 19, 2015 19:50
-
-
Save marcocitus/65c830c4b9577b2e1097 to your computer and use it in GitHub Desktop.
PL/pgsql functions to compress and decompress PostgreSQL tables using cstore_fdw
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Compress a table using cstore_fdw | |
CREATE OR REPLACE FUNCTION compress_table(uncomp_table regclass) RETURNS VOID AS | |
$BODY$ | |
DECLARE | |
comp_table_name varchar := table_name || '_comp'; | |
BEGIN | |
IF EXISTS (SELECT 1 FROM pg_class WHERE oid = table_name AND relkind = 'f') THEN | |
RETURN; | |
END IF; | |
EXECUTE format('CREATE FOREIGN TABLE %I (%s) SERVER cstore_server OPTIONS(compression ''pglz'');', comp_table_name, get_columns_ddl(table_name::varchar)); | |
EXECUTE format('INSERT INTO %I SELECT * FROM %I', comp_table_name, table_name); | |
EXECUTE format('DROP TABLE %I', table_name); | |
EXECUTE format('ALTER TABLE %I RENAME TO %s', comp_table_name, table_name::text); | |
END; | |
$BODY$ | |
LANGUAGE plpgsql; | |
-- Turn a a foriegn table into a regular table | |
CREATE OR REPLACE FUNCTION decompress_table(table_name regclass) RETURNS VOID AS | |
$BODY$ | |
DECLARE | |
uncomp_table_name varchar := table_name || '_uncomp'; | |
BEGIN | |
IF EXISTS (SELECT 1 FROM pg_class WHERE oid = table_name AND relkind = 'r') THEN | |
RETURN; | |
END IF; | |
EXECUTE format('CREATE TABLE %I AS SELECT * FROM %I', uncomp_table_name, table_name); | |
EXECUTE format('DROP FOREIGN TABLE %I', table_name); | |
EXECUTE format('ALTER TABLE %I RENAME TO %I', uncomp_table_name, table_name); | |
END; | |
$BODY$ | |
LANGUAGE plpgsql; | |
-- Adapted from http://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr | |
CREATE OR REPLACE FUNCTION get_columns_ddl(p_table_name varchar) | |
RETURNS text AS | |
$BODY$ | |
DECLARE | |
v_table_ddl text; | |
column_record record; | |
BEGIN | |
FOR column_record IN | |
SELECT | |
b.nspname as schema_name, | |
b.relname as table_name, | |
a.attname as column_name, | |
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type, | |
CASE WHEN | |
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) | |
FROM pg_catalog.pg_attrdef d | |
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN | |
'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) | |
FROM pg_catalog.pg_attrdef d | |
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) | |
ELSE | |
'' | |
END as column_default_value, | |
CASE WHEN a.attnotnull = true THEN | |
'NOT NULL' | |
ELSE | |
'NULL' | |
END as column_not_null, | |
a.attnum as attnum, | |
e.max_attnum as max_attnum | |
FROM | |
pg_catalog.pg_attribute a | |
INNER JOIN | |
(SELECT c.oid, | |
n.nspname, | |
c.relname | |
FROM pg_catalog.pg_class c | |
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
WHERE c.relname ~ ('^('||p_table_name||')$') | |
AND pg_catalog.pg_table_is_visible(c.oid) | |
ORDER BY 2, 3) b | |
ON a.attrelid = b.oid | |
INNER JOIN | |
(SELECT | |
a.attrelid, | |
max(a.attnum) as max_attnum | |
FROM pg_catalog.pg_attribute a | |
WHERE a.attnum > 0 | |
AND NOT a.attisdropped | |
GROUP BY a.attrelid) e | |
ON a.attrelid=e.attrelid | |
WHERE a.attnum > 0 | |
AND NOT a.attisdropped | |
ORDER BY a.attnum | |
LOOP | |
IF column_record.attnum = 1 THEN | |
v_table_ddl:=''; | |
ELSE | |
v_table_ddl:=v_table_ddl||','; | |
END IF; | |
IF column_record.attnum <= column_record.max_attnum THEN | |
v_table_ddl:=v_table_ddl||chr(10)|| | |
' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null; | |
END IF; | |
END LOOP; | |
RETURN v_table_ddl; | |
END; | |
$BODY$ | |
LANGUAGE 'plpgsql'; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment