### Complete Theme Configuration Example

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgstyle.md

A comprehensive example of UI and output styling in pgclirc.

```ini
[colors]
# UI Elements
completion-menu.completion = bg:#1e1e1e #d4d4d4
completion-menu.completion.current = bg:#0e639c #ffffff
keyword = bold #569cd6
selected = bg:#005a9e #ffffff

# Output Formatting  
output.header = bold #4ec9b0
output.odd-row = 
output.even-row = bg:#1e1e1e
output.null = italic #808080

# Transaction Status
bottom-toolbar = bg:#1e1e1e #d4d4d4
bottom-toolbar.transaction.valid = bg:#1e1e1e #6a9955
bottom-toolbar.transaction.failed = bg:#1e1e1e #d16969

```

--------------------------------

### Install pgcli with easy_install

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

If pip is not installed, you can attempt to install pgcli using easy_install.

```bash
$ sudo easy_install pgcli
```

--------------------------------

### Install Keyring on Linux

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Commands to install the keyring package and potentially a specific backend like GNOME Keyring or pass on a Linux system.

```bash
# Install keyring
pip install keyring

# For GNOME, ensure Secret Service is available
sudo apt-get install gnome-keyring

# Or use pass (password manager)
sudo apt-get install pass
keyring --keyring-provider-name=fail
```

--------------------------------

### Install pgcli with pip and sudo

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

If pip installation fails due to permission issues, try running the command with sudo permissions.

```bash
$ sudo pip install pgcli
```

--------------------------------

### Install pgcli using pip

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Install the latest version of pgcli using pip. This is the recommended method for most users.

```bash
$ pip install -U pgcli
```

--------------------------------

### Install pgcli with pip

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Install pgcli using pip, the Python package installer. Ensure PostgreSQL is installed on your system first.

```bash
$ pip install pgcli
```

--------------------------------

### Behave Configuration Example

Source: https://github.com/dbcli/pgcli/blob/main/CONTRIBUTING.rst

An example configuration for the behave testing package, specifying capture settings and user data.

```ini
[behave]
stderr_capture = false

[behave.userdata]
pg_test_user = dbuser
pg_test_host = db.example.com
pg_test_port = 30000
```

--------------------------------

### Instantiate MetaQuery

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/types.md

Provides an example of how to create a MetaQuery object with sample data for a SQL query.

```python
from pgcli.main import MetaQuery

query = MetaQuery(
    query="SELECT * FROM users",
    successful=True,
    total_time=0.123,
    execution_time=0.100,
    meta_changed=False,
    db_changed=False,
    path_changed=False,
    mutated=False,
    is_special=False
)
```

--------------------------------

### Run pgcli with uvx

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Use uvx to run pgcli by installing it on the fly into an isolated environment. This is useful for trying out pgcli without a permanent installation.

```bash
$ uvx pgcli
```

--------------------------------

### Install pgcli on macOS using Homebrew

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Install pgcli on macOS using the Homebrew package manager.

```bash
$ brew install pgcli
```

--------------------------------

### Connect to Database using PGExecute

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Example of establishing a database connection using the PGExecute class with specific connection parameters. This is a prerequisite for using PGCli programmatically.

```python
from pgcli.pgexecute import PGExecute
from pgcli.main import PGCli

executor = PGExecute(
    database="mydb",
    user="postgres",
    host="localhost",
    port=5432
)
pgcli = PGCli(pgexecute=executor)
```

--------------------------------

### Instantiate and Use FunctionMetadata

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Example demonstrating how to create a FunctionMetadata object and use its methods like has_variadic and args. Ensure FunctionMetadata is imported.

```python
from pgcli.packages.parseutils.meta import FunctionMetadata

func = FunctionMetadata(
    schema_name="public",
    func_name="get_user",
    arg_names=["user_id"],
    arg_types=["integer"],
    arg_modes=["i"],
    return_type="TABLE(id integer, name text)",
    is_aggregate=False,
    is_window=False,
    is_set_returning=True,
    is_extension=False,
    arg_defaults=None,
)

if func.has_variadic():
    print("Function accepts variable arguments")

for arg in func.args():
    print(f"{arg.name}: {arg.datatype}")
```

--------------------------------

### Install pgcli in Editable Mode

Source: https://github.com/dbcli/pgcli/blob/main/CONTRIBUTING.rst

Installs pgcli in editable mode using uv pip, linking the installation to the working copy for immediate effect of code changes.

```bash
$ uv pip install --editable .

or

$ uv pip install -e .
```

--------------------------------

### Install Testing Requirements

Source: https://github.com/dbcli/pgcli/blob/main/CONTRIBUTING.rst

Installs the necessary requirements for running pgcli integration tests, including development dependencies.

```bash
$ uv pip install ".[dev]"
```

--------------------------------

### Candidate Example

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/types.md

Creates a Candidate object for an auto-completion suggestion. Shows how to set the completion text, priority, and metadata.

```python
from pgcli.pgcompleter import Candidate

cand = Candidate(
    completion="user_id",
    prio=10,
    meta="column",
    synonyms=["uid", "user_id"],
    prio2=1,
    display="user_id",
)

```

--------------------------------

### Example pgcli Configuration File

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/configuration.md

