marcocitus

TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"
 
psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
psql -tA -F" " -h localhost -d postgres -c "select relname, s.id, p.node_name, p.node_port from pg_class c, pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement p where s.relation_id = c.oid and s.id = p.shard_id and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c 'psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"

marcocitus

CREATE TABLE customer_reviews_regular
(                                                                                                                       
    customer_id TEXT not null,
    review_date DATE not null,
    review_rating INTEGER not null,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10) not null,
    product_title TEXT not null,
    product_sales_rank BIGINT,

marcocitus

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

marcocitus

CREATE TABLE IF NOT EXISTS logs (
    id uuid,
    date timestamptz,
    version jsonb,
    data jsonb
);

CREATE INDEX logs_id_idx ON logs (id);

SELECT master_create_distributed_table('logs', 'date', 'append');

marcocitus

-- Parent table
CREATE TABLE events (
 event_id BIGINT,
 event_date INT,
 monthyear INT,
 year INT,
 FractionDate DOUBLE PRECISION,
 Actor1Code TEXT,
 Actor1Name TEXT,
 Actor1CountryCode TEXT,

marcocitus

CREATE OR REPLACE FUNCTION public.create_shard_view(table_shardid bigint, view_name regclass, query_format text)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE
  view_shardid bigint := master_get_new_shardid();
  table_name regclass;
  placement record;
BEGIN
  SELECT logicalrelid INTO table_name FROM pg_dist_shard WHERE shardid = table_shardid;

marcocitus

CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
  SELECT setweight(to_tsvector(name),'A') || 
         setweight(to_tsvector(description),'B');
$function$;

marcocitus

--
-- Test compatibility with Citus extension
--

-- Function to perform a COPY, but hide error messages
CREATE OR REPLACE FUNCTION silent_copy()
RETURNS void LANGUAGE plpgsql AS $function$
BEGIN
    COPY contestant FROM '@abs_srcdir@/data/contestants.1.csv' WITH CSV;
EXCEPTION WHEN OTHERS THEN

marcocitus

CREATE OR REPLACE FUNCTION consecutive_date_pattern(start_date date, num_days int)
RETURNS text[]
AS $BODY$
DECLARE
  result text[];
BEGIN
  SELECT array_agg('date=>"'||d::date||'"') INTO result
  FROM generate_series(start_date, start_date + num_days * interval '1 day', '1 day') d;

  RETURN result;

marcocitus

-- Functions to partition a table and drop old partitions

CREATE SEQUENCE IF NOT EXISTS partition_id_seq;


CREATE OR REPLACE FUNCTION partition_create(
                             table_name regclass)
RETURNS text
LANGUAGE plpgsql
AS $function$