Table of Contents

About

In Postgres, an anonymous code block is executed via the non-standard SQL DO statement.

Steps

Start Postgres

The next steps will be using the postgres howto connection. 

tabul service start postgres

1 service was started
Name       Type
--------   ------
postgres   docker
  • Ping your connection to check that the database is up.
tabul connection ping postgres

The connection (postgres) has been pinged successfully

The Anonymous Block

This code example is extracted of the SQL DO statement documentation.

This anonymous block is idempotent:

  • it will create the role webuser if it does not exist
  • and grant it access to all view in the public schema

The anonymous code block is located at postgres/do_anonymous_block.sql in the howto connection

With the data print command, we can see the content. 

tabul data print --type text --pipe-mode postgres/do_anonymous_block.sql@howto
# --type text disable sql statement parsing
# --pipe-mode disallow the headers

-- https://www.postgresql.org/docs/current/sql-do.html
DO
$$DECLARE r record;
BEGIN
    IF
NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'webuser') THEN
CREATE ROLE webuser;
END IF;
FOR r IN
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'VIEW'
  AND table_schema = 'public' LOOP
        EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
END LOOP;
END$$;

Execution

tabul data execute '(postgres/do_anonymous_block.sql@howto)@postgres'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

List of runtime executed
runtime_data_uri                                   exit_code   count   latency   data_uri                                                                      error_message
------------------------------------------------   ---------   -----   -------   ---------------------------------------------------------------------------   -------------
(postgres/do_anonymous_block.sql@howto)@postgres           0       1   0.43s     execute/20251110-204157-936-pipe-tabul-data-exec/do_anonymous_block.log@tmp

Validation

Anonymous block result returns only an execution status. To verify the execution result, you need: 

SELECT rolname
FROM pg_catalog.pg_roles
where rolname = 'webuser'



tabul data print '(postgres/do_anonymous_block_actual_roles.sql@howto)@postgres'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

(postgres/do_anonymous_block_actual_roles.sql@howto)@postgres
rolname
-------
webuser
  • or to perform a diff.
tabul data diff \
  postgres/do_anonymous_block_expected_roles.csv@howto \
  '(postgres/do_anonymous_block_actual_roles.sql@howto)@postgres'
# The quotes are mandatory because parenthesis have a meaning in Bash (ie they start a subshell)

Diff Summary Report
The data resources are equals.
from                                                   to                                                              equals   record_count   change_count
----------------------------------------------------   -------------------------------------------------------------   ------   ------------   ------------
postgres/do_anonymous_block_expected_roles.csv@howto   (postgres/do_anonymous_block_actual_roles.sql@howto)@postgres   ✓                   1              0