/*+ exists */
WITH tagged_resources AS
(
SELECT split_part(ResourceARN, '/', 2) as vpc_id
FROM awscc.tagging.tagged_resources
WHERE region = '{% raw %}{{ region }}{% endraw %}'
AND TagFilters = '{% raw %}{{ global_tags | to_aws_tag_filters }}{% endraw %}'
AND ResourceTypeFilters = '["ec2:vpc"]'
),
vpcs AS
(
SELECT vpc_id
FROM awscc.ec2.vpcs_list_only
WHERE region = '{% raw %}{{ region }}{% endraw %}'
)
SELECT r.vpc_id
FROM vpcs r
INNER JOIN tagged_resources tr
ON r.vpc_id = tr.vpc_id;
/*+ statecheck, retries=5, retry_delay=5 */
SELECT COUNT(*) as count FROM
(
SELECT
AWS_POLICY_EQUAL(tags, '{% raw %}{{ vpc_tags }}{% endraw %}') as test_tags
FROM awscc.ec2.vpcs
WHERE Identifier = '{% raw %}{{ this.vpc_id }}{% endraw %}'
AND region = '{% raw %}{{ region }}{% endraw %}'
AND cidr_block = '{% raw %}{{ vpc_cidr_block }}{% endraw %}'
) t
WHERE test_tags = 1;
/*+ create */
INSERT INTO awscc.ec2.vpcs (
CidrBlock,
Tags,
EnableDnsSupport,
EnableDnsHostnames,
region
)
SELECT
'{% raw %}{{ vpc_cidr_block }}{% endraw %}',
'{% raw %}{{ vpc_tags }}{% endraw %}',
true,
true,
'{% raw %}{{ region }}{% endraw %}'
RETURNING *;
/*+ exports */
SELECT '{% raw %}{{ this.vpc_id }}{% endraw %}' as vpc_id,
'{% raw %}{{ vpc_cidr_block }}{% endraw %}' as vpc_cidr_block;
/*+ delete */
DELETE FROM awscc.ec2.vpcs
WHERE Identifier = '{% raw %}{{ vpc_id }}{% endraw %}'
AND region = '{% raw %}{{ region }}{% endraw %}';