PostgreSQL - Block Structure

In PostgreSQL, particularly when working with procedural languages like PL/pgSQL, there's a concept of a block structure that's used to group and encapsulate related SQL statements and logic. This block structure is foundational to creating functions, procedures, and other modular code constructs.

Here's an overview of the block structure in PL/pgSQL:

Basic Structure:

[ <<label>> ]
[ DECLARE
    -- variable declarations go here
]
BEGIN
    -- SQL statements and PL/pgSQL commands go here
EXCEPTION
    WHEN condition1 THEN
        -- handle exception
    WHEN condition2 THEN
        -- handle another exception
    -- ... more exception handling if needed
END;

Components:

label: An optional label for the block. Useful for nested blocks and control structures.
DECLARE: This section is optional and used to declare local variables, constants, and cursors that you'll use in the block.
BEGIN ... END: The main body of the block, where SQL statements and PL/pgSQL commands are executed.
EXCEPTION: This section is optional and used to handle exceptions. Each WHEN defines a particular exception condition and how to handle it.

Examples:

Simple Block:
```
DO $$
BEGIN
    RAISE NOTICE 'This is a simple block';
END;
$$ LANGUAGE plpgsql;
```
This PL/pgSQL block uses the DO command to execute an anonymous code block that raises a notice.

Block with Variable Declaration and Exception Handling:

DO $$
DECLARE
    divisor INTEGER := 0;  -- intentionally setting to 0 to cause an error
    result REAL;
BEGIN
    result := 10 / divisor;  -- This will raise an error
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Tried to divide by zero!';
END;
$$ LANGUAGE plpgsql;

In this block, we have declared a variable divisor and initialized it to zero. Then we try to divide by it, which will raise a division by zero exception. This exception is caught and handled in the EXCEPTION block.

Nested Blocks:

You can nest blocks within other blocks to establish different scopes and levels of exception handling.

BEGIN
    -- outer block logic
    BEGIN
        -- inner block logic
    EXCEPTION
        -- inner block exception handling
    END;
EXCEPTION
    -- outer block exception handling
END;

Remember, when working with functions and procedures, you would also include other components like function parameters, return types, etc., alongside these block structures.

Declaring variables in PostgreSQL block structure:
- Description: You can declare variables within a PL/pgSQL block using the DECLARE keyword. Variables can store various data types.
- Code:
```
DO $$ 
DECLARE
  my_variable INT := 42;
  my_text_variable TEXT := 'Hello, World!';
BEGIN
  -- Your PL/pgSQL code here
END $$;
```

Conditional statements in PostgreSQL block structure:

Description: Use IF, ELSIF, and ELSE to create conditional statements within a PL/pgSQL block.

Code:

DO $$
DECLARE
  my_number INT := 10;
BEGIN
  IF my_number > 0 THEN
    RAISE NOTICE 'Number is positive';
  ELSE
    RAISE NOTICE 'Number is non-positive';
  END IF;
END $$;

Looping constructs in PL/pgSQL blocks in PostgreSQL:
- Description: PL/pgSQL supports various loop constructs such as FOR, WHILE, and LOOP for repetitive tasks.
- Code:
```
DO $$
DECLARE
  counter INT := 1;
BEGIN
  FOR counter IN 1..5 LOOP
    RAISE NOTICE 'Iteration: %', counter;
  END LOOP;
END $$;
```
Exception handling in PostgreSQL block structure:
- Description: Handle exceptions using EXCEPTION blocks to gracefully manage errors within a PL/pgSQL block.
- Code:
```
DO $$
BEGIN
  -- Your PL/pgSQL code here
  EXCEPTION
    WHEN others THEN
      RAISE NOTICE 'An error occurred: %', SQLERRM;
END $$;
```

Nested blocks and scoping in PL/pgSQL:

Description: PL/pgSQL allows nesting of blocks, and variables are scoped to their respective blocks.

Code:

DO $$
DECLARE
  outer_variable INT := 42;
BEGIN
  DECLARE
    inner_variable INT := 10;
  BEGIN
    -- Access outer and inner variables here
  END;
END $$;

Transactions within block structures in PostgreSQL:

Description: You can start transactions within PL/pgSQL blocks using BEGIN and COMMIT or ROLLBACK.

Code:

DO $$
BEGIN
  BEGIN
    -- Start of transaction
    UPDATE my_table SET column1 = value1 WHERE condition;
  EXCEPTION
    WHEN others THEN
      -- Handle error and possibly rollback changes
      ROLLBACK;
  END;

  -- Continue with more PL/pgSQL code
  COMMIT;
END $$;

Returning values from PL/pgSQL blocks in PostgreSQL:
- Description: Use the RETURN statement to return values from PL/pgSQL blocks, especially when using the DO anonymous block.
- Code:
```
DO $$
DECLARE
  result INT;
BEGIN
  -- Your PL/pgSQL code here
  result := 42;
  RETURN result;
END $$ LANGUAGE plpgsql;
```