phalcon/cphalcon · error · MissingDefinitionKey

The index 'sql' is required in the definition array

Error message

The index 'sql' is required in the definition array

What it means

The PostgreSQL dialect's createMaterializedView() throws MissingDefinitionKey when the definition array has no 'sql' key. A materialized view is just CREATE MATERIALIZED VIEW name AS <query>, so the underlying SELECT under 'sql' is the only required element.

Source

Thrown at phalcon/Db/Dialect/Postgresql.zep:319

        }
        if tableComment {
            let indexSqlAfterCreate .= " COMMENT ON TABLE " . table . " IS '" . str_replace("'", "''", tableComment) . "';";
        }

        let sql .= ";" . indexSqlAfterCreate;

        return sql;
    }

    /**
     * Generates SQL to create a materialized view.
     */
    public function createMaterializedView( string viewName,  array definition, string schemaName = null) -> string
    {
        var viewSql;

        if unlikely !fetch viewSql, definition["sql"] {
            throw new MissingDefinitionKey("sql");
        }

        return "CREATE MATERIALIZED VIEW "
            . this->prepareTable(viewName, schemaName)
            . " AS " . viewSql;
    }

    /**
     * Generates SQL to create a view
     */
    public function createView( string viewName,  array definition, string schemaName = null) -> string
    {
        var viewSql;

        if unlikely !fetch viewSql, definition["sql"] {
            throw new MissingDefinitionKey("sql");
        }

View on GitHub (pinned to b7419de9cd)

Solutions

1. Pass the SELECT under 'sql': $adapter->createMaterializedView('mv_stats', ['sql' => 'SELECT ... GROUP BY ...'])
2. Verify the key name if the definition comes from YAML/JSON config

Example fix

// before
$connection->createMaterializedView('mv_stats', ['query' => 'SELECT count(*) FROM logs']);

// after
$connection->createMaterializedView('mv_stats', ['sql' => 'SELECT count(*) FROM logs']);

if (!isset($definition['sql']) || trim($definition['sql']) === '') {
    throw new InvalidArgumentException('createMaterializedView requires the query under "sql"');
}

function isCreatableViewDefinition(array $definition): bool
{
    return isset($definition['sql']) && is_string($definition['sql']) && trim($definition['sql']) !== '';
}

try {
    $connection->createMaterializedView($name, $definition);
} catch (\Phalcon\Db\Exceptions\MissingDefinitionKey $e) {
    throw new RuntimeException("Cannot create materialized view {$name}: " . $e->getMessage(), 0, $e);
}

When it happens

Trigger: Calling $adapter->createMaterializedView('mv_stats', []) or with the query under a different key ('query', 'select'); helper-generated definitions where the SQL body key is missing for empty materialized views.

Common situations: Report/aggregate pipeline code creating materialized views from templates; refactoring view creation helpers shared between CREATE VIEW and CREATE MATERIALIZED VIEW paths.

Related errors

• The index 'columns' is required in the definition array
• The index 'columns' is required in the definition array
• The index 'sql' is required in the definition array
• Unrecognized PostgreSQL data type at column {}
• RETURNING requires at least one column or '*'

AI-assisted analysis of phalcon/cphalcon@b7419de9cd (2026-08-21). Data as JSON: /api/errors/8860f294f08e5181. Report an issue: GitHub.