packages/common/prisma/migrations/20240412103300_aws_resources/migration.sql [50:111]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
END
$$ LANGUAGE plpgsql;

-- Aggregate rows from all AWS Cloudquery tables
CREATE OR REPLACE FUNCTION aws_resources_raw()
    RETURNS TABLE
            (
                cq_table      text,
                partition     text,
                service       text,
                region        text,
                account_id    text,
                resource_type text,
                arn           text,
                taggable      boolean,
                tags          jsonb
            )
AS
$$
DECLARE
    cloudquery_table   record;
    account_id         text;
    request_account_id text;
    owner_id           text;
    region             text;
    tags               text;
    taggable           text;
BEGIN
    FOR cloudquery_table IN
            -- Find all base tables (no views allowed)
            SELECT DISTINCT table_name
            FROM information_schema.tables
            WHERE table_type = 'BASE TABLE'
            -- find the intersection of tables that have an ARN column
            INTERSECT
            SELECT DISTINCT table_name
            FROM information_schema.columns
            WHERE table_name LIKE 'aws_%s'
            and COLUMN_NAME IN ('arn')
        LOOP
            -- Check if table has an account_id column, or default to NULL
            account_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'account_id') THEN 'account_id' ELSE 'NULL' END;
            -- Check if table has an owner_id column, or default to NULL
            owner_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'owner_id') THEN 'owner_id' ELSE 'NULL' END;
            -- Check if table has an request_account_id column, or default to NULL
            request_account_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'request_account_id') THEN 'request_account_id' ELSE 'NULL' END;
            -- Check if table has an region column, or default to NULL
            region := CASE WHEN table_has_column(cloudquery_table.table_name, 'region') THEN 'region' ELSE 'NULL' END;
            -- Check if table has an tags column, or default to an empty object
            taggable := CASE WHEN table_has_column(cloudquery_table.table_name, 'tags') THEN 'true' ELSE 'false' END;
            tags := CASE WHEN taggable = 'true' THEN 'tags' ELSE '''{}''::jsonb' END;

            -- Fetch rows of table and append to function output
            RETURN QUERY EXECUTE FORMAT(E'
                    SELECT
                        %L as cq_table,
                        arn_to_partition(arn) as partition,
                        arn_to_service(arn) as service,
                        COALESCE(%s, arn_to_region(arn)) AS region,
                        COALESCE(%s, %s, %s, arn_to_account_id(arn)) AS account_id,
                        arn_to_resource_type(arn) as resource_type,
                        arn,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



packages/common/prisma/migrations/20240508195000_refresh_aws_resources/migration.sql [13:74]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
END
$$ LANGUAGE plpgsql;

-- Aggregate rows from all AWS Cloudquery tables
CREATE OR REPLACE FUNCTION aws_resources_raw()
    RETURNS TABLE
            (
                cq_table      text,
                partition     text,
                service       text,
                region        text,
                account_id    text,
                resource_type text,
                arn           text,
                taggable      boolean,
                tags          jsonb
            )
AS
$$
DECLARE
    cloudquery_table   record;
    account_id         text;
    request_account_id text;
    owner_id           text;
    region             text;
    tags               text;
    taggable           text;
BEGIN
    FOR cloudquery_table IN
        -- Find all base tables (no views allowed)
        SELECT DISTINCT table_name
        FROM information_schema.tables
        WHERE table_type = 'BASE TABLE'
        -- find the intersection of tables that have an ARN column
        INTERSECT
        SELECT DISTINCT table_name
        FROM information_schema.columns
        WHERE table_name LIKE 'aws_%s'
          and COLUMN_NAME IN ('arn')
        LOOP
            -- Check if table has an account_id column, or default to NULL
            account_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'account_id') THEN 'account_id' ELSE 'NULL' END;
            -- Check if table has an owner_id column, or default to NULL
            owner_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'owner_id') THEN 'owner_id' ELSE 'NULL' END;
            -- Check if table has an request_account_id column, or default to NULL
            request_account_id := CASE WHEN table_has_column(cloudquery_table.table_name, 'request_account_id') THEN 'request_account_id' ELSE 'NULL' END;
            -- Check if table has an region column, or default to NULL
            region := CASE WHEN table_has_column(cloudquery_table.table_name, 'region') THEN 'region' ELSE 'NULL' END;
            -- Check if table has an tags column, or default to an empty object
            taggable := CASE WHEN table_has_column(cloudquery_table.table_name, 'tags') THEN 'true' ELSE 'false' END;
            tags := CASE WHEN taggable = 'true' THEN 'tags' ELSE '''{}''::jsonb' END;

            -- Fetch rows of table and append to function output
            RETURN QUERY EXECUTE FORMAT(E'
                    SELECT
                        %L as cq_table,
                        arn_to_partition(arn) as partition,
                        arn_to_service(arn) as service,
                        COALESCE(%s, arn_to_region(arn)) AS region,
                        COALESCE(%s, %s, %s, arn_to_account_id(arn)) AS account_id,
                        arn_to_resource_type(arn) as resource_type,
                        arn,
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



