cxgn_breedbase.public

Parameters

Name	Type	Mode
refresh	boolean	IN

Definition

DECLARE
		maprow RECORD;
		querystr TEXT;
		queries TEXT[];
		emptyquery TEXT;
		matviewquery TEXT;
	BEGIN
	
		-- Remove exsiting materialized view, if it exists
		DROP MATERIALIZED VIEW IF EXISTS public.materialized_markerview;

-- Get the unique species / reference genome combinations from the nd_protocolprop table
		FOR maprow IN (
			SELECT value->>'species_name' AS species,
				concat(
					substring(split_part(value->>'species_name', ' ', 1), 1, 1),
					substring(split_part(value->>'species_name', ' ', 2), 1, 1)
				) AS species_abbreviation,
				value->>'reference_genome_name' AS reference_genome,
				replace(replace(value->>'reference_genome_name', '_', ''), ' ', '') AS reference_genome_cleaned
			FROM nd_protocolprop 
			WHERE type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = 'vcf_map_details')
			GROUP BY species, reference_genome
		)
		
		-- Loop through each unique combination of species / reference genome and build the marker query
		LOOP
			querystr := 'SELECT nd_protocolprop.nd_protocol_id, ''' || maprow.species || ''' AS species_name, ''' || maprow.reference_genome || ''' AS reference_genome_name, s.value->>''name'' AS marker_name, s.value->>''chrom'' AS chrom, cast(coalesce(nullif(s.value->>''pos'',''''),NULL) as numeric) AS pos, s.value->>''ref'' AS ref, s.value->>''alt'' AS alt, CASE WHEN s.value->>''alt'' < s.value->>''ref'' THEN concat(''' || maprow.species_abbreviation || ''', ''' || maprow.reference_genome_cleaned || ''', ''_'', REGEXP_REPLACE(s.value->>''chrom'', ''^chr?'', ''''), ''_'', s.value->>''pos'', ''_'', s.value->>''alt'', ''_'', s.value->>''ref'') ELSE concat(''' || maprow.species_abbreviation || ''', ''' || maprow.reference_genome_cleaned || ''', ''_'', REGEXP_REPLACE(s.value->>''chrom'', ''^chr?'', ''''), ''_'', s.value->>''pos'', ''_'', s.value->>''ref'', ''_'', s.value->>''alt'') END AS variant_name FROM nd_protocolprop, LATERAL jsonb_each(nd_protocolprop.value) s(key, value) WHERE type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = ''vcf_map_details_markers'') AND nd_protocol_id IN (SELECT nd_protocol_id FROM nd_protocolprop WHERE value->>''species_name'' = ''' || maprow.species || ''' and value->>''reference_genome_name'' = ''' || maprow.reference_genome || ''' AND type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = ''vcf_map_details''))';
			queries := array_append(queries, querystr);

END LOOP;

-- Add an empty query in case there is no existing marker data
		emptyquery := 'SELECT column1::int AS nd_protocol_id, column2::text AS species_name, column3::text AS reference_genome_name, column4::text AS marker_name, column5::text AS chrom, column6::numeric AS pos, column7::text AS ref, column8::text AS alt, column9::text AS variant_name FROM (values (null,null,null,null,null,null,null,null,null)) AS x WHERE false';
		queries := array_append(queries, emptyquery);
		
		-- Combine queries with a UNION
		matviewquery := array_to_string(queries, ' UNION ');

-- Build the materialized view
		EXECUTE 'CREATE MATERIALIZED VIEW public.materialized_markerview AS (' || matviewquery || ') WITH NO DATA';
		ALTER MATERIALIZED VIEW public.materialized_markerview OWNER TO web_usr;

-- Add indexes
		CREATE INDEX materialized_markerview_idx1 ON public.materialized_markerview(nd_protocol_id);
		CREATE INDEX materialized_markerview_idx2 ON public.materialized_markerview(species_name);
		CREATE INDEX materialized_markerview_idx3 ON public.materialized_markerview(reference_genome_name);
		CREATE INDEX materialized_markerview_idx4 ON public.materialized_markerview(marker_name);
		CREATE INDEX materialized_markerview_idx5 ON public.materialized_markerview(UPPER(marker_name));
		CREATE INDEX materialized_markerview_idx6 ON public.materialized_markerview(chrom);
		CREATE INDEX materialized_markerview_idx7 ON public.materialized_markerview(pos);
		CREATE INDEX materialized_markerview_idx8 ON public.materialized_markerview(variant_name);
		CREATE INDEX materialized_markerview_idx9 ON public.materialized_markerview(UPPER(variant_name));
		CREATE INDEX materialized_markerview_idx10 ON public.materialized_markerview USING GIN(marker_name gin_trgm_ops);
		CREATE INDEX materialized_markerview_idx11 ON public.materialized_markerview USING GIN(variant_name gin_trgm_ops);

-- Refresh materialzied view, if requested with function argument
		IF $1 THEN
			EXECUTE 'REFRESH MATERIALIZED VIEW public.materialized_markerview';
		END IF;

-- Return true if the materialized view is refreshed
		RETURN $1;

END

create_materialized_markerview(refresh boolean)

Parameters

Definition