# `pg_conn2` - PostgreSQL Extension for Managing Separate Database Connections

## Overview

`pg_conn2` is a PostgreSQL extension that allows you to create and manage separate database connections from within your SQL queries. This is useful for scenarios where you need to execute queries on a different connection context within the same transaction.

## Features

- **Create separate connections**: Establish new database connections using `pg_conn2make(dbname)`
- **Execute queries**: Run SQL queries on separate connections with `pg_conn2exec()`
- **Automatic cleanup**: Connections are automatically closed at transaction end (commit or abort)

## Installation

### Prerequisites

- PostgreSQL development headers (`postgresql-devel` or `postgresql-server-dev-*`)
- LibPQ development library
- `pg_config` in your PATH

### Build and Install

```sh
make
sudo make install
```

### Enable the Extension

Connect to your database and run:

```sql
CREATE EXTENSION pg_conn2;
```

## Usage

### Basic Example

```sql
-- Create a new connection
SELECT pg_conn2make('mydb') AS conn \gset

-- Execute a query on that connection
SELECT pg_conn2exec(:'conn', 'CREATE TABLE test_table (id int, name text)');
SELECT pg_conn2exec(:'conn', 'INSERT INTO test_table VALUES (1, ''Alice''), (2, ''Bob'')');

-- Connection is automatically closed at transaction end (COMMIT or ROLLBACK)
COMMIT;
```

### Manual Connection Closing

```sql
BEGIN;

-- Create a connection
SELECT pg_conn2make('mydb') AS conn \gset

-- Execute queries
SELECT pg_conn2exec(:'conn', 'SELECT 1');

-- Manually close the connection
SELECT pg_conn2close(:'conn');

-- Auto-cleanup at transaction end will do nothing (already closed)
COMMIT;
```

### Error Handling

```sql
BEGIN;

SELECT pg_conn2make('mydb') AS conn \gset

-- If query fails, connection is still tracked for cleanup
SELECT pg_conn2exec(:'conn', 'INVALID SQL');

-- Connection auto-closed on ROLLBACK
ROLLBACK;
```

## Functions

### `pg_conn2make(dbname text) → pg_conn2`

Creates a new database connection to the specified database using local socket with user `postgres`.

**Parameters**:

- `dbname`: Name of the database to connect to

**Returns**: An opaque handle representing the connection

**Example**:

```sql
SELECT pg_conn2make('mydb') AS my_conn \gset
```

### `pg_conn2exec(connection pg_conn2, query text) → void`

Executes a SQL query on the specified connection.

**Parameters**:

- `connection`: Connection handle returned by `pg_conn2make()`
- `query`: SQL query string to execute

**Returns**: void

**Errors**:

- Raises error if connection is closed or invalid
- Raises error if query execution fails

**Example**:

```sql
SELECT pg_conn2exec(:'my_conn', 'CREATE TABLE foo (id int)');
```

### `pg_conn2close(connection pg_conn2) → void`

Manually closes a connection. Safe to call multiple times.

**Parameters**:

- `connection`: Connection handle to close

**Returns**: void

**Example**:

```sql
SELECT pg_conn2close(:'my_conn');
```

## Connection Details

- **Host**: Local Unix socket (`/var/run/postgresql`)
- **User**: `postgres`
- **Authentication**: Uses peer authentication or configured authentication for the `postgres` user

## Transaction Behavior

Connections created with `pg_conn2make(dbname)` are automatically registered for cleanup at top transaction end either on `COMMIT`, `ROLLBACK` or manual close.

## Testing

To run the tests:

```sh
make installcheck
```

## Limitations

- Connections always use local socket with user `postgres` to the specified database
- The `pg_conn2` type cannot be stored in tables or persisted
- Query results from `pg_conn2exec()` and `pg_conn2exec_many()` are not returned