create_materialized_markerview(refresh boolean)

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