iasql_functions

Components

Functions

    iasql_begin

    iasql_commit

    iasql_get_errors

    iasql_get_sql_since

    iasql_help

    iasql_install

    iasql_modules_list

    iasql_preview

    iasql_rollback

    iasql_uninstall

Code examples

Begin, commit and preview

--- installs the aws_cloudwatch module
SELECT * FROM iasql_install('aws_cloudwatch');
--- starts a transaction
SELECT * FROM iasql_begin();
--- insert a log group
insert into log_group (log_group_name) values ('<logGroupName>');
--- calls iasql_preview should expect a creation
select * from iasql_preview();
--- calls iasql_commit should create
select * from iasql_commit();

Basic rollback functionality

--- installs the aws_cloudwatch module
SELECT * FROM iasql_install('aws_cloudwatch');
--- starts a transaction
SELECT * FROM iasql_begin();
--- insert a log group
insert into log_group (log_group_name) values ('<logGroupName>');
--- calls iasql_rollback should delete
select * from iasql_rollback();
--- checks the log group
select * from log_group where log_group_name = '<logGroupName>';

iasql_get_sql_since functionality

--- installs the aws_account module
SELECT * FROM iasql_install('aws_account');
--- inserts aws credentials
INSERT INTO aws_credentials (access_key_id, secret_access_key)
VALUES ('<AWS_ACCESS_KEY_ID>', '<AWS_SECRET_ACCESS_KEY>');
--- should work with no arguments retrieving all logs
SELECT * FROM iasql_get_sql_since();
--- should work with a valid text timestamp
SELECT * FROM iasql_get_sql_since('2023-01-01T12:00:00');
--- should work with a dynamic date
SELECT * FROM iasql_get_sql_since(now() + interval '2 seconds');
--- manual inserts on iasql audit logs
INSERT INTO iasql_audit_log (ts, table_name, "user", change_type, change)
VALUES (NOW(), 'iasql_audit_log', SESSION_USER, 'INSERT',  ('')::json);
--- checks update sql
SELECT * FROM iasql_get_sql_since();
--- checks delete sql
SELECT * FROM iasql_get_sql_since();
--- installs the aws_elb module
SELECT * FROM iasql_install('aws_elb');
--- begin a transaction
SELECT * FROM iasql_begin();
--- adds a new load balancer
BEGIN;
INSERT INTO load_balancer (load_balancer_name, scheme, vpc, load_balancer_type, ip_address_type)
VALUES ('<lbName>', '<lbScheme>', null, '<lbType>', '<lbIPAddressType>');

INSERT INTO load_balancer_security_groups(load_balancer_id, security_group_id)
SELECT (SELECT id FROM load_balancer WHERE load_balancer_name = '<lbName>'),
(SELECT id FROM security_group WHERE group_name = 'default' AND region = '<region>');
COMMIT;
--- checks sql sub-query tables load_balancer and security_group for load_balancer_security_group join table
SELECT * FROM iasql_get_sql_since();
--- checks timestamp update
SELECT *
FROM iasql_audit_log
WHERE change_type = 'INSERT' AND table_name IN ('load_balancer_security_groups', 'load_balancer');