Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create view in Citus
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;
INSERT INTO pg_dist_shard
SELECT view_name, view_shardid, 'r', NULL, shardminvalue, shardmaxvalue
FROM pg_dist_shard
WHERE shardid = table_shardid;
INSERT INTO pg_dist_shard_placement
SELECT view_shardid, shardstate, 0, nodename, nodeport
FROM pg_dist_shard_placement
WHERE shardid = table_shardid;
PERFORM dblink_exec(format('host=%s port=%s', nodename, nodeport),
format('CREATE VIEW %I AS %s', view_name||'_'||view_shardid,
format(query_format, table_name||'_'||table_shardid)))
FROM pg_dist_shard_placement WHERE shardid = view_shardid;
RETURN view_shardid;
END;
$function$;
CREATE OR REPLACE FUNCTION public.create_view_shards(view_name regclass, table_name regclass, query_format text)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
IF EXISTS (SELECT 1 FROM pg_dist_shard WHERE logicalrelid = view_name) THEN
RAISE EXCEPTION '% already has shards', view_name;
END IF;
PERFORM create_shard_view(shardid, view_name, query_format)
FROM pg_dist_shard
WHERE logicalrelid = table_name;
END;
$function$;
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.