Untitled
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