A sample configuration file demonstrating various settings for pgcli. Includes connection defaults, display preferences, completion options, output formatting, pager settings, and behavioral flags.

```ini
[main]
# Connection defaults
host = localhost
port = 5432
user = postgres

# Display
table_format = psql
syntax_style = monokai
vi = True
multi_line = True

# Completion
smart_completion = True
search_path_filter = False

# Output
row_limit = 1000
null_string = NULL
expanded_output = False
auto_expand = True
max_field_width = 100

# Pager
enable_pager = True
pager = less
LESS = -SRXF

# Behavior
timing = True
verbose_errors = False
keyring = False
```

--------------------------------

### Install pgcli with pipx

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Install pgcli in an isolated virtual environment using pipx. This is recommended for managing Python applications.

```bash
$ pipx install pgcli
```

--------------------------------

### Install Encrypted File Backend for Keyring

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

If you need an encrypted file backend for keyring, use this pip command to install the required package. This is often necessary for secure credential storage.

```bash
pip install keyring[cryptfile]
```

--------------------------------

### Instantiate ColumnMetadata

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Example of creating a ColumnMetadata named tuple. Ensure ColumnMetadata is imported.

```python
from pgcli.packages.parseutils.meta import ColumnMetadata

col = ColumnMetadata(
    name="user_id",
    datatype="integer",
    foreignkeys=[],
    default=None,
    has_default=False
)
```

--------------------------------

### FunctionMetadata Example

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/types.md

Instantiates a FunctionMetadata object to represent a specific PostgreSQL function. Demonstrates how to populate the fields.

```python
from pgcli.packages.parseutils.meta import FunctionMetadata

func = FunctionMetadata(
    schema_name="public",
    func_name="get_users",
    arg_names=("min_id", "max_id"),
    arg_types=("integer", "integer"),
    arg_modes=("i", "i"),
    return_type="TABLE(id integer, name text)",
    is_aggregate=False,
    is_window=False,
    is_set_returning=True,
    is_extension=False,
    arg_defaults=("1", "1000"),
)

```

--------------------------------

### Instantiate ForeignKey

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/types.md

Provides an example of creating a ForeignKey object to represent a reference from 'orders.user_id' to 'users.id'.

```python
FK = ForeignKey(
    parentschema="public",
    parenttable="users",
    parentcolumn="id",
    childschema="public",
    childtable="orders",
    childcolumn="user_id",
)
```

--------------------------------

### Install pgcli on Debian-based Linux

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Install pgcli using the system's package manager on Debian-based Linux distributions. This method may provide an older version.

```bash
$ sudo apt-get install pgcli
```

--------------------------------

### Instantiate ForeignKey

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Example of creating a ForeignKey named tuple. Ensure ForeignKey is imported.

```python
from pgcli.packages.parseutils.meta import ForeignKey

fk = ForeignKey(
    parentschema="public",
    parenttable="users",
    parentcolumn="id",
    childschema="public",
    childtable="orders",
    childcolumn="user_id",
)
```

--------------------------------

### Complete Workflow for Completion Refresher

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-completion-refresher.md

Demonstrates the full lifecycle of the completion refresher, from initialization of necessary components (executor, completer) to defining a callback and starting the background refresh process. It also shows how to check if the refresh is ongoing and retrieve status messages.

```python
from pgcli.completion_refresher import CompletionRefresher
from pgcli.pgexecute import PGExecute
from pgcli.pgcompleter import PGCompleter
from pgspecial.main import PGSpecial

# Initialize
executor = PGExecute(database="mydb", user="postgres")
pgspecial = PGSpecial()
completer = PGCompleter(smart_completion=True, pgspecial=pgspecial)
refresher = CompletionRefresher()

# Define callback
def on_refresh_complete(new_completer):
    """Called when refresh completes"""
    print(f"Refresh complete with {len(new_completer.all_completions)} completions")

# Start background refresh
results = refresher.refresh(
    executor=executor,
    special=pgspecial,
    callbacks=on_refresh_complete,
    history=None,
    settings={
        "single_connection": False,
        "less_chatty": False,
    }
)

print(results[0][3])  # Print status message

# Check if still refreshing
while refresher.is_refreshing():
    print("Still refreshing...")
    time.sleep(1)

print("Refresh complete!")
```

--------------------------------

### INI Configuration File Structure

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Example of the INI-style configuration file structure used by PGCli, showing sections like [main], [data_formats], and [alias_dsn].

```ini
[main]
host = localhost
port = 5432
user = postgres
database = postgres

table_format = psql
syntax_style = native
vi = False
multi_line = False
multiline_mode = psql
keyword_casing = auto

smart_completion = True
search_path_filter = False
generate_casing_file = False
generate_aliases = False

timing = False
row_limit = 1000
null_string = <null>
max_field_width = 500
expanded_output = False
auto_expand = False
pager = less
enable_pager = True
```

```ini
[data_formats]
decimal = 0.00
float = 0.00
timestamp = %Y-%m-%d %H:%M:%S
```

```ini
[alias_dsn]
mydb = postgresql://user@localhost/mydb
```

--------------------------------

### Types and Constants Documentation

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Documentation for PGCli's type definitions, including named tuples like MetaQuery, OutputSettings, and ColumnMetadata, as well as completion context types and their usage examples.

