Skip to content

Loading…

DBAL-211: wrong where clause in PostgreSqlPlatform::getTableWhereClause #1385

Closed
doctrinebot opened this Issue · 3 comments

2 participants

@doctrinebot

Jira issue originally created by user goetas:

I have the folowing table structure:

Schema "public": with one table called "users"
Schema "forums": with one table called "users"

methods like PostgreSqlPlatform::getListTableForeignKeysSQL($table, $database = '') should list FK inside $table

the default search path is "public,pg_catalog"

calling PostgreSqlPlatform::getListTableForeignKeysSQL('users') it shuld extract the FK from public.users table, but this is the current result:

[PDOException] SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

this exception is thrown because PostgreSqlPlatform::getTableWhereClause do not cosider the current search path.

i propose the following implementation for PostgreSqlPlatform::getTableWhereClause

    private function getTableWhereClause($table, $classAlias = 'c', $namespaceAlias = 'n')
    {
        $whereClause = $namespaceAlias.".nspname NOT IN ('pg*catalog', 'information_schema', 'pg*toast') AND ";
        if (strpos($table, ".") !== false) {
            list($schema, $table) = explode(".", $table);
            $whereClause .= "$classAlias.relname = '" . $table . "' AND $namespaceAlias.nspname = '" . $schema . "'";
        } else {
            // $whereClause .= "$classAlias.relname = '" . $table . "'"; // this was the current implementation
            $whereClause .= "$classAlias.relname = '" . $table . "' AND $namespaceAlias.nspname = ANY(string*to_array((select setting from pg_catalog.pg_settings where name = 'search*path'),','))";
        }

        return $whereClause;
    }

this implementation will restrict the search range only to current "search_path".

(sorry for my english)

@doctrinebot

Comment created by @beberlei:

This looks very good.

@doctrinebot

Comment created by @beberlei:

Fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.1.6 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.