Untitled

 avatar
unknown
plain_text
a year ago
7.1 kB
4
Indexable
Current code:
CREATE OR REPLACE VIEW public.workflow_detail_view
 AS
 SELECT wv.workflow_id,
    wv.internal_workflow_id,
    wv.engine,
        CASE
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) -> 'workflowType'::text) IS NOT NULL THEN (wv.benchmark_config_jsonb -> 'config'::text) ->> 'workflowType'::text
            ELSE (wv.benchmark_config_jsonb -> 'config'::text) ->> 'pas_workflow_type'::text
        END AS workflow_type,
    ((wv.benchmark_config_jsonb -> 'config'::text) -> 'dbInstanceClasses'::text)::json AS dbinstanceclasses_json,
    rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbInstanceClasses'::text, '["'::text), '"]'::text) AS dbinstanceclasses,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'domain'::text AS domain,
    ((wv.benchmark_config_jsonb -> 'config'::text) -> 'dbParameterGroup'::text)::json AS dbparametergroup,
    ((wv.benchmark_config_jsonb -> 'config'::text) -> 'engineVersions'::text)::json AS engineversions_json,
    COALESCE(rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'engineVersions'::text, '["'::text), '"]'::text), (( SELECT i.engine_internal_version
           FROM instance_v2 i
             JOIN child_workflow_v2 cw ON i.child_workflow_id::text = cw.child_workflow_id::text
          WHERE wv.workflow_id::text = cw.workflow_id::text
         LIMIT 1))::text) AS engineversions,
    ((wv.benchmark_config_jsonb -> 'config'::text) -> 'threads'::text)::json AS threads,
    ((wv.benchmark_config_jsonb -> 'config'::text) -> 'emails'::text)::json AS emails,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'concurrency'::text AS concurrency,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'workloadGroup'::text AS workloadgroup,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'monitoringInterval'::text AS monitoringinterval,
    wv.status,
    wv.submitted_time,
    wv.start_time,
    wv.end_time,
    regexp_replace(wv.user_config::json ->> 'feature'::text, '[\[\]"]'::text, ''::text, 'g'::text) AS features,
    wv.username,
        CASE
            WHEN ((wv.benchmark_config_jsonb -> 'perf_service_configs'::text) ->> 'test_type'::text) = 'regression'::text THEN 'regression'::text
            ELSE 'adhoc'::text
        END AS test_type,
        CASE
            WHEN rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbInstanceClasses'::text, '["'::text), '"]'::text) ~~ '%.serverless'::text THEN split_part(((((wv.benchmark_config_jsonb -> 'config'::text) -> 'extraParams'::text) -> 'dbClusterExtraParams'::text) -> 'serverlessV2ScalingConfiguration'::text) ->> 'maxCapacity'::text, '.'::text, 1)
            ELSE split_part(rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbInstanceClasses'::text, '["'::text), '"]'::text), '.'::text, 3)
        END AS instance_size,
    (wv.benchmark_config_jsonb -> 'sidekick'::text) IS NOT NULL AS sidekick_enabled,
    "position"(wv.benchmark_config::text, '"--report-interval":'::text) > 0 AS sysbench_interval_enabled,
    "position"(wv.benchmark_config::text, '"--histogram": "on"'::text) > 0 AS histogram_enabled,
    ((wv.benchmark_config_jsonb -> 'testconfig'::text) -> 'copy_flamegraph_to_s3'::text) IS NOT NULL AS flamegraph_enabled,
    split_part(rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbInstanceClasses'::text, '["'::text), '"]'::text), '.'::text, 2) AS instance_family,
    COALESCE(( SELECT
                CASE
                    WHEN (((cls.describe_cluster_info -> 'DBClusters'::text) -> 0) ->> 'StorageType'::text) = 'aurora-iopt1'::text THEN 'enabled'::text
                    WHEN (((cls.describe_cluster_info -> 'DBClusters'::text) -> 0) ->> 'StorageType'::text) <> 'aurora-iopt1'::text THEN 'disabled'::text
                    WHEN cls.describe_cluster_info IS NOT NULL AND (((cls.describe_cluster_info -> 'DBClusters'::text) -> 0) ->> 'StorageType'::text) IS NULL THEN 'disabled'::text
                    ELSE NULL::text
                END AS "case"
           FROM cluster_v2 cls
             JOIN child_workflow_v2 cw ON cls.child_workflow_id::text = cw.child_workflow_id::text AND cw.workflow_id::text = wv.workflow_id::text
         LIMIT 1), ( SELECT
                CASE
                    WHEN (cc.config_json ->> 'volume_storage_sku'::text) = 'iopt1'::text THEN 'enabled'::text
                    WHEN (cc.config_json ->> 'volume_storage_sku'::text) <> 'iopt1'::text THEN 'disabled'::text
                    ELSE NULL::text
                END AS io_sku
           FROM csd_config_v2 cc
             JOIN child_workflow_v2 cw ON cc.child_workflow_id::text = cw.child_workflow_id::text AND cw.workflow_id::text = wv.workflow_id::text
         LIMIT 1),
        CASE
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) ->> 'storageType'::text) = 'aurora-iopt1'::text THEN 'enabled'::text
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) ->> 'storageType'::text) <> 'aurora-iopt1'::text THEN 'disabled'::text
            WHEN ((wv.benchmark_config_jsonb -> 'testconfig'::text) -> 'execute_enable_io_sku'::text) IS NOT NULL THEN 'enabled'::text
            ELSE 'disabled'::text
        END) AS io_sku,
    (wv.benchmark_config_jsonb -> 'logicalreplication'::text) IS NOT NULL AS logicalreplication_enabled,
    COALESCE("substring"(wv.internal_workflow_id::text, ('(.*)-'::text || wv.engine::text) || '-.*'::text), 'none'::text) AS prefix,
        CASE
            WHEN (((wv.benchmark_config_jsonb -> 'config'::text) -> 'endpoints'::text) ->> 0) ~~ '%rds-internal-%-pz-%-cluster%'::text THEN "substring"(((wv.benchmark_config_jsonb -> 'config'::text) -> 'endpoints'::text) ->> 0, 'rds-internal-(.*?)-pz-'::text)
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) ->> 'pz'::text) IS NOT NULL THEN (wv.benchmark_config_jsonb -> 'config'::text) ->> 'pz'::text
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbClusterNameSuffix'::text) ~~ 'rds-internal-%-pz-%-cluster'::text THEN "substring"((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbClusterNameSuffix'::text, 'rds-internal-(.*?)-pz-'::text)
            ELSE NULL::text
        END AS pz,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbClusterNameSuffix'::text AS db_cluster_name_suffix,
    (wv.benchmark_config_jsonb -> 'config'::text) ->> 'pasWorkflowType'::text AS pas_workflow_type,
    COALESCE(((wv.benchmark_config_jsonb -> 'config'::text) ->> 'numberOfReaders'::text)::integer, 0) AS number_of_readers
   FROM workflow_v2 wv;


I already have this column in workflow_detail view which is instance_family so I want to add a new column to this view based on that:
CASE WHEN instance_family = 'serverless'::text THEN GREATEST(instance_size::integer / 16, 1)
      WHEN instance_size ~ '\d'::text THEN regexp_replace(instance_size, '[^\d]'::text, ''::text, 'g'::text)::integer
      ELSE 0 END) * 4 AS instance_cores

How to do revise and add this code to the current workflow_detail_view code such that it takes the column instance_family from existing view and creates a new column from above logic
Leave a Comment