```APIDOC
### Types & Constants

- Named tuples: MetaQuery, OutputSettings, ColumnMetadata, etc.
- Completion context types.
- Named tuple field documentation.
- Type usage examples.
```

--------------------------------

### Get pgcli Configuration Filename

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Returns the full path to the configuration file. It can either use the default location or a path explicitly provided as an argument.

```python
from pgcli.config import get_config_filename

# Use default location
default_config = get_config_filename()

# Use custom location
custom_config = get_config_filename("/etc/pgcli/config")
```

--------------------------------

### Set Up Development Virtual Environment

Source: https://github.com/dbcli/pgcli/blob/main/CONTRIBUTING.rst

Creates and activates a virtual environment for pgcli development using uv.

```bash
cd pgcli
uv venv
source ./pgcli-dev/bin/activate
```

--------------------------------

### Initialize PGExecute and PGCli

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Demonstrates how to initialize the PGExecute and PGCli objects for basic usage. Ensure you have the necessary database credentials.

```python
from pgcli.pgexecute import PGExecute
from pgcli.main import PGCli

executor = PGExecute(database="mydb", user="postgres")
pgcli = PGCli(pgexecute=executor)
```

--------------------------------

### Get SQL Completions for Cursor Position

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Demonstrates how to retrieve SQL completion suggestions for a given query string and cursor position using the `suggest_type` function. Useful for building custom SQL editors.

```python
from pgcli.packages.sqlcompletion import suggest_type

full_query = "SELECT * FROM users WHERE "
cursor_pos = len(full_query)
suggestions = suggest_type(full_query, full_query[:cursor_pos])
```

--------------------------------

### Connect to PostgreSQL with SSL using environment variables

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Configure and establish an SSL connection to a PostgreSQL database by setting environment variables for SSL mode, certificate, key, and root certificate, then connecting with pgcli.

```bash
export PGSSLMODE="verify-full"
export PGSSLCERT="/your-path-to-certs/client.crt"
export PGSSLKEY="/your-path-to-keys/client.key"
export PGSSLROOTCERT="/your-path-to-ca/ca.crt"
pgcli -h localhost -p 5432 -U username postgres
```

--------------------------------

### Markdown Code Block Example

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

This is an example of a standard Markdown code block with syntax highlighting, as used throughout the documentation.

```markdown
```markdown
### Method Name

Full signature as code block

| Parameter | Type | Description |
| ... table with name, type, default, description |

Returns: Type and semantics
Raises: Exception types and conditions
Description: What it does and why
Example: Runnable code snippet
Source: File path and line number
```
```

--------------------------------

### Initialize PGExecute with Basic Connection

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Establishes a PostgreSQL connection using basic parameters like database, user, and host. The connection is set to autocommit mode.

```python
from pgcli.pgexecute import PGExecute

# Basic connection
executor = PGExecute(database="mydb", user="postgres", host="localhost")
```

--------------------------------

### PGCli Keyring Initialization in __init__

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Shows how keyring support is initialized within the PGCli application's constructor, based on a configuration setting.

```python
import pgcli.auth

# In pgcli.main.PGCli.__init__:
auth.keyring_initialize(
    c["main"].as_bool("keyring"),
    logger=self.logger
)
```

--------------------------------

### Get SQL Completions

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Use this function to get completion suggestions for SQL statements. It requires the current query string and the text before the cursor.

```python
from pgcli.packages.sqlcompletion import suggest_type

suggestions = suggest_type("SELECT * FROM ", "SELECT * FROM ")
# Returns completion context for table suggestions
```

--------------------------------

### Initialize SqlStatement for Simple SELECT

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

Demonstrates initializing SqlStatement with a simple SELECT query and shows the last token and word before the cursor.

```python
from pgcli.packages.sqlcompletion import SqlStatement

# Simple SELECT
stmt = SqlStatement(
    "SELECT * FROM users WHERE",
    "SELECT * FROM users WHERE"
)
print(stmt.last_token)  # Token: "WHERE"
print(stmt.word_before_cursor)  # ""
```

--------------------------------

### Complete Authentication Workflow with Keyring

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Demonstrates a full authentication process using the keyring module. It initializes the keyring, attempts to retrieve a password, prompts the user if not found, stores the new password, and then uses it to establish a database connection.

```python
import logging
from pgcli.auth import keyring_initialize, keyring_get_password, keyring_set_password

logger = logging.getLogger(__name__)

# Initialize keyring
keyring_initialize(keyring_enabled=True, logger=logger)

# Try to retrieve stored password
password = keyring_get_password("postgres")

if not password:
    # Prompt user for password
    from getpass import getpass
    password = getpass("Enter PostgreSQL password: ")
    
    # Store for future use
    keyring_set_password("postgres", password)

# Use password to connect
from pgcli.pgexecute import PGExecute
executor = PGExecute(
    database="mydb",
    user="postgres",
    password=password,
    host="localhost"
)
```

--------------------------------

### Get View Columns Metadata

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves column information for all views.

```python
executor.view_columns()
```

--------------------------------

### Get All Schemas

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves a list of all available schema names in the database.

```python
all_schemas = executor.schemata()
```

--------------------------------

### Catch PgCliQuitError

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/errors.md

Example of how to catch the PgCliQuitError to handle user quit commands.

