yf_db_utils_pgsql class

    public function add_user($name, array $data, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function alter_column($table, $col_name, $data, $extra = [], &$error = false)
    {
        if (is_array($table)) {
            $extra = (array) $extra + $table;
            $table = '';
        }
        if ( ! strlen($table)) {
            $error = 'table name is empty';
            return false;
        }
        if (isset($data['first']) || ($data['after'] ?? null)) {
            $error = 'column position change is not supported on pgsql';
            return false;
        }
        $col_info = $this->column_info($table, $col_name);
        if ( ! $col_info) {
            $error = 'column not exists';
            return false;
        }
        foreach ((array) $data as $k => $v) {
            if (array_key_exists($k, $col_info)) {
                $col_info[$k] = $v;
            }
        }
        $col = $this->db->escape_key($col_name);
        $parts = [];
        if (array_intersect_key((array) $data, ['type' => 1, 'length' => 1, 'decimals' => 1, 'values' => 1])) {
            $info = ['auto_inc' => false] + $col_info;
            $type = $this->_compile_column_type($info, $check);
            if ($type === false || $check) {
                $error = $check ? 'altering to enum is not supported on pgsql' : 'column type is wrong';
                return false;
            }
            $parts[] = 'ALTER COLUMN ' . $col . ' TYPE ' . $type . ' USING ' . $col . '::' . $type;
        }
        if (array_key_exists('nullable', (array) $data)) {
            $parts[] = 'ALTER COLUMN ' . $col . ($col_info['nullable'] ? ' DROP NOT NULL' : ' SET NOT NULL');
        }
        if (array_key_exists('default', (array) $data)) {
            $default = $this->_compile_column_default($col_info);
            $parts[] = 'ALTER COLUMN ' . $col . ($default === '' ? ' DROP DEFAULT' : ' SET ' . $default);
        }
        if ( ! $parts) {
            $error = 'nothing to alter';
            return false;
        }
        $sql = 'ALTER TABLE ' . $this->_escape_table_name($table) . ' ' . implode(', ', $parts);
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function alter_database($db_name, $extra = [], &$error = false)
    {
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        if ( ! isset($extra['conn_limit']) || ! is_numeric($extra['conn_limit'])) {
            $error = 'no supported database options provided';
            return false;
        }
        $sql = 'ALTER DATABASE ' . $this->_escape_database_name($db_name) . ' WITH CONNECTION LIMIT ' . (int) $extra['conn_limit'];
        return ($extra['sql'] ?? null) ? $sql : (bool) $this->db->query($sql);
    }

    public function alter_table($table, $extra = [], &$error = false)
    {
        if (is_array($table)) {
            $extra = (array) $extra + $table;
            $table = '';
        }
        if ( ! $table) {
            $error = 'table_name is empty';
            return false;
        }
        if ( ! isset($extra['comment'])) {
            $error = 'no supported table options provided';
            return false;
        }
        $sql = 'COMMENT ON TABLE ' . $this->_escape_table_name($table) . ' IS ' . $this->_quote($extra['comment']);
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function check_table($table, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function create_database($db_name, $extra = [], &$error = false)
    {
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        if ( ! isset($extra['if_not_exists'])) {
            $extra['if_not_exists'] = true;
        }
        if ($extra['if_not_exists'] && ! ($extra['sql'] ?? null) && $this->database_exists($db_name)) {
            return true;
        }
        $sql = 'CREATE DATABASE ' . $this->_escape_database_name($db_name);
        return ($extra['sql'] ?? null) ? $sql : (bool) $this->db->query($sql);
    }

    public function create_event($name, $event_shedule, $event_body, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function create_function($name, $sql_body, $sql_returns_type, $sql_params = '', $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function create_procedure($name, $sql_body, $sql_params = '', $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function create_trigger($name, $table, $trigger_time, $trigger_event, $trigger_body, $extra = [], &$error = false)
    {
        $db_name = '';
        if (strpos($name, '.') !== false) {
            list($db_name, $name) = explode('.', trim($name), 2);
        }
        if ( ! $name) {
            $error = 'trigger name is empty';
            return false;
        }
        if (strpos($table, '.') !== false) {
            list($db_name, $table) = explode('.', trim($table), 2);
        }
        if ( ! $table) {
            $error = 'trigger table is empty';
            return false;
        }
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be used on pgsql';
            return false;
        }
        if ( ! strlen($trigger_time) || ! in_array(strtolower($trigger_time), ['before', 'after'])) {
            $error = 'trigger time is wrong';
            return false;
        }
        if ( ! strlen($trigger_event) || ! in_array(strtolower($trigger_event), ['insert', 'update', 'delete'])) {
            $error = 'trigger event is wrong';
            return false;
        }
        if ( ! strlen($trigger_body)) {
            $error = 'trigger body is empty';
            return false;
        }
        if (strpos($trigger_body, $this->_trigger_tag) !== false) {
            $error = 'trigger body contains the reserved quote ' . $this->_trigger_tag;
            return false;
        }
        if ( ! preg_match('~^\s*(DECLARE|BEGIN)\b~i', $trigger_body)) {
            $trigger_body = 'BEGIN' . PHP_EOL . $trigger_body . PHP_EOL . 'END';
        }
        $fn = $this->db->escape_key($name . '_fn');
        $sql = 'CREATE OR REPLACE FUNCTION ' . $fn . '() RETURNS trigger LANGUAGE plpgsql AS ' . $this->_trigger_tag . PHP_EOL
            . $trigger_body . PHP_EOL . $this->_trigger_tag . ';' . PHP_EOL
            . 'CREATE TRIGGER ' . $this->db->escape_key($name) . PHP_EOL
            . ' ' . strtoupper($trigger_time) . ' ' . strtoupper($trigger_event) . PHP_EOL
            . ' ON ' . $this->_escape_table_name($table) . ' FOR EACH ROW EXECUTE FUNCTION ' . $fn . '()';
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function database_info($db_name = '', $extra = [], &$error = false)
    {
        if (is_array($db_name)) {
            $extra = (array) $extra + $db_name;
            $db_name = '';
        }
        if ( ! $db_name) {
            $db_name = $this->db->DB_NAME;
        }
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        $info = $this->db->get('SELECT pg_encoding_to_char(encoding) AS charset, datcollate AS collate, datconnlimit AS conn_limit FROM pg_database WHERE datname = ' . $this->_quote($db_name));
        if ( ! $info) {
            $error = 'db_name not exists';
            return false;
        }
        return [
            'name' => $db_name,
            'charset' => $info['charset'],
            'collate' => $info['collate'],
            'conn_limit' => (int) $info['conn_limit'],
        ];
    }

    public function delete_user($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function drop_database($db_name, $extra = [], &$error = false)
    {
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        if ( ! isset($extra['if_exists'])) {
            $extra['if_exists'] = true;
        }
        if ( ! ($extra['sql'] ?? null) && ! $this->database_exists($db_name)) {
            return true;
        }
        if ($db_name === $this->db->DB_NAME) {
            $error = 'the connected database cannot be dropped';
            return false;
        }
        $sql = 'DROP DATABASE ' . ($extra['if_exists'] ? 'IF EXISTS ' : '') . $this->_escape_database_name($db_name);
        return ($extra['sql'] ?? null) ? $sql : (bool) $this->db->query($sql);
    }

    public function drop_event($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function drop_function($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function drop_procedure($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function drop_table($table, $extra = [], &$error = false)
    {
        if (is_array($table)) {
            $extra = (array) $extra + $table;
            $table = '';
        }
        if ( ! $table) {
            $error = 'table_name is empty';
            return false;
        }
        $escaped = $this->_escape_table_name($table);
        if ($escaped === false) {
            $error = 'table "' . $table . '" is not in the connected database';
            return false;
        }
        $sql = 'DROP TABLE IF EXISTS ' . $escaped . ((($extra['cascade'] ?? null) || $this->DEFER_FOREIGN_KEYS) ? ' CASCADE' : '');
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function drop_trigger($name, $extra = [], &$error = false)
    {
        if ( ! strlen($name)) {
            $error = 'name is empty';
            return false;
        }
        $db_name = '';
        if (strpos($name, '.') !== false) {
            list($db_name, $name) = explode('.', trim($name), 2);
        }
        $triggers = $this->list_triggers($db_name, [], $error);
        if ($triggers === false) {
            return false;
        }
        $trigger = $triggers[$name] ?? null;
        if ( ! $trigger) {
            return ($extra['sql'] ?? null) ? '' : true;
        }
        $fn = $name . '_fn';
        $sql = 'DROP TRIGGER IF EXISTS ' . $this->db->escape_key($name) . ' ON ' . $this->db->escape_key($trigger['table']);
        if (stripos($trigger['statement'], 'EXECUTE FUNCTION ' . $fn . '(') !== false) {
            $sql .= ';' . PHP_EOL . 'DROP FUNCTION IF EXISTS ' . $this->db->escape_key($fn) . '()';
        }
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function event_exists($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function event_info($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function function_exists($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function function_info($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_charsets($extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_collations($extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_events($db_name = '', $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_functions($extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_procedures($db_name = '', $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function list_users($extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function optimize_table($table, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function procedure_exists($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function procedure_info($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function rename_column($table, $col_name, $new_name, $extra = [], &$error = false)
    {
        if (is_array($table)) {
            $extra = (array) $extra + $table;
            $table = '';
        }
        if ( ! strlen($table)) {
            $error = 'table name is empty';
            return false;
        }
        if ( ! strlen($col_name) || ! strlen($new_name)) {
            $error = 'column name is empty';
            return false;
        }
        $sql = 'ALTER TABLE ' . $this->_escape_table_name($table) . ' RENAME COLUMN ' . $this->db->escape_key($col_name) . ' TO ' . $this->db->escape_key(strtolower($new_name));
        return ($extra['sql'] ?? null) ? $sql : $this->db->query($sql);
    }

    public function rename_database($db_name, $new_name, $extra = [], &$error = false)
    {
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        if ( ! strlen($new_name)) {
            $error = 'new_name is empty';
            return false;
        }
        if ( ! ($extra['sql'] ?? null)) {
            if ( ! $this->database_exists($db_name)) {
                $error = 'db_name not exists';
                return false;
            }
            if ($this->database_exists($new_name)) {
                $error = 'new database already exists';
                return false;
            }
            if ($db_name === $this->db->DB_NAME) {
                $error = 'the connected database cannot be renamed';
                return false;
            }
        }
        $sql = 'ALTER DATABASE ' . $this->_escape_database_name($db_name) . ' RENAME TO ' . $this->_escape_database_name($new_name);
        return ($extra['sql'] ?? null) ? $sql : (bool) $this->db->query($sql);
    }

    public function repair_table($table, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function truncate_database($db_name, $extra = [], &$error = false)
    {
        if ( ! strlen($db_name)) {
            $error = 'db_name is empty';
            return false;
        }
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be truncated on pgsql';
            return false;
        }
        $sql = [];
        foreach ((array) $this->list_triggers($db_name) as $name => $tmp) {
            $sql[] = $this->drop_trigger($db_name . '.' . $name, $extra);
        }
        foreach ((array) $this->list_views($db_name, ['no_details' => true]) as $name => $tmp) {
            $sql[] = $this->drop_view($db_name . '.' . $name, $extra);
        }
        foreach ((array) $this->list_tables($db_name) as $table) {
            $sql[] = $this->drop_table($db_name . '.' . $table, ['cascade' => true] + (array) $extra);
        }
        return ($extra['sql'] ?? null) ? implode(PHP_EOL, array_filter($sql)) : true;
    }

    public function update_user($name, array $data, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function user_exists($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function user_info($name, $extra = [], &$error = false)
    {
        return $this->_not_supported($error);
    }

    public function _column_row($row)
    {
        $type = (string) $row['Type'];
        if (($row['check_def'] ?? null) && strpos($type, 'character varying') === 0 && preg_match('~ARRAY\[(.+?)\]~', $row['check_def'], $m)) {
            preg_match_all('~\'((?:[^\']|\'\')*)\'::~', $m[1], $values);
            $values = $values[1];
            if ($values && ! preg_grep('~[\',]|\'\'~', $values)) {
                $row['Type'] = 'enum(\'' . implode('\',\'', $values) . '\')';
            }
        }
        if ($row['Default'] !== null) {
            $row['Default'] = $this->_parse_default_expr($row['Default']);
        }
        return $row;
    }

    public function _compile_column_default(array $v)
    {
        $def = $v['default'] ?? null;
        if ($def === null || ! empty($v['auto_inc'])) {
            return '';
        }
        $def = (string) $def;
        if (strtoupper($def) === 'NULL') {
            return 'DEFAULT NULL';
        }
        if (in_array(strtolower($v['type'] ?? ''), ['datetime', 'timestamp', 'date', 'time'])) {
            if (preg_match('~^(?:current_timestamp(?:\(\d*\))?|now\(\)|current_date|localtimestamp)$~i', $def)) {
                return 'DEFAULT CURRENT_TIMESTAMP';
            }
            if (strpos($def, '0000-00-00') === 0) {
                return '';
            }
        }
        return 'DEFAULT ' . $this->_quote($def);
    }

    public function _compile_column_line(array $v)
    {
        $name = strtolower($v['name'] ?? '');
        $type = $this->_compile_column_type(['name' => $name] + $v, $check);
        if ( ! strlen($name) || $type === false) {
            return false;
        }
        $line = $this->db->escape_key($name) . ' ' . $type;
        if (empty($v['auto_inc'])) {
            $line .= ! ($v['nullable'] ?? null) ? ' NOT NULL' : '';
            $default = $this->_compile_column_default($v);
            $line .= strlen($default) ? ' ' . $default : '';
        }
        return $line . ($check ? ' CHECK (' . $check . ')' : '');
    }

    public function _compile_column_type(array $v, &$check = '')
    {
        $check = '';
        $type = strtolower(trim($v['type'] ?? ''));
        $len = isset($v['length']) && is_numeric($v['length']) ? (int) $v['length'] : 0;
        $dec = isset($v['decimals']) && is_numeric($v['decimals']) ? (int) $v['decimals'] : null;
        $auto = ! empty($v['auto_inc']);
        switch ($type) {
            case 'tinyint':
            case 'smallint':
            case 'year':
                return $auto ? 'smallserial' : 'smallint';
            case 'mediumint':
            case 'int':
            case 'integer':
                return $auto ? 'serial' : 'integer';
            case 'bigint':
                return $auto ? 'bigserial' : 'bigint';
            case 'bit':
                return 'bit' . ($len ? '(' . $len . ')' : '');
            case 'bool':
            case 'boolean':
                return 'boolean';
            case 'real':
            case 'float':
                return 'real';
            case 'double':
            case 'double precision':
                return 'double precision';
            case 'decimal':
            case 'numeric':
                return 'numeric' . ($len ? '(' . $len . ($dec !== null ? ',' . $dec : '') . ')' : '');
            case 'varchar':
                return 'varchar(' . ($len ?: 255) . ')';
            case 'char':
                return 'char(' . ($len ?: 1) . ')';
            case 'tinytext':
            case 'mediumtext':
            case 'longtext':
            case 'text':
            case 'set':
                return 'text';
            case 'tinyblob':
            case 'mediumblob':
            case 'longblob':
            case 'blob':
            case 'binary':
            case 'varbinary':
            case 'bytea':
                return 'bytea';
            case 'datetime':
            case 'timestamp':
                return 'timestamp' . ($len && $len <= 6 ? '(' . $len . ')' : '');
            case 'enum':
                $values = array_values(array_filter((array) ($v['values'] ?? []), 'strlen'));
                if ($values) {
                    $in = array_map([$this, '_quote'], $values);
                    $check = $this->db->escape_key(strtolower($v['name'] ?? '')) . ' IN (' . implode(', ', $in) . ')';
                }
                return 'varchar(255)';
        }
        if ( ! preg_match('~^[a-z][a-z0-9_ ]*$~', $type)) {
            return false;
        }
        return $type . ($len ? '(' . $len . ')' : '');
    }

    public function _compile_create_table($data, $extra = [], &$error = false)
    {
        $table = $this->_escape_table_name($data['name'] ?? '');
        if ($table === false || empty($data['fields'])) {
            $error = 'table name or fields are empty';
            return false;
        }
        $plain = strtolower((string) $this->_plain_table_name($data['name']));
        $lines = $after = [];
        foreach ((array) $data['fields'] as $name => $v) {
            $v = (array) $v;
            $v['name'] = ($v['name'] ?? null) ?: $name;
            $line = $this->_compile_column_line($v);
            if ($line === false) {
                $error = 'column definition is wrong: ' . $v['name'];
                return false;
            }
            $lines[] = '  ' . $line;
        }
        foreach ((array) ($data['indexes'] ?? []) as $name => $v) {
            $v = (array) $v;
            $cols = array_values((array) ($v['columns'] ?? []));
            $type = strtolower($v['type'] ?? 'index');
            if ( ! $cols) {
                continue;
            }
            if ($type === 'primary') {
                $lines[] = '  PRIMARY KEY (' . implode(',', $this->db->escape_key(array_map('strtolower', $cols))) . ')';
                continue;
            }
            $name = ($v['name'] ?? null) ?: $name;
            if ( ! strlen($name) || is_numeric($name)) {
                $name = $plain . '_' . implode('_', $cols);
            }
            if ( ! in_array($type, ['index', 'unique', 'fulltext']) || ($type === 'fulltext' && ! $this->FULLTEXT_AS_GIN)) {
                continue;
            }
            $sql = $this->_sql_add_index($data['name'], $name, $type, $cols, $error);
            if ($sql === false) {
                return false;
            }
            $after[] = $sql . ';';
        }
        foreach ((array) ($data['foreign_keys'] ?? []) as $name => $v) {
            $v = (array) $v;
            $actions = [];
            foreach (['on_delete' => 'ON DELETE', 'on_update' => 'ON UPDATE'] as $key => $sql_action) {
                $action = strtoupper(str_replace('_', ' ', (string) ($v[$key] ?? '')));
                if (strlen($action) && ! in_array($action, ['CASCADE', 'RESTRICT', 'NO ACTION', 'SET NULL', 'SET DEFAULT'])) {
                    $error = 'foreign key action is wrong: ' . $name;
                    return false;
                }
                $actions[] = strlen($action) ? ' ' . $sql_action . ' ' . $action : '';
            }
            $cols = implode(',', $this->db->escape_key(array_map('strtolower', array_values((array) $v['columns']))));
            $ref_cols = implode(',', $this->db->escape_key(array_map('strtolower', array_values((array) $v['ref_columns']))));
            $fk_sql = 'CONSTRAINT ' . $this->db->escape_key(strtolower($name)) . ' FOREIGN KEY (' . $cols . ')'
                . ' REFERENCES ' . $this->_escape_ref_table($v['ref_table']) . ' (' . $ref_cols . ')' . implode('', $actions);
            if ($this->DEFER_FOREIGN_KEYS) {
                $this->deferred_sql[] = 'ALTER TABLE ' . $table . ' ADD ' . $fk_sql;
                continue;
            }
            $lines[] = '  ' . $fk_sql;
        }
        if (strlen($data['options']['comment'] ?? '')) {
            $after[] = 'COMMENT ON TABLE ' . $table . ' IS ' . $this->_quote($data['options']['comment']) . ';';
        }
        $sql = 'CREATE TABLE ' . $table . ' (' . PHP_EOL . implode(',' . PHP_EOL, $lines) . PHP_EOL . ');';
        return implode(PHP_EOL, array_merge([$sql], $after));
    }

    public function _ddl_column_line(array $data)
    {
        return $this->_compile_column_line($data);
    }

    public function _ddl_create_sql(array $data, &$error = false)
    {
        return $this->_compile_create_table($data, [], $error);
    }

    public function _es($val = '')
    {
        if ($val === null) {
            return 'NULL';
        }
        $val = trim($val);
        if ( ! strlen($val)) {
            return '';
        }
        return is_object($this->db) && method_exists($this->db, '_es') ? $this->db->_es($val) : str_replace('\'', '\'\'', $val);
    }

    public function _escape_ref_table($name)
    {
        return $this->_escape_table_name($name);
    }

    public function _escape_table_name($name = '')
    {
        $name = str_replace(['\'', '"', '`'], '', trim($name));
        if ( ! strlen($name)) {
            return false;
        }
        $db = '';
        $table = $name;
        if (strpos($name, '.') !== false) {
            list($db, $table) = explode('.', $name, 2);
            $db = trim($db);
            $table = trim($table);
        }
        if ( ! strlen($table) || ! $this->_is_current_db($db)) {
            return false;
        }
        return $this->db->escape_key($this->db->_fix_table_name($table));
    }

    public function _escape_val($val = '')
    {
        if ($val === null || $val === 'NULL') {
            return 'NULL';
        }
        $val = trim(trim($val), '\'');
        if ( ! strlen($val)) {
            return '';
        }
        return $this->_quote($val);
    }

    public function _get_supported_field_types()
    {
        return [
            'bit', 'tinyint', 'smallint', 'mediumint', 'bigint', 'integer', 'int',
            'real', 'float', 'double', 'decimal', 'numeric',
            'varchar', 'char', 'tinytext', 'mediumtext', 'longtext', 'text',
            'tinyblob', 'mediumblob', 'longblob', 'blob', 'varbinary', 'binary', 'bytea',
            'timestamp', 'datetime', 'time', 'date', 'year',
            'boolean', 'json', 'jsonb', 'uuid',
            'enum', 'set',
        ];
    }

    public function _get_supported_table_options()
    {
        return [
            'comment' => 'COMMENT',
        ];
    }

    public function _get_unsigned_field_types()
    {
        return [];
    }

    public function _index_pg_name($table, $name)
    {
        $plain = strtolower((string) $this->_plain_table_name($table));
        $name = strtolower($name);
        return strpos($name, $plain . '_') === 0 ? $name : $plain . '_' . $name;
    }

    public function _is_current_db($db_name)
    {
        return ! strlen($db_name) || $db_name === $this->db->DB_NAME;
    }

    public function _not_supported(&$error = false)
    {
        $error = 'not supported on pgsql';
        return false;
    }

    public function _parse_column_type($str, &$error = false)
    {
        $str = preg_replace(['~\s+with(?:out)?\s+time\s+zone~i', '~\s+(?:unsigned|signed|zerofill)\b~i', '~\[\]$~'], '', trim($str));
        $type = $length = '';
        $decimals = $values = null;
        if (preg_match('~^(?P<type>[a-z][a-z0-9_ ]*?)\s*(?:\((?P<length>.*)\))?$~is', $str, $m)) {
            $type = strtolower(trim($m['type']));
            $length = $m['length'] ?? '';
        } elseif (preg_match('~^[a-z][a-z0-9_]*~i', $str, $m)) {
            $type = strtolower($m[0]);
        }
        $aliases = [
            'character varying' => 'varchar', 'character' => 'char', 'bpchar' => 'char', 'integer' => 'int', 'int4' => 'int',
            'int2' => 'smallint', 'int8' => 'bigint', 'double precision' => 'double', 'float8' => 'double', 'float4' => 'real',
            'bool' => 'boolean', 'serial' => 'int', 'serial4' => 'int', 'bigserial' => 'bigint', 'serial8' => 'bigint',
            'smallserial' => 'smallint', 'serial2' => 'smallint', 'timestamptz' => 'timestamp', 'timetz' => 'time',
            'bit varying' => 'bit', 'varbit' => 'bit',
        ];
        $type = $aliases[$type] ?? $type;
        if (in_array($type, ['enum', 'set']) && strlen($length)) {
            $values = [];
            foreach (explode(',', $length) as $v) {
                $v = trim(trim(trim($v), '\'"'));
                if (strlen($v)) {
                    $values[$v] = $v;
                }
            }
            $length = '';
        } elseif (strlen($length) && ! is_numeric($length) && false !== strpos($length, ',')) {
            if (in_array($type, ['real', 'double', 'float', 'decimal', 'numeric'])) {
                list($length, $decimals) = explode(',', $length);
                $length = (int) trim($length);
                $decimals = (int) trim($decimals);
            }
        }
        return [
            'type' => $type,
            'length' => strlen($length) ? $length : null,
            'unsigned' => false,
            'decimals' => $decimals,
            'values' => $values,
        ];
    }

    public function _parse_default_expr($expr)
    {
        $expr = trim($expr);
        if (preg_match('~^\'((?:[^\']|\'\')*)\'(?:::[a-z0-9_ ]+(?:\(\d+(?:,\d+)?\))?(?:\[\])?)?$~is', $expr, $m)) {
            return str_replace('\'\'', '\'', $m[1]);
        }
        if (preg_match('~^NULL(?:::.+)?$~i', $expr) || stripos($expr, 'nextval(') === 0) {
            return null;
        }
        if (preg_match('~^\(?(-?\d+(?:\.\d+)?)\)?(?:::[a-z0-9_ ]+)?$~i', $expr, $m)) {
            return $m[1];
        }
        if (preg_match('~^(?:now\(\)|current_timestamp(?:\(\d*\))?|transaction_timestamp\(\))$~i', $expr)) {
            return 'CURRENT_TIMESTAMP';
        }
        return $expr;
    }

    public function _plain_table_name($table)
    {
        $table = trim(str_replace(['\'', '"', '`'], '', $table));
        $db = '';
        if (strpos($table, '.') !== false) {
            list($db, $table) = explode('.', $table, 2);
        }
        if ( ! strlen($table) || ! $this->_is_current_db(trim($db))) {
            return false;
        }
        return $this->db->_fix_table_name(trim($table));
    }

    public function _quote($val)
    {
        return '\'' . $this->db->es((string) $val) . '\'';
    }

    public function _sql_add_index($table, $index_name, $index_type, array $fields, &$error = false)
    {
        $t = $this->_escape_table_name($table);
        $cols = $this->db->escape_key(array_map('strtolower', array_values($fields)));
        if ($index_name == 'PRIMARY' || $index_type == 'primary') {
            return 'ALTER TABLE ' . $t . ' ADD PRIMARY KEY (' . implode(',', $cols) . ')';
        }
        $name = $this->db->escape_key($this->_index_pg_name($table, $index_name));
        if ($index_type == 'fulltext') {
            if ( ! $this->FULLTEXT_AS_GIN) {
                $error = 'fulltext index is not supported on pgsql, set FULLTEXT_AS_GIN to build a GIN index';
                return false;
            }
            $docs = [];
            foreach ($cols as $col) {
                $docs[] = 'coalesce(' . $col . ', \'\')';
            }
            return 'CREATE INDEX ' . $name . ' ON ' . $t . ' USING gin (to_tsvector(\'simple\', ' . implode(' || \' \' || ', $docs) . '))';
        }
        return 'CREATE ' . ($index_type == 'unique' ? 'UNIQUE ' : '') . 'INDEX ' . $name . ' ON ' . $t . ' (' . implode(',', $cols) . ')';
    }

    public function _sql_columns($table)
    {
        return 'SELECT a.attname AS "Field",
				pg_catalog.format_type(a.atttypid, a.atttypmod) AS "Type",
				CASE WHEN a.attnotnull THEN \'NO\' ELSE \'YES\' END AS "Null",
				pg_get_expr(d.adbin, d.adrelid) AS "Default",
				CASE WHEN a.attgenerated <> \'\' THEN \'stored generated\'
					WHEN a.attidentity <> \'\' OR pg_get_expr(d.adbin, d.adrelid) LIKE \'nextval(%\' THEN \'auto_increment\'
					ELSE \'\' END AS "Extra",
				CASE WHEN EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = a.attrelid AND i.indisprimary AND a.attnum = ANY (i.indkey))
					THEN \'PRI\' ELSE \'\' END AS "Key",
				NULL::text AS "Collation",
				(SELECT pg_get_constraintdef(k.oid) FROM pg_constraint k
					WHERE k.conrelid = a.attrelid AND k.contype = \'c\' AND k.conkey = ARRAY[a.attnum] LIMIT 1) AS "check_def"
			FROM pg_attribute a
				JOIN pg_class c ON c.oid = a.attrelid
				JOIN pg_namespace n ON n.oid = c.relnamespace
				LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
			WHERE c.relname = ' . $this->_quote($this->_plain_table_name($table)) . '
				AND n.nspname = current_schema() AND c.relkind IN (\'r\', \'p\', \'v\', \'m\', \'f\')
				AND a.attnum > 0 AND NOT a.attisdropped
			ORDER BY a.attnum';
    }

    public function _sql_drop_foreign_key($table, $index_name)
    {
        return 'ALTER TABLE ' . $this->_escape_table_name($table) . ' DROP CONSTRAINT ' . $this->db->escape_key($index_name);
    }

    public function _sql_drop_index($table, $index_name)
    {
        $t = $this->_escape_table_name($table);
        $plain = $this->_plain_table_name($table);
        $sql = 'SELECT k.conname FROM pg_constraint k JOIN pg_class c ON c.oid = k.conrelid JOIN pg_namespace n ON n.oid = c.relnamespace
			WHERE c.relname = ' . $this->_quote($plain) . ' AND n.nspname = current_schema() AND k.contype IN (\'p\', \'u\', \'x\')';
        if ($index_name == 'PRIMARY') {
            $constraint = $this->db->get_one($sql . ' AND k.contype = \'p\'') ?: $plain . '_pkey';
            return 'ALTER TABLE ' . $t . ' DROP CONSTRAINT ' . $this->db->escape_key($constraint);
        }
        $pg_name = $this->_index_pg_name($table, $index_name);
        $exists = $this->db->get_one('SELECT 1 FROM pg_class WHERE relkind = \'i\' AND relnamespace = current_schema()::regnamespace AND relname = ' . $this->_quote($pg_name));
        $name = $exists ? $pg_name : $index_name;
        if ($this->db->get_one($sql . ' AND k.conname = ' . $this->_quote($name))) {
            return 'ALTER TABLE ' . $t . ' DROP CONSTRAINT ' . $this->db->escape_key($name);
        }
        return 'DROP INDEX ' . $this->db->escape_key($name);
    }

    public function _sql_foreign_key_rules($db_name, $table)
    {
        $rule = function ($col) {
            return 'CASE con.' . $col . ' WHEN \'a\' THEN \'NO ACTION\' WHEN \'r\' THEN \'RESTRICT\' WHEN \'c\' THEN \'CASCADE\'
				WHEN \'n\' THEN \'SET NULL\' ELSE \'SET DEFAULT\' END';
        };
        return 'SELECT con.conname AS "CONSTRAINT_NAME", ' . $rule('confupdtype') . ' AS "UPDATE_RULE", ' . $rule('confdeltype') . ' AS "DELETE_RULE"
			FROM pg_constraint con
				JOIN pg_class c ON c.oid = con.conrelid
				JOIN pg_namespace n ON n.oid = c.relnamespace
			WHERE con.contype = \'f\' AND c.relname = ' . $this->_quote($this->db->_fix_table_name($table)) . '
				AND n.nspname = current_schema() AND current_database() = ' . $this->_quote($db_name);
    }

    public function _sql_foreign_keys($db_name, $table)
    {
        return 'SELECT con.conname AS "CONSTRAINT_NAME", att.attname AS "COLUMN_NAME",
				rc.relname AS "REFERENCED_TABLE_NAME", ratt.attname AS "REFERENCED_COLUMN_NAME"
			FROM pg_constraint con
				JOIN pg_class c ON c.oid = con.conrelid
				JOIN pg_namespace n ON n.oid = c.relnamespace
				JOIN pg_class rc ON rc.oid = con.confrelid
				CROSS JOIN LATERAL unnest(con.conkey, con.confkey) WITH ORDINALITY AS k(attnum, refnum, ord)
				JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = k.attnum
				JOIN pg_attribute ratt ON ratt.attrelid = con.confrelid AND ratt.attnum = k.refnum
			WHERE con.contype = \'f\' AND c.relname = ' . $this->_quote($this->db->_fix_table_name($table)) . '
				AND n.nspname = current_schema() AND current_database() = ' . $this->_quote($db_name) . '
			ORDER BY con.conname, k.ord';
    }

    public function _sql_list_databases()
    {
        return 'SELECT datname, datname FROM pg_database WHERE datistemplate = false ORDER BY datname';
    }

    public function _sql_list_indexes($table, $db_name = '')
    {
        return 'SELECT CASE WHEN i.indisprimary THEN \'PRIMARY\'
					WHEN left(ic.relname, length(tc.relname) + 1) = tc.relname || \'_\' THEN substr(ic.relname, length(tc.relname) + 2)
					ELSE ic.relname END AS "Key_name",
				CASE WHEN i.indisunique THEN 0 ELSE 1 END AS "Non_unique",
				CASE WHEN am.amname = \'gin\' AND pg_get_indexdef(i.indexrelid) LIKE \'%to_tsvector%\' THEN \'FULLTEXT\' ELSE \'BTREE\' END AS "Index_type",
				pg_get_indexdef(i.indexrelid, k.ord::int, true) AS "Column_name"
			FROM pg_index i
				JOIN pg_class ic ON ic.oid = i.indexrelid
				JOIN pg_class tc ON tc.oid = i.indrelid
				JOIN pg_namespace n ON n.oid = tc.relnamespace
				JOIN pg_am am ON am.oid = ic.relam
				CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS k(attnum, ord)
			WHERE tc.relname = ' . $this->_quote($this->_plain_table_name($table)) . '
				AND n.nspname = current_schema() AND current_database() = ' . $this->_quote($db_name ?: $this->db->DB_NAME) . '
				AND k.ord <= i.indnkeyatts
			ORDER BY (NOT i.indisprimary), ic.relname, k.ord';
    }

    public function _sql_list_tables($db_name, &$error = false)
    {
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be read on pgsql';
            return false;
        }
        return 'SELECT table_name, table_type FROM information_schema.tables
			WHERE table_catalog = current_database() AND table_schema = current_schema()
			ORDER BY table_name';
    }

    public function _sql_list_triggers($db_name, &$error = false)
    {
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be read on pgsql';
            return false;
        }
        return 'SELECT trigger_name AS "Trigger", event_object_table AS "Table", event_manipulation AS "Event",
				action_timing AS "Timing", action_statement AS "Statement"
			FROM information_schema.triggers
			WHERE trigger_catalog = current_database() AND trigger_schema = current_schema()
			ORDER BY trigger_name, event_manipulation';
    }

    public function _sql_list_views($db_name, &$error = false)
    {
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be read on pgsql';
            return false;
        }
        return 'SELECT table_name FROM information_schema.tables
			WHERE table_catalog = current_database() AND table_schema = current_schema() AND table_type = \'VIEW\'
			ORDER BY table_name';
    }

    public function _sql_rename_table($table, $new_name)
    {
        $from = $this->_escape_table_name($table);
        $to = $this->_escape_table_name($new_name);
        return $from && $to ? 'ALTER TABLE ' . $from . ' RENAME TO ' . $to : false;
    }

    public function _sql_table_charset($db_name, $table)
    {
        return 'SELECT pg_encoding_to_char(encoding) AS character_set_name FROM pg_database WHERE datname = current_database()';
    }

    public function _sql_table_status($db_name, $table)
    {
        return 'SELECT NULL::text AS "Row_format", NULL::text AS "Collation", NULL::text AS "Engine",
				CASE WHEN c.reltuples < 0 THEN 0 ELSE c.reltuples::bigint END AS "Rows",
				pg_relation_size(c.oid) AS "Data_length", NULL::bigint AS "Auto_increment",
				COALESCE(obj_description(c.oid, \'pg_class\'), \'\') AS "Comment",
				NULL::text AS "Create_time", NULL::text AS "Update_time"
			FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
			WHERE c.relname = ' . $this->_quote($this->db->_fix_table_name($table)) . '
				AND n.nspname = current_schema() AND current_database() = ' . $this->_quote($db_name) . '
				AND c.relkind IN (\'r\', \'p\')';
    }

    public function _sql_tables_status($db_name, &$error = false)
    {
        if ( ! $this->_is_current_db($db_name)) {
            $error = 'only the connected database can be read on pgsql';
            return false;
        }
        return 'SELECT c.relname AS "Name", NULL::text AS "Engine",
				CASE WHEN c.reltuples < 0 THEN 0 ELSE c.reltuples::bigint END AS "Rows",
				pg_relation_size(c.oid) AS "Data_length", NULL::text AS "Collation"
			FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
			WHERE n.nspname = current_schema() AND c.relkind IN (\'r\', \'p\')
			ORDER BY c.relname';
    }

    public function _sql_truncate_table($table)
    {
        $escaped = $this->_escape_table_name($table);
        return $escaped ? 'TRUNCATE TABLE ' . $escaped . ' RESTART IDENTITY' : false;
    }

    public function _sql_view_definition($db_name, $name)
    {
        return 'SELECT \'CREATE VIEW "\' || replace(c.relname, \'"\', \'""\') || \'" AS \' || ltrim(rtrim(pg_get_viewdef(c.oid, true), \';\')) AS "Create View"
			FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
			WHERE c.relname = ' . $this->_quote($name) . ' AND c.relkind = \'v\' AND n.nspname = current_schema()';
    }