Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PL/pgsql functions to compress and decompress PostgreSQL tables using cstore_fdw
-- 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
You can’t perform that action at this time.