```python
from pgcli.main import PgCliQuitError

try:
    pgcli.quit()
except PgCliQuitError:
    print("User requested to quit")
    # Perform cleanup
```

--------------------------------

### Initialize PGCli Client

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-main.md

Instantiate the PGCli client for basic or custom configurations. Supports pre-initialized executors and various settings like row limit and output format.

```python
from pgcli.main import PGCli

# Create a basic pgcli client
pgcli = PGCli()
```

```python
from pgcli.main import PGCli

# Create with custom configuration
pgcli = PGCli(
    pgclirc_file="/home/user/.config/pgcli/config",
    row_limit=1000,
    auto_vertical_output=True,
    warn="all"
)
```

```python
from pgcli.main import PGCli
from pgcli.pgexecute import PGExecute
executor = PGExecute(database="mydb", user="postgres", host="localhost")
pgcli = PGCli(pgexecute=executor, single_connection=True)
```

--------------------------------

### PGCli Constructor

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-main.md

Initializes the pgcli client by loading configuration, setting up the database executor, initializing the completer, and registering special commands. This is called once at application startup.

```APIDOC
## PGCli Constructor

### Description
Initializes the pgcli client by loading configuration, setting up the database executor, initializing the completer, and registering special commands. This is called once at application startup.

### Signature
```python
__init__(
    self,
    force_passwd_prompt: bool = False,
    never_passwd_prompt: bool = False,
    pgexecute: PGExecute | None = None,
    pgclirc_file: str | None = None,
    row_limit: int | None = None,
    application_name: str = "pgcli",
    single_connection: bool = False,
    less_chatty: bool | None = None,
    prompt: str | None = None,
    prompt_dsn: str | None = None,
    auto_vertical_output: bool = False,
    warn: str | None = None,
    ssh_tunnel_url: str | None = None,
    log_file: str | None = None,
) -> None
```

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **force_passwd_prompt** (bool) - Optional - Force password prompt even if SSH key or other auth is available
- **never_passwd_prompt** (bool) - Optional - Never prompt for password
- **pgexecute** (PGExecute | None) - Optional - Pre-initialized database executor object; if None, created automatically
- **pgclirc_file** (str | None) - Optional - Path to configuration file; if None, uses default location
- **row_limit** (int | None) - Optional - Maximum number of rows to display; overrides config setting
- **application_name** (str) - Optional - Application name for PostgreSQL connection
- **single_connection** (bool) - Optional - Use single connection for both queries and completions
- **less_chatty** (bool | None) - Optional - Reduce informational output
- **prompt** (str | None) - Optional - Custom prompt format; uses psql-style placeholders like \u, \h, \d
- **prompt_dsn** (str | None) - Optional - Custom DSN-based prompt format
- **auto_vertical_output** (bool) - Optional - Automatically switch to vertical output for wide results
- **warn** (str | None) - Optional - Destructive query warning level ("true", "false", "all", "moderate")
- **ssh_tunnel_url** (str | None) - Optional - SSH tunnel URL for remote database connections
- **log_file** (str | None) - Optional - Path to log file for query results

### Request Example
```python
from pgcli.main import PGCli

# Create a basic pgcli client
pgcli = PGCli()

# Create with custom configuration
pgcli = PGCli(
    pgclirc_file="/home/user/.config/pgcli/config",
    row_limit=1000,
    auto_vertical_output=True,
    warn="all"
)

# Create with pre-configured executor
from pgcli.pgexecute import PGExecute
executor = PGExecute(database="mydb", user="postgres", host="localhost")
pgcli = PGCli(pgexecute=executor, single_connection=True)
```

### Response
#### Success Response (200)
None

#### Response Example
None

### Raises
- `ValueError` if configuration files are invalid
- `OperationalError` (psycopg) if database connection fails
```

--------------------------------

### Display pgcli help information

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Show the help message for pgcli, listing all available options and their descriptions.

```bash
$ pgcli --help
```

--------------------------------

### Get Casing Identifiers

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves user-defined identifiers for smart casing used in auto-completion.

```python
executor.casing()
```

--------------------------------

### Load pgcli Configuration

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Illustrates how to load pgcli's configuration settings using the `get_config` function. Allows access to settings like table format and row limit.

```python
from pgcli.config import get_config

config = get_config()
table_format = config["main"]["table_format"]
row_limit = config["main"].as_int("row_limit")
```

--------------------------------

### Connect to Database

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Programmatically connect to a PostgreSQL database using PGExecute and initialize PGCli.

```APIDOC
## Connect to Database

```python
from pgcli.pgexecute import PGExecute
from pgcli.main import PGCli

executor = PGExecute(
    database="mydb",
    user="postgres",
    host="localhost",
    port=5432
)
pgcli = PGCli(pgexecute=executor)
```
```

--------------------------------

### Python API Initialization

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Initialize the PGCli client and PGExecute for programmatic database interaction.

```APIDOC
## Python API

```python
from pgcli.main import PGCli
from pgcli.pgexecute import PGExecute

# Create executor
executor = PGExecute(database="mydb", user="postgres")

# Create client
pgcli = PGCli(pgexecute=executor)
```
```

--------------------------------

### Initialize Keyring Support

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Initializes the keyring module. Enable or disable keyring support and provide a logger instance. If the `keyring` package cannot be imported, a warning is logged.

