search

phalcon / cphalcon

High performance, full-stack PHP framework delivered as a C extension.
https://phalcon.io
BSD 3-Clause "New" or "Revised" License
10.79k stars 1.96k forks source link

Materialized view #11017

Closed slechtic closed 6 years ago

slechtic commented 9 years ago

Hi, is not possible to use materialized view (Postgre sql 9.4). Maybe the reason is than materialized views are not visible in pg_views. Do you have any advice?

phalcon commented 9 years ago

Are you getting any error or exception?

slechtic commented 9 years ago

exception 'Phalcon\Mvc\Model\Exception' with message 'Table "logs"."view_invoice_log" doesn't exist on database when dumping meta-data for XXX\Domain\Log\InvoiceChangeLog'

slechtic commented 8 years ago

Do you plan to support materialized view in Postgresql?

durrjose commented 8 years ago

I have the same issue. I am evaluating 3 different Frameworks and was especially interested in Phalcon. Phalcon is using the information_schema to query for tables. This does not include materialized views. I found a fix for CakePHP which had the same problem. Here is my notes and modified code from there. (you have to query pg_class as well):

//@original_code //$sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = ?"; //$result = $this->_execute($sql, array($schema));

/* Query rewrite to add support to PostgreSQL materialized views. */ $sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = ?"; $sql .= " UNION SELECT table_name as name FROM INFORMATION_SCHEMA.views WHERE table_schema = ?"; $sql .= "UNION SELECT oid::regclass::text FROM pg_class WHERE relkind = 'm' or relkind = 'v";
$result = $this->_execute($sql, array($schema));

Is this something that can be overwritten via a config change? If so, what method, etc is this possible through?

durrjose commented 8 years ago

currently, from what I can tell, phalcon uses this query to determine if a 'table' exists: SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name='table_name' I work for a rather large company and speed is important, which Phalcon seems to have. I would love a solution to this problem. :)

Jurigag commented 8 years ago

Currently not but feel free to do PR with this behaviour you want.

slechtic commented 8 years ago

I use own \Phalcon\Db\Dialect\PostgreSQL and override 3 methods:

/**
     * Generates SQL checking for the existence of a schema.view
     */
    public function viewExists($viewName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "'";
    }

    /**
     * Generates the SQL to list all views of a schema or user
     *
     * @param string schemaName
     * @return string
     */
    public function listViews($schemaName = null) {
        if ($schemaName) {
            return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = '" . $schemaName . "' ORDER BY view_name";
        }
        return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = 'public' ORDER BY view_name";
    }

    /**
     * Generates SQL checking for the existence of a schema.table
     *
     * <code>
     *    echo $dialect->tableExists("posts", "blog");
     *    echo $dialect->tableExists("posts");
     * </code>
     */
    public function tableExists($tableName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "'";
    }

tableExists check all types (table, view, materialized view) because I use Phalcon\Mvc\Model\MetaData\Strategy\Introspection and there is used tableExists method for reading meta data for all types.

durrjose commented 8 years ago

@slechtic Thanks for the info. I am having trouble implementing this though. What is the best way to do the overwrite? Can you give an example, of making Phalcon use the new PostgreSQL class instead of the old? Thanks in advance.

slechtic commented 8 years ago

You must create a class:

class YourCustomDialect extends \Phalcon\Db\Dialect\PostgreSQL implements \Phalcon\Db\DialectInterface {

    /**
     * Generates SQL checking for the existence of a schema.view
     */
    public function viewExists($viewName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "'";
    }

    /**
     * Generates the SQL to list all views of a schema or user
     *
     * @param string schemaName
     * @return string
     */
    public function listViews($schemaName = null) {
        if ($schemaName) {
            return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = '" . $schemaName . "' ORDER BY view_name";
        }
        return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = 'public' ORDER BY view_name";
    }

    /**
     * Generates SQL checking for the existence of a schema.table
     *
     * <code>
     *    echo $dialect->tableExists("posts", "blog");
     *    echo $dialect->tableExists("posts");
     * </code>
     */
    public function tableExists($tableName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "'";
    }
}

and then set your dialect to your db connection in DI:

$dbAdapter = new DbAdapter(array(
    "host" => $this->config->database->host,
    "username" => $this->config->database->username,
    "password" => $this->config->database->password,
    "dbname" => $this->config->database->dbname
));
$dbAdapter->setDialect(new YourCustomDialect());
durrjose commented 8 years ago

Thank you slechtic, I was missing that I could address the setDialect directly. This got me one step further, but now it is saying that it cannot get column names: Exception: Cannot obtain table columns for the mapped source 'Materialized_view_name' used in model MyModelName.

Did you see this too? and if so, could you share your related methods with me? I am currently looking at the method describeColumns(), but not sure what to return... I tried the following that I found on another site, but still get no results, even though I get results in a direct query on my database... Not sure what it is expecting as a return value... 

public function describeColumns($table, $schema='public')
    {
        return  "SELECT 
                a.attname AS field
                ,t.typname as TYPE
                ,(information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS SIZE
                ,(information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS NumericSize
                ,(information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number AS NumericScale
                ,(CASE WHEN a.attnotnull=false THEN 'YES' ELSE 'NO' END) as null 
                ,(CASE WHEN con.contype IS NOT NULL THEN 'PRI' ELSE '' END)  as key
                , CASE 
                        WHEN t.typname LIKE '%int%' 
                        AND (pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data LIKE '%nextval%' 
                        THEN 'auto_increment' 
                        ELSE '' 
                    END  as Extra
                ,a.attnum as ordinal_position
                ,(pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data as column_default
                ,(CASE WHEN col_description(c.oid,a.attnum) is not null THEN col_description(c.oid,a.attnum) ELSE a.attname END) as description 
                FROM  
                pg_class c
                ,pg_namespace nf
                ,pg_type t
                ,pg_attribute a
                LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))
                LEFT JOIN pg_description d ON d.objsubid = a.attnum AND a.attrelid = d.objoid
                LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[1] = a.attnum

                WHERE nf.nspname = '".$schema."' AND c.relname='".$table."' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND nf.oid = c.relnamespace 
                ORDER BY a.attnum";

    }

Thanks again for all your help.

durrjose commented 8 years ago

P.S. I have not been able to find original phalcon source code to reference, just empty stubs for its methods. Is there a resource available that will show exactly what phalcon is doing in its methods?

Jurigag commented 8 years ago

You mean describeColumns method ?

https://github.com/phalcon/cphalcon/blob/master/phalcon/db/dialect/postgresql.zep#L561

durrjose commented 8 years ago

Thank you Jurigag, This is the source that I was looking for. What is the difference between cphalcon and phalcon? Is one just the source and the other the compiled version? Sorry, new to phalcon and just want to make sure I understand. (never want to just assume.)

Jurigag commented 8 years ago

cphalcon is repository name, phalcon is zep source, ext is c source compiled from zep

durrjose commented 8 years ago

Thanks!

durrjose commented 8 years ago

So I had to add the following describeColumns method to my custom class above to get the column names to work. It seems fine now...

public function describeColumns($table, $schema='public')
{
    if ($schema) {
        return  
            "SELECT DISTINCT
                a.attname AS Field,
                t.typname AS Type,
                (information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS Size,
                (information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS NumericSize,
                (information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS NumericScale,
                (CASE WHEN a.attnotnull=false THEN 'YES' ELSE 'NO' END) as Null, 
                (CASE WHEN con.contype IS NOT NULL THEN 'PRI' ELSE '' END)  as Key,
                CASE  WHEN t.typname LIKE '%int%' AND 
                    (pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data LIKE '%nextval%' 
                    THEN 'auto_increment' 
                    ELSE '' END  as Extra,

                a.attnum as Position

            FROM  
                pg_class c,
                pg_namespace nf,
                pg_type t,
                pg_attribute a
            LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))
            LEFT JOIN pg_description d ON d.objsubid = a.attnum AND a.attrelid = d.objoid
            LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[1] = a.attnum
            WHERE nf.nspname = '$schema' 
                AND c.relname='$table' 
                AND a.attnum > 0 
                AND a.attrelid = c.oid 
                AND a.atttypid = t.oid 
                AND nf.oid = c.relnamespace 
            ORDER BY a.attnum";
    }
    return  
        "SELECT DISTINCT
            a.attname AS Field,
            t.typname AS Type,
            (information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS Size,
            (information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS NumericSize,
            (information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS NumericScale,
            (CASE WHEN a.attnotnull=false THEN 'YES' ELSE 'NO' END) as Null, 
            (CASE WHEN con.contype IS NOT NULL THEN 'PRI' ELSE '' END)  as Key,
            CASE  WHEN t.typname LIKE '%int%' AND 
                (pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data LIKE '%nextval%' 
                THEN 'auto_increment' 
                ELSE '' END  as Extra,

            a.attnum as Position

        FROM  
            pg_class c,
            pg_namespace nf,
            pg_type t,
            pg_attribute a
        LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))
        LEFT JOIN pg_description d ON d.objsubid = a.attnum AND a.attrelid = d.objoid
        LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[1] = a.attnum
        WHERE nf.nspname = 'public' 
            AND c.relname='$table' 
            AND a.attnum > 0 
            AND a.attrelid = c.oid 
            AND a.atttypid = t.oid 
            AND nf.oid = c.relnamespace 
        ORDER BY a.attnum";
 }
durrjose commented 8 years ago

The above describeColumns() did not seem to be returning enough information... I was geting an error missing index: 9. I traced through the code to find the 10th column (array[9]) in the array as being the column's default value. I have added it and now I believe this is correct:

public function describeColumns($table, $schema='public')
{
    if ($schema) {
        return  
            "SELECT 
                a.attname AS Field,
                t.typname AS Type,
                (information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS Size,
                (information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS NumericSize,
      (information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), 
                    information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                    AS NumericScale,
                (CASE WHEN a.attnotnull=false THEN 'YES' ELSE 'NO' END) as Null, 
                (CASE WHEN con.contype IS NOT NULL THEN 'PRI' ELSE '' END)  as Key,
                CASE  WHEN t.typname LIKE '%int%' AND 
                    (pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data LIKE '%nextval%' 
                    THEN 'auto_increment' 
                    ELSE '' END  as Extra,
                a.attnum as Position,
                ad.adsrc as Description
        FROM  
                pg_class c,
                pg_namespace nf,
                pg_type t,
                pg_attribute a
            LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))
            LEFT JOIN pg_description d ON d.objsubid = a.attnum AND a.attrelid = d.objoid
            LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[1] = a.attnum
            WHERE nf.nspname = '$schema' 
                AND c.relname='$table' 
                AND a.attnum > 0 
                AND a.attrelid = c.oid 
                AND a.atttypid = t.oid 
                AND nf.oid = c.relnamespace 
            ORDER BY a.attnum";
    }
    return  
        "SELECT 
            a.attname AS Field,
            t.typname AS Type,
            (information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS Size,
            (information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS NumericSize,                (information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), 
                information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number 
                AS NumericScale,
            (CASE WHEN a.attnotnull=false THEN 'YES' ELSE 'NO' END) as Null, 
            (CASE WHEN con.contype IS NOT NULL THEN 'PRI' ELSE '' END)  as Key,
            CASE  WHEN t.typname LIKE '%int%' AND 
                (pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data LIKE '%nextval%' 
                THEN 'auto_increment' 
                ELSE '' END  as Extra,
            a.attnum as Position,
            ad.adsrc as Description
        FROM  
            pg_class c,
            pg_namespace nf,
            pg_type t,
            pg_attribute a
        LEFT JOIN pg_attrdef ad ON (((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum)))
        LEFT JOIN pg_description d ON d.objsubid = a.attnum AND a.attrelid = d.objoid
        LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[1] = a.attnum
        WHERE nf.nspname = 'public' 
            AND c.relname='$table' 
            AND a.attnum > 0 
            AND a.attrelid = c.oid 
            AND a.atttypid = t.oid 
            AND nf.oid = c.relnamespace 
        ORDER BY a.attnum";
}
stale[bot] commented 6 years ago

Thank you for contributing to this issue. As it has been 90 days since the last activity, we are automatically closing the issue. This is often because the request was already solved in some way and it just wasn't updated or it's no longer applicable. If that's not the case, please feel free to either reopen this issue or open a new one. We will be more than happy to look at it again! You can read more here: https://blog.phalconphp.com/post/github-closing-old-issues

GammaGames commented 2 years ago

Anyone that comes across this very old issue and needs to use Phalcon models with a materialized view:

class CustomPostgreSQLDialect extends \Phalcon\Db\Dialect\PostgreSQL implements \Phalcon\Db\DialectInterface {
    /**
     * Generates SQL checking for the existence of a schema.view
     */
    public function viewExists($viewName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "'";
    }

    /**
     * Generates the SQL to list all views of a schema or user
     */
    public function listViews($schemaName = null) {
        if ($schemaName) {
            return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = '" . $schemaName . "' ORDER BY view_name";
        }
        return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = 'public' ORDER BY view_name";
    }

    /**
     * Generates SQL checking for the existence of a schema.table
     *
     * ```php
     * echo $dialect->tableExists("posts", "blog");
     *
     * echo $dialect->tableExists("posts");
     * ```
     */
    public function tableExists($tableName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "'";
    }

    /**
     * Generates SQL describing a table
     *
     * ```php
     * print_r(
     *     $dialect->describeColumns("posts")
     * );
     * ```
     */
    public function describeColumns($table, $schema = null) {
        if($schema === null) {
            $schema = "public";
        }

        return "WITH original as (
            SELECT distinct
            c.table_schema,
            c.table_name,
            c.column_name AS Field,
            c.data_type AS Type,
            c.character_maximum_length AS Size,
            c.numeric_precision AS NumericSize,
            c.numeric_scale AS NumericScale,
            c.is_nullable AS Null,
            CASE
                WHEN pkc.column_name NOTNULL THEN 'PRI'
            ELSE ''
            END AS Key,
            CASE
                WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment'
                ELSE ''
            END AS Extra,
            c.ordinal_position AS Position,
            c.column_default,
            des.description FROM information_schema.columns c
            LEFT JOIN (
                SELECT kcu.column_name,
                kcu.table_name,
                kcu.table_schema
                FROM information_schema.table_constraints tc
                INNER JOIN information_schema.key_column_usage kcu
                on (kcu.constraint_name = tc.constraint_name AND kcu.table_name=tc.table_name AND kcu.table_schema=tc.table_schema)
                WHERE tc.constraint_type='PRIMARY KEY'
            ) pkc
            ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name)
            LEFT JOIN (
                SELECT objsubid,
                description,
                relname,
                nspname
                FROM pg_description
                JOIN pg_class
                ON pg_description.objoid = pg_class.oid
                JOIN pg_namespace
                ON pg_class.relnamespace = pg_namespace.oid
            ) des
            ON ( des.objsubid = C.ordinal_position AND C.table_schema = des.nspname AND C.TABLE_NAME = des.relname )
        ),
        materialized_views AS (
            SELECT
                s.nspname table_schema,
                t.relname table_name,
                a.attname field,
                pg_catalog.format_type(a.atttypid, NULL) type,
                CASE
                    WHEN a.atttypmod < 0 THEN NULL
                    ELSE a.atttypmod
                END size,
                CASE
                    WHEN a.attlen < 0 THEN NULL
                    ELSE a.attlen
                END numericsize,
                CASE
                    WHEN a.attlen < 0 THEN NULL
                    ELSE 0
                END numericscale,
                'NO' NULL,
                CASE
                    WHEN a.attname = 'id' THEN 'PRI'
                    ELSE ''
                END key,
                '' extra,
                a.attnum position,
                NULL column_default,
                NULL description
            FROM pg_attribute a
            JOIN pg_class t on a.attrelid = t.oid
            JOIN pg_namespace s on t.relnamespace = s.oid
            WHERE a.attnum > 0
            AND NOT a.attisdropped
            and relkind = 'm'
            ORDER BY s.oid, t.oid, a.attnum
        ),
        united AS (
            SELECT *
            FROM original
            UNION
            SELECT *
            FROM materialized_views
        )
        SELECT field, type, size, numericsize, numericscale, null, key, extra, position, column_default, description
        FROM united
        WHERE table_schema='" . $schema . "' AND table_name='" . $table . "' ORDER BY position;";
    }
}

Probably not perfect but it works for now. Got the original query in the CTE from: https://github.com/phalcon/cphalcon/blob/f1315049b46c0e3d348c6446e592c071b901f011/phalcon/Db/Dialect/Postgresql.zep#L297

the-notable commented 1 year ago

Anyone that comes across this very old issue and needs to use Phalcon models with a materialized view:

class CustomPostgreSQLDialect extends \Phalcon\Db\Dialect\PostgreSQL implements \Phalcon\Db\DialectInterface {
    /**
     * Generates SQL checking for the existence of a schema.view
     */
    public function viewExists($viewName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm') AND relname='" . $viewName . "'";
    }

    /**
     * Generates the SQL to list all views of a schema or user
     */
    public function listViews($schemaName = null) {
        if ($schemaName) {
            return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = '" . $schemaName . "' ORDER BY view_name";
        }
        return "SELECT relname AS view_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm') AND n.nspname = 'public' ORDER BY view_name";
    }

    /**
     * Generates SQL checking for the existence of a schema.table
     *
     * ```php
     * echo $dialect->tableExists("posts", "blog");
     *
     * echo $dialect->tableExists("posts");
     * ```
     */
    public function tableExists($tableName, $schemaName = null) {
        if ($schemaName) {
            return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "' AND n.nspname = '" . $schemaName . "'";
        }
        return "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM pg_class c WHERE relkind IN ('v', 'm', 'r') AND relname='" . $tableName . "'";
    }

    /**
     * Generates SQL describing a table
     *
     * ```php
     * print_r(
     *     $dialect->describeColumns("posts")
     * );
     * ```
     */
    public function describeColumns($table, $schema = null) {
        if($schema === null) {
            $schema = "public";
        }

        return "WITH original as (
            SELECT distinct
            c.table_schema,
            c.table_name,
            c.column_name AS Field,
            c.data_type AS Type,
            c.character_maximum_length AS Size,
            c.numeric_precision AS NumericSize,
            c.numeric_scale AS NumericScale,
            c.is_nullable AS Null,
            CASE
                WHEN pkc.column_name NOTNULL THEN 'PRI'
            ELSE ''
            END AS Key,
            CASE
                WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment'
                ELSE ''
            END AS Extra,
            c.ordinal_position AS Position,
            c.column_default,
            des.description FROM information_schema.columns c
            LEFT JOIN (
                SELECT kcu.column_name,
                kcu.table_name,
                kcu.table_schema
                FROM information_schema.table_constraints tc
                INNER JOIN information_schema.key_column_usage kcu
                on (kcu.constraint_name = tc.constraint_name AND kcu.table_name=tc.table_name AND kcu.table_schema=tc.table_schema)
                WHERE tc.constraint_type='PRIMARY KEY'
            ) pkc
            ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name)
            LEFT JOIN (
                SELECT objsubid,
                description,
                relname,
                nspname
                FROM pg_description
                JOIN pg_class
                ON pg_description.objoid = pg_class.oid
                JOIN pg_namespace
                ON pg_class.relnamespace = pg_namespace.oid
            ) des
            ON ( des.objsubid = C.ordinal_position AND C.table_schema = des.nspname AND C.TABLE_NAME = des.relname )
        ),
        materialized_views AS (
            SELECT
                s.nspname table_schema,
                t.relname table_name,
                a.attname field,
                pg_catalog.format_type(a.atttypid, NULL) type,
                CASE
                    WHEN a.atttypmod < 0 THEN NULL
                    ELSE a.atttypmod
                END size,
                CASE
                    WHEN a.attlen < 0 THEN NULL
                    ELSE a.attlen
                END numericsize,
                CASE
                    WHEN a.attlen < 0 THEN NULL
                    ELSE 0
                END numericscale,
                'NO' NULL,
                CASE
                    WHEN a.attname = 'id' THEN 'PRI'
                    ELSE ''
                END key,
                '' extra,
                a.attnum position,
                NULL column_default,
                NULL description
            FROM pg_attribute a
            JOIN pg_class t on a.attrelid = t.oid
            JOIN pg_namespace s on t.relnamespace = s.oid
            WHERE a.attnum > 0
            AND NOT a.attisdropped
            and relkind = 'm'
            ORDER BY s.oid, t.oid, a.attnum
        ),
        united AS (
            SELECT *
            FROM original
            UNION
            SELECT *
            FROM materialized_views
        )
        SELECT field, type, size, numericsize, numericscale, null, key, extra, position, column_default, description
        FROM united
        WHERE table_schema='" . $schema . "' AND table_name='" . $table . "' ORDER BY position;";
    }
}

Probably not perfect but it works for now. Got the original query in the CTE from:

https://github.com/phalcon/cphalcon/blob/f1315049b46c0e3d348c6446e592c071b901f011/phalcon/Db/Dialect/Postgresql.zep#L297

The above was not working for me. Specifically, the union between original and materialized_views was not matching up due to a few missing AS column aliases. The null column was also failing to join. Not sure if it was due to conflict with the reserved keyword. The following is working for me:

WITH original as (
      SELECT distinct
      c.table_schema,
      c.table_name,
      c.column_name AS Field,
      c.data_type AS Type,
      c.character_maximum_length AS Size,
      c.numeric_precision AS NumericSize,
      c.numeric_scale AS NumericScale,
      c.is_nullable AS \"null\",
      CASE
          WHEN pkc.column_name NOTNULL THEN 'PRI'
      ELSE ''
      END AS Key,
      CASE
          WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment'
          ELSE ''
      END AS Extra,
      c.ordinal_position AS Position,
      c.column_default,
      des.description FROM information_schema.columns c
      LEFT JOIN (
          SELECT kcu.column_name,
          kcu.table_name,
          kcu.table_schema
          FROM information_schema.table_constraints tc
          INNER JOIN information_schema.key_column_usage kcu
          on (kcu.constraint_name = tc.constraint_name AND kcu.table_name=tc.table_name AND kcu.table_schema=tc.table_schema)
          WHERE tc.constraint_type='PRIMARY KEY'
      ) pkc
      ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name)
      LEFT JOIN (
          SELECT objsubid,
          description,
          relname,
          nspname
          FROM pg_description
          JOIN pg_class
          ON pg_description.objoid = pg_class.oid
          JOIN pg_namespace
          ON pg_class.relnamespace = pg_namespace.oid
      ) des
      ON ( des.objsubid = C.ordinal_position AND C.table_schema = des.nspname AND C.TABLE_NAME = des.relname )
      ),
      materialized_views AS (
      SELECT
          s.nspname table_schema,
          t.relname table_name,
          a.attname field,
          pg_catalog.format_type(a.atttypid, NULL) as type,
          CASE
              WHEN a.atttypmod < 0 THEN NULL
              ELSE a.atttypmod
          END size,
          CASE
              WHEN a.attlen < 0 THEN NULL
              ELSE a.attlen
          END numericsize,
          CASE
              WHEN a.attlen < 0 THEN NULL
              ELSE 0
          END numericscale,
          'NO' AS \"null\",
          CASE
              WHEN a.attname = 'id' THEN 'PRI'
              ELSE ''
          END as key,
          '' extra,
          a.attnum as position,
          NULL column_default,
          NULL description
      FROM pg_attribute a
      JOIN pg_class t on a.attrelid = t.oid
      JOIN pg_namespace s on t.relnamespace = s.oid
      WHERE a.attnum > 0
      AND NOT a.attisdropped
      and relkind = 'm'
      ORDER BY s.oid, t.oid, a.attnum
      ),
      united AS (
      SELECT *
      FROM original
      UNION
      SELECT *
      FROM materialized_views
      )
      SELECT field, type, size, numericsize, numericscale, \"null\", key, extra, position, column_default, description
      FROM united
      WHERE table_schema='${schema}' AND table_name='${table}' ORDER BY position;