Make Your Postgres Code More Readable

Learn how to make Postgres code more readable and understandable with custom delimiters.

Table of Contents 📖

• Postgres Delimiters
• Custom Delimiters

Postgres Delimiters

If you have been using Postgres, you have most likely come across dollar-quoted delimiters like in the following example:

DO $$$
BEGIN
    RAISE NOTICE 'Hello, world!';
END;
$$$;

INFO: In PostgreSQL, a DO block is an anonymous procedural block, and PL/pgSQL is the default language used for these blocks.

Dollar-quoting is a mechanism in PostgreSQL that allows you to define strings or code blocks without needing to escape special characters like single quotes. The default delimiter is $$$. Here it marks the start and end of the code block.

Custom Delimiters

What's great about Postgres delimiters is that it is very easy to create your own custom delimiters. This allows you to create blocks of code that are easier to read and understand. Check out the two examples below:

DO $function_body$
BEGIN
    RAISE NOTICE 'Function execution started.';
END;
$function_body$;

DO $json_block$
BEGIN
    RAISE NOTICE '{
        "key": "value",
        "message": "This is an example with $$"
    }';
END;
$json_block$;

INFO: You can specify a custom delimiter by appending a label to the dollar sign. The label can be any alphanumeric string and must match at both the start and end of the block.

Custom delimiters improve readability by allowing you to create meaningful names for your blocks. This can make your code more self-documenting and easier to understand. Custom delimiters also avoid collisions like in the example below:

DO $$$
BEGIN
    RAISE NOTICE 'This is a $$$clash$$$ example!';
END;
$$$;

This causes a delimiter clash because PostgreSQL interprets the $$$ in the string ('This is a $$$clash$$$ example!') as the end of the block, resulting in a syntax error. Custom delimiters resolve this issue by allowing you to define unique markers for the block.