```python
import logging
from pgcli.auth import keyring_initialize

logger = logging.getLogger(__name__)

# Enable keyring support
keyring_initialize(keyring_enabled=True, logger=logger)

# Disable keyring
keyring_initialize(keyring_enabled=False, logger=logger)
```

--------------------------------

### Get Completions for Position

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Retrieve SQL completion suggestions for a given query string and cursor position.

```APIDOC
## Get Completions for Position

```python
from pgcli.packages.sqlcompletion import suggest_type

full_query = "SELECT * FROM users WHERE "
cursor_pos = len(full_query)
suggestions = suggest_type(full_query, full_query[:cursor_pos])
```
```

--------------------------------

### CompletionRefresher.refresh

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-completion-refresher.md

Starts a background refresh of completion data. It can accept callbacks and settings to customize the refresh process.

```APIDOC
## refresher.refresh

### Description
Starts a background refresh of completion data. It can accept callbacks and settings to customize the refresh process.

### Signature
```python
refresher.refresh(
    executor: PGExecute,
    special: PGSpecial,
    callbacks: Callable = None,
    history: object = None,
    settings: dict = None
)
```

### Parameters
#### Arguments
- **executor** (PGExecute) - The database executor to use for fetching data.
- **special** (PGSpecial) - The PGSpecial object.
- **callbacks** (Callable, optional) - A function to be called when the refresh completes.
- **history** (object, optional) - A history object used to learn user preferences (must have `get_strings()` method).
- **settings** (dict, optional) - Configuration settings for the refresh process. See Connection Handling and History Learning for details.

### Returns
A list containing the results of the refresh operations.

### Example
```python
from pgcli.completion_refresher import CompletionRefresher
from pgcli.pgexecute import PGExecute
from pgcli.pgcompleter import PGCompleter
from pgspecial.main import PGSpecial
import time

# Initialize
executor = PGExecute(database="mydb", user="postgres")
pgspecial = PGSpecial()
completer = PGCompleter(smart_completion=True, pgspecial=pgspecial)
refresher = CompletionRefresher()

# Define callback
def on_refresh_complete(new_completer):
    """Called when refresh completes"""
    print(f"Refresh complete with {len(new_completer.all_completions)} completions")

# Start background refresh
results = refresher.refresh(
    executor=executor,
    special=pgspecial,
    callbacks=on_refresh_complete,
    history=None,
    settings={
        "single_connection": False,
        "less_chatty": False,
    }
)

print(results[0][3])  # Print status message

# Check if still refreshing
while refresher.is_refreshing():
    print("Still refreshing...")
    time.sleep(1)

print("Refresh complete!")
```
```

--------------------------------

### pgcli Python API Initialization

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Demonstrates how to initialize the PGCli and PGExecute classes in Python. The PGExecute class manages the database connection.

```python
from pgcli.main import PGCli
from pgcli.pgexecute import PGExecute

# Create executor
executor = PGExecute(database="mydb", user="postgres")

# Create client
pgcli = PGCli(pgexecute=executor)
```

--------------------------------

### Load Configuration

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Load pgcli configuration settings programmatically.

```APIDOC
## Load Configuration

```python
from pgcli.config import get_config

config = get_config()
table_format = config["main"]["table_format"]
row_limit = config["main"].as_int("row_limit")
```
```

--------------------------------

### Get Search Path Schemas

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves the current schema search path from PostgreSQL as a list of schema names.

```python
schemas = executor.search_path()  # ['public', 'pg_catalog', ...]
```

--------------------------------

### keyring_initialize

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Initializes the keyring module, enabling or disabling keyring support based on the provided flag. It attempts to import the 'keyring' package and configures its usage.

```APIDOC
## Function: keyring_initialize

### Description
Initializes the keyring module if enabled. Attempts to import the `keyring` package and sets the global keyring variable. If import fails, logs a warning but doesn't raise an exception.

### Parameters
#### Keyword Parameters
- **keyring_enabled** (bool) - Required - Whether to enable keyring support
- **logger** (logging.Logger) - Required - Logger instance for warnings

### Returns
None

### Example
```python
import logging
from pgcli.auth import keyring_initialize

logger = logging.getLogger(__name__)

# Enable keyring support
keyring_initialize(keyring_enabled=True, logger=logger)

# Disable keyring
keyring_initialize(keyring_enabled=False, logger=logger)
```
```

--------------------------------

### Get Function Output Fields

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Returns a list of output fields for a function. This method is part of the FunctionMetadata class.

```python
def fields(self) -> list[ColumnMetadata]:
    pass
```

--------------------------------

### suggest_special

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

Handles completions specifically for pgspecial backslash commands, providing relevant suggestions for commands starting with '\'.

```APIDOC
### Function: `suggest_special`

```python
def suggest_special(text: str) -> tuple
```

### Parameters

- **text** (str): Text of special command (starts with \).

### Returns

Special command completion context.

### Description

Handles completions for pgspecial backslash commands.
```

--------------------------------

### Catch psycopg DatabaseError

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/errors.md

Example of catching DatabaseError for SQL syntax errors, constraint violations, or query execution failures.

```python
from psycopg import DatabaseError

try:
    for result in executor.run("SELECT * FROM nonexistent_table"):
        pass
except DatabaseError as e:
    print(f"Database error: {e}")
```

