PostgreSQL SQL Parsing

/**
 * PostgreSQL SQL parser implementation
 */
public class PostgreSQLParser extends Parser {
    /**
     * Creates a new PostgreSQL parser
     * @param configuration Flyway configuration
     * @param parsingContext Parsing context with database information
     */
    public PostgreSQLParser(Configuration configuration, ParsingContext parsingContext);
    
    /**
     * Returns the alternative string literal quote character for PostgreSQL
     * Used for dollar-quoted strings ($tag$content$tag$)
     * @return '$' character
     */
    protected char getAlternativeStringLiteralQuote();
    
    /**
     * Creates a parsed SQL statement with PostgreSQL-specific handling
     * @param reader Input reader for SQL content
     * @param recorder Recording mechanism for parsing state
     * @param statementPos Position of statement start
     * @param statementLine Line number of statement start
     * @param statementCol Column number of statement start
     * @param nonCommentPartPos Position of non-comment content
     * @param nonCommentPartLine Line number of non-comment content
     * @param nonCommentPartCol Column number of non-comment content
     * @param statementType Type of SQL statement detected
     * @param canExecuteInTransaction Whether statement can run in transaction
     * @param delimiter Statement delimiter
     * @param sql Complete SQL text
     * @param tokens Parsed tokens
     * @param batchable Whether statement can be batched
     * @return Parsed statement (potentially PostgreSQLCopyParsedStatement for COPY)
     * @throws IOException if parsing fails
     */
    protected ParsedSqlStatement createStatement(PeekingReader reader, Recorder recorder,
        int statementPos, int statementLine, int statementCol, int nonCommentPartPos, 
        int nonCommentPartLine, int nonCommentPartCol, StatementType statementType, 
        boolean canExecuteInTransaction, Delimiter delimiter, String sql, 
        List<Token> tokens, boolean batchable) throws IOException;
    
    /**
     * Adjusts block depth for PostgreSQL-specific constructs
     * Handles BEGIN ATOMIC blocks and CASE statements within atomic blocks
     * @param context Parser context
     * @param tokens List of parsed tokens
     * @param keyword Current keyword being processed
     * @param reader Input reader
     */
    protected void adjustBlockDepth(ParserContext context, List<Token> tokens, 
                                   Token keyword, PeekingReader reader);
    
    /**
     * Detects PostgreSQL-specific statement types
     * @param simplifiedStatement Simplified SQL statement text
     * @param context Parser context
     * @param reader Input reader
     * @return Statement type (including COPY for COPY FROM STDIN)
     */
    protected StatementType detectStatementType(String simplifiedStatement, 
                                               ParserContext context, 
                                               PeekingReader reader);
    
    /**
     * Determines if a statement can execute within a transaction
     * @param simplifiedStatement Simplified SQL statement
     * @param keywords List of parsed keywords
     * @return true if can execute in transaction, false if must execute outside, null if default behavior
     */
    protected Boolean detectCanExecuteInTransaction(String simplifiedStatement, 
                                                   List<Token> keywords);
    
    /**
     * Handles PostgreSQL dollar-quoted string literals
     * @param reader Input reader
     * @param context Parser context
     * @param pos Position in input
     * @param line Line number
     * @param col Column number
     * @return String token for dollar-quoted literal
     * @throws IOException if parsing fails
     */
    protected Token handleAlternativeStringLiteral(PeekingReader reader, 
                                                   ParserContext context, 
                                                   int pos, int line, int col) throws IOException;
}

import org.flywaydb.database.postgresql.PostgreSQLParser;
import org.flywaydb.core.internal.parser.ParsingContext;

// Create parser (typically done by PostgreSQLDatabaseType)
Configuration config = new ClassicConfiguration();
ParsingContext context = new ParsingContext();
PostgreSQLParser parser = new PostgreSQLParser(config, context);

// Parser automatically handles PostgreSQL-specific syntax
// when processing migration files through Flyway

/**
 * Parsed statement for PostgreSQL COPY FROM STDIN operations
 */
public class PostgreSQLCopyParsedStatement extends ParsedSqlStatement {
    /**
     * Creates a COPY parsed statement
     * @param pos Position in source
     * @param line Line number
     * @param col Column number
     * @param sql SQL statement text
     * @param copyData The data portion of the COPY statement
     */
    public PostgreSQLCopyParsedStatement(int pos, int line, int col, 
                                        String sql, String copyData);
}

-- This PostgreSQL-specific syntax is automatically handled
COPY users (name, email, age) FROM STDIN;
John Doe	john@example.com	30
Jane Smith	jane@example.com	25
\.

-- Standard dollar quoting
CREATE FUNCTION example() RETURNS text AS $$
BEGIN
    RETURN 'This is dollar-quoted content';
END;
$$ LANGUAGE plpgsql;

-- Labeled dollar quoting  
CREATE FUNCTION tagged() RETURNS text AS $func$
BEGIN
    RETURN 'Content with $$ inside';
END;
$func$ LANGUAGE plpgsql;

-- These statements are detected as non-transactional
CREATE DATABASE mydb;
CREATE TABLESPACE mytablespace LOCATION '/data';
CREATE SUBSCRIPTION mysub CONNECTION 'host=localhost' PUBLICATION mypub;
ALTER SYSTEM SET work_mem = '64MB';
CREATE INDEX CONCURRENTLY idx_name ON table_name (column);
DROP INDEX CONCURRENTLY idx_name;
REINDEX DATABASE mydb;
VACUUM FULL table_name;
DISCARD ALL;

-- Version-specific handling
ALTER TYPE status_type ADD VALUE 'new_status';  -- Non-transactional in PostgreSQL < 12

CREATE FUNCTION atomic_example(x int) RETURNS int
LANGUAGE SQL
BEGIN ATOMIC
    CASE x
        WHEN 1 THEN RETURN x + 1;
        WHEN 2 THEN RETURN x + 2;
        ELSE RETURN x;
    END CASE;
END;

// Internal patterns (for reference)
private static final Pattern COPY_FROM_STDIN_REGEX = 
    Pattern.compile("^COPY( .*)? FROM STDIN");
private static final Pattern CREATE_DATABASE_TABLESPACE_SUBSCRIPTION_REGEX = 
    Pattern.compile("^(CREATE|DROP) (DATABASE|TABLESPACE|SUBSCRIPTION)");
private static final Pattern ALTER_SYSTEM_REGEX = 
    Pattern.compile("^ALTER SYSTEM");
private static final Pattern CREATE_INDEX_CONCURRENTLY_REGEX = 
    Pattern.compile("^(CREATE|DROP)( UNIQUE)? INDEX CONCURRENTLY");

// Example version-specific handling
if (parsingContext.getDatabase().getVersion().isAtLeast("12")) {
    // PostgreSQL 12+ allows ALTER TYPE ADD VALUE in transactions
    return null;  // Use default transaction behavior
} else {
    // Pre-12 versions require non-transactional execution
    return false;
}

try {
    ParsedSqlStatement statement = parser.parse(migrationSql);
} catch (FlywayParseException e) {
    System.err.println("SQL parsing failed at line " + e.getLineNumber() + 
                      ", column " + e.getColumnNumber());
    System.err.println("Error: " + e.getMessage());
}

// Migration file: V1__Create_tables.sql
Flyway flyway = Flyway.configure()
    .dataSource("jdbc:postgresql://localhost:5432/mydb", "user", "password")
    .locations("classpath:db/migration")
    .load();

// PostgreSQLParser automatically handles PostgreSQL-specific syntax
flyway.migrate();