--------------------------------

### Catch psycopg OperationalError

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/errors.md

Example of catching OperationalError to handle database connection failures. Includes common causes and solutions.

```python
from pgcli.pgexecute import PGExecute
from psycopg import OperationalError

try:
    executor = PGExecute(
        database="mydb",
        user="postgres",
        host="localhost",
        port=5432
    )
except OperationalError as e:
    print(f"Connection failed: {e}")
```

--------------------------------

### Get Table Columns Metadata

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves column information for all tables, including names, types, defaults, and foreign keys.

```python
executor.table_columns()
```

--------------------------------

### Connect to a local database

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Connect to a local PostgreSQL database by providing its name.

```bash
$ pgcli local_database
```

--------------------------------

### Get Tables Before Cursor

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

Shows how to use get_tables() with the 'before' scope to retrieve only the table references that appear before the cursor position.

```python
stmt = SqlStatement(
    "SELECT * FROM schema1.users u JOIN orders o ON u.id = o.user_id",
    "SELECT * FROM schema1.users u JOIN"
)
# Only tables before cursor
before = stmt.get_tables("before")
# Returns: [("schema1", "users")]
```

--------------------------------

### Load pgcli Configuration

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Loads configuration from user and optionally default configuration files. It merges the default configuration first, then overlays the user configuration. Access settings using dictionary-like access or .get() with a default value.

```python
from pgcli.config import load_config

# Load user config only
config = load_config("/home/user/.config/pgcli/config")

# Load with defaults
config = load_config(
    "/home/user/.config/pgcli/config",
    "/usr/share/pgcli/pgclirc"
)

# Access configuration
table_format = config["main"]["table_format"]
pager = config["main"].get("pager", "less")
```

--------------------------------

### Get Short Hostname

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Retrieves the abbreviated hostname from the executor. Useful for compact prompts. For IP addresses, the full address is returned.

```python
short = executor.short_host  # "myserver" from "myserver.example.com"
```

--------------------------------

### Writing Configuration with ConfigObj

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Shows how to write the modified configuration object back to its file using the .write() method.

```python
# Write configuration back to file
config.write()
```

--------------------------------

### Get All Tables in a Statement

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

Illustrates using get_tables() with the 'full' scope to retrieve all schema and table name pairs from a SQL statement.

```python
stmt = SqlStatement(
    "SELECT * FROM schema1.users u JOIN orders o ON u.id = o.user_id",
    "SELECT * FROM schema1.users u JOIN"
)

# All tables in statement
all_tables = stmt.get_tables("full")
# Returns: [("schema1", "users"), (None, "orders")]
```

--------------------------------

### Check Keyring Backend Availability

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

Run this Python command to verify which keyring backend is currently being used. This helps in diagnosing if keyring is initialized correctly.

```python
import keyring
print(keyring.get_keyring())
```

--------------------------------

### Build pgcli Docker image

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Build the Docker image for pgcli. This allows you to run pgcli within a Docker container without system-wide installation.

```bash
$ docker build -t pgcli .
```

--------------------------------

### Command-Line Entry Point

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

The pgcli command-line interface can be invoked with optional arguments to specify the database and user.

```APIDOC
## Command-Line

```bash
pgcli [OPTIONS] [DBNAME] [USERNAME]
```

Entry point defined in `pyproject.toml`:

```python
pgcli = "pgcli.main:cli"
```

The `cli()` function is a Click command that parses arguments and initializes PGCli.
```

--------------------------------

### Delete a Saved Query

Source: https://github.com/dbcli/pgcli/blob/main/changelog.rst

Remove a saved query using the '\nd [name]' command. For example, '\nd simple' deletes the query saved as 'simple'.

```sql
pgcli> \nd simple
```

--------------------------------

### Custom Key Binding

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

Add custom key bindings to PGCli to trigger specific actions. This example shows how to bind Ctrl+Q to a custom action.

```python
from pgcli.key_bindings import pgcli_bindings

kb = pgcli_bindings(pgcli)

@kb.add("c-q")
def _(event):
    pgcli.custom_action()
```

--------------------------------

### Project Scripts Configuration

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-main.md

Defines the pgcli command-line entry point in pyproject.toml.

```toml
[project.scripts]
pgcli = "pgcli.main:cli"
```

--------------------------------

### Get Function Input Parameters

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Returns a list of input parameters for a function, including default value information. This method is part of the FunctionMetadata class.

```python
def args(self) -> list[ColumnMetadata]:
    pass
```

--------------------------------

### Initialize PGExecute with Notification Callback

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Establishes a PostgreSQL connection and registers a callback function to handle PostgreSQL NOTIFY events. The connection is set to autocommit mode.

```python
def notify_handler(notify):
    print(f"Notification: {notify.payload}")

executor = PGExecute(
    database="mydb",
    notify_callback=notify_handler
)
```

--------------------------------

### Initialize Auto-Completion (Ctrl+Space)

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-key-bindings.md

Manually opens the auto-completion menu. If the menu is closed, it opens with suggestions; if open, it selects the next suggestion. Use arrow keys to navigate.

```python
@kb.add("c-space")
def _(event):
    b = event.app.current_buffer
    if b.complete_state:
        b.complete_next()
    else:
        b.start_completion(select_first=False)
```

--------------------------------

### Get Transaction State Indicator

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Returns a single character indicating the current transaction state, useful for displaying status in prompts. The indicator can be '?', '!', '*', or an empty string.

```python
indicator = executor.transaction_indicator
prompt = f"postgres{indicator}> "
```

--------------------------------

### Connect using a PostgreSQL connection string

Source: https://github.com/dbcli/pgcli/blob/main/README.rst

Connect to a PostgreSQL database using a full connection string, including user, password, host, port, and database name, along with SSL options.

```bash
$ pgcli postgres://amjith:pa$$w0rd@example.com:5432/app_db?sslmode=verify-ca&sslrootcert=/myrootcert
```

--------------------------------

### Get Source Tables in INSERT Statement

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

Demonstrates using get_tables() with the 'insert' scope for INSERT statements to retrieve only source tables, excluding the target table.

```python
stmt = SqlStatement(
    "INSERT INTO products SELECT * FROM inventory",
    "INSERT INTO products SELECT"
)
tables = stmt.get_tables("insert")
# Returns: [(None, "inventory")] - skips "products"
```

--------------------------------

### Execute a Saved Query

Source: https://github.com/dbcli/pgcli/blob/main/changelog.rst

Recall and execute a previously saved query by its name using the '\n [name]' command. For example, '\n simple' executes the query saved as 'simple'.

```sql
pgcli> \n simple
```

--------------------------------

### Initialize PGCompleter

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgcompleter.md

Initializes the PGCompleter with optional smart completion, a PGSpecial handler, and custom settings. Settings can configure casing and aliases.

```python
from pgcli.pgcompleter import PGCompleter
from pgspecial.main import PGSpecial

pgspecial = PGSpecial()
completer = PGCompleter(
    smart_completion=True,
    pgspecial=pgspecial,
    settings={
        "casing_file": "/home/user/.config/pgcli/casing",
        "generate_casing_file": True,
        "keyword_casing": "auto",
        "alias_map_file": None,
    }
)
```

--------------------------------

### Configuration Documentation

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Documentation covering all configuration options for PGCli, including the main section, color schemes, alias DSNs, SSH tunnels, prompt formats, environment variables, and command-line option mapping.

```APIDOC
### Configuration

- All [main] section options (30+ settings).
- [colors], [alias_dsn], [ssh tunnels] sections.
- Prompt format placeholders.
- Environment variables.
- Command-line option mapping.
```

--------------------------------

### Error Handling Documentation

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Details on PGCli's error handling mechanisms, including custom exceptions, database errors, error recovery patterns, and examples of exception catching.

```APIDOC
### Error Handling

- Custom exceptions (PgCliQuitError, InvalidMapFile).
- Database errors (OperationalError, DatabaseError, etc.).
- Error recovery patterns.
- Exception catching examples.
```

--------------------------------

### PGExecute Constructor

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Establishes a PostgreSQL connection and initializes the executor. The connection is set to autocommit mode. It accepts various connection parameters or a DSN string.

```APIDOC
## PGExecute `__init__`

### Description
Establishes a PostgreSQL connection and initializes the executor. The connection is set to autocommit mode.

### Signature
```python
class PGExecute:
    # ...
```

### Parameters
#### Constructor Parameters
- **database** (str | None) - Optional - Database name to connect to
- **user** (str | None) - Optional - PostgreSQL user name
- **password** (str | None) - Optional - User password
- **host** (str | None) - Optional - Host address or socket path
- **port** (int | str | None) - Optional - Port number (default: 5432)
- **dsn** (str | None) - Optional - Connection string (postgresql://...) overrides other params
- **notify_callback** (Callable[[psycopg.Notify], None] | None) - Optional - Callback function for PostgreSQL NOTIFY events
- **kwargs** (dict) - Optional - Additional psycopg3 connection parameters

### Raises
- `OperationalError` if connection fails
- `InterfaceError` if connection parameters are invalid

### Example
```python
from pgcli.pgexecute import PGExecute

# Basic connection
executor = PGExecute(database="mydb", user="postgres", host="localhost")

# Using DSN
executor = PGExecute(dsn="postgresql://user:pass@localhost:5432/mydb")

# With notification callback
def notify_handler(notify):
    print(f"Notification: {notify.payload}")

executor = PGExecute(
    database="mydb",
    notify_callback=notify_handler
)
```
```

--------------------------------

### Check if SQL Query Starts with Specific Prefixes

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-parseutils.md

Checks if a SQL query begins with any of the provided keywords (case-insensitive). Useful for identifying query types like DELETE or DROP.

```python
from pgcli.packages.parseutils import query_starts_with

sql = "DELETE FROM users WHERE id = 1"
if query_starts_with(sql.lower(), ["delete", "drop", "truncate"]):
    print("Destructive query detected")
```

--------------------------------

### Key Classes Documentation

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/README.md

Detailed documentation for key classes within PGCli, including PGCli, PGExecute, PGCompleter, CompletionRefresher, and FunctionMetadata. This includes their purpose, location, and the number of public methods documented.

```APIDOC
### Key Classes

1.  **PGCli** (pgcli/main.py)
    -   Interactive PostgreSQL client.
    -   Configuration and state management.
    -   Special command handling.
    -   10 public methods documented.

2.  **PGExecute** (pgcli/pgexecute.py)
    -   Database connection and query execution.
    -   Schema metadata retrieval.
    -   Transaction state tracking.
    -   20+ public methods documented.

3.  **PGCompleter** (pgcli/pgcompleter.py)
    -   SQL auto-completion engine.
    -   Smart context-aware filtering.
    -   10+ metadata extension methods.

4.  **CompletionRefresher** (pgcli/completion_refresher.py)
    -   Background schema refresh in threads.
    -   Completion metadata population.
    -   3 public methods + refresher registry.

5.  **FunctionMetadata** (pgcli/packages/parseutils/meta.py)
    -   PostgreSQL function signature representation.
    -   Argument modes, defaults, return types.
    -   2 main methods with examples.
```

--------------------------------

### CLI Entry Point Command

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-main.md

The command to invoke the pgcli CLI with optional database and username arguments.

```bash
pgcli [options] [database] [username]
```

--------------------------------

### Accessing ConfigObj Values

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Demonstrates how to access nested values, get values with type conversion (as_bool, as_int, as_list), retrieve values with defaults, and list sections/keys using the ConfigObj API.

```python
# Access nested values
value = config["section"]["key"]

# Get with type conversion
value = config["section"].as_bool("key")
value = config["section"].as_int("key")
value = config["section"].as_list("key")

# Get with defaults
value = config["section"].get("key", default_value)

# List sections and keys
sections = config.sections  # list of section names
keys = config["section"].keys()  # keys in a section
```

--------------------------------

### Get Casing File Path

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Retrieves the path to the casing file configured in the settings. If the configuration specifies 'default', it uses the default location within the config directory. Requires a loaded ConfigObj.

```python
from pgcli.config import get_casing_file, get_config

config = get_config()
casing_file = get_casing_file(config)

if casing_file:
    with open(casing_file) as f:
        casings = [line.strip() for line in f]
```

--------------------------------

### Get pgcli Configuration Directory Path

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Determines the configuration directory path based on the operating system and environment variables like XDG_CONFIG_HOME or USERPROFILE. Use this to find where pgcli stores its configuration.

```python
from pgcli.config import config_location

config_dir = config_location()
print(config_dir)  # /home/user/.config/pgcli/
```

--------------------------------

### Initialize PGExecute with DSN

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-pgexecute.md

Establishes a PostgreSQL connection using a Data Source Name (DSN) string, which overrides other connection parameters. The connection is set to autocommit mode.

```python
executor = PGExecute(dsn="postgresql://user:pass@localhost:5432/mydb")
```

--------------------------------

### Get SQL Completion Suggestions

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-sqlcompletion.md

This function retrieves completion suggestions for a given SQL query based on the cursor's position. It handles different types of suggestions like tables, columns, and keywords.

```python
from pgcli.packages.sqlcompletion import (
    suggest_type,
    Column,
    Table,
    Keyword,
    FromClauseItem,
)

def get_completions(full_query: str, cursor_position: int) -> list[str]:
    """Get completion suggestions for a query."""
    
    # Split text at cursor
    text_before_cursor = full_query[:cursor_position]
    
    # Get completion context
    suggestions = suggest_type(full_query, text_before_cursor)
    
    completions = []
    
    # Handle different completion types
    for suggestion in suggestions:
        if isinstance(suggestion, Table):
            # Suggest table names
            schema = suggestion.schema
            completions.extend(get_tables(schema))
        
        elif isinstance(suggestion, Column):
            # Suggest column names
            for table_ref in suggestion.table_refs:
                completions.extend(get_columns(table_ref))
        
        elif isinstance(suggestion, Keyword):
            # Suggest SQL keywords
            completions.extend(get_keywords())
    
    return completions
```

--------------------------------

### Run Unit Tests with Pytest

Source: https://github.com/dbcli/pgcli/blob/main/CONTRIBUTING.rst

Navigate to the pgcli directory and execute pytest to run the unit tests.

```bash
$ cd pgcli
$ pytest
```

--------------------------------

### Skip Initial Comments in Service File

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-config.md

Skips initial comment lines in a ~/.pg_service.conf file that might not start with '#'. Rewinds the file stream to the first configuration section. Accepts an open file object.

```python
from pgcli.config import skip_initial_comment

with open("/home/user/.pg_service.conf") as f:
    lines_skipped = skip_initial_comment(f)
    # Now safe to parse config sections
```

--------------------------------

### pgcli Command-Line Entry Point

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/INDEX.md

The command-line interface for pgcli, defined in pyproject.toml. It parses arguments and initializes the PGCli class.

```bash
pgcli [OPTIONS] [DBNAME] [USERNAME]
```

--------------------------------

### Keyring Error Message Template

Source: https://github.com/dbcli/pgcli/blob/main/_autodocs/api-reference-auth.md

A template string used for formatting error messages related to keyring operations. It suggests steps to resolve issues, including keyring setup, uninstallation, or disabling the feature.

```python
from textwrap import dedent

keyring_error_message = dedent(
    """
    {}
    {}
    To remove this message do one of the following:
    - prepare keyring as described at: https://keyring.readthedocs.io/en/stable/
    - uninstall keyring: pip uninstall keyring
    - disable keyring in our configuration: add keyring = False to [main]"""
)
```