Materialized view columns

[hiendaovinh]

Hi, With the given schema: https://github.com/stephenafamo/bob/blob/main/gen/bobgen-psql/driver/testdatabase.sql, I think it's reasonable to expect bob to generate type_monsters_mv columns just like type_monsters_v but it doesn't. Is it expected behavior? https://github.com/stephenafamo/bob/blob/main/gen/bobgen-psql/driver/psql.golden.json#L2228-L2238 Thank you.

[stephenafamo]

It would be a reasonable expectation, I just haven't gotten to it yet.

Because materialized views are not part of the SQL specification, Postgres does not include information about them in the information_schema.views table.
So querying for details of a materialized view needs a whole new query in the driver.

I'm not sure how soon I'll get to this myself. Of course, I'll be happy to review a PR for this 😁.

[hiendaovinh]

I'll be happy to work on this. Let me test pg_get_viewdef and pg_matviews and see what I can do.

[hiendaovinh]

pg_attribute holds enough information on matview columns. We just need to find a way to map & normalize them to information_schema.

[stephenafamo]

If you can implement this TableDetails function for materialized views, then the rest would be a breeze.

[hiendaovinh]

Yes I'm working on it.

pg_attribute & information_schema.columns

• [x] column_name: attname
• [ ] column_type (Currently returned actual enum "workday" name instead of "ENUM")
• [ ] udt_schema
• [ ] udt_name
• [ ] array_type
• [ ] domain_name
• [ ] column_default
• [ ] column_comment
• [x] is_nullable: attnotnull
• [x] is_generated: attgenerated
• [x] is_identity: attidentity

Will comeback and update this.

[hiendaovinh]

This might also helps: https://github.com/typeorm/typeorm/blob/master/src/driver/postgres/PostgresQueryRunner.ts

[stephenafamo]

Another place to look is in SQLBoiler. https://github.com/volatiletech/sqlboiler/blob/master/drivers/sqlboiler-psql/driver/psql.go#LL392C2-L485

While not expecting exactly the same columns, it is quite similar since Bob was heavily inspired by SQLBoiler.

[stephenafamo]

How's it going? @hiendaovinh

[hiendaovinh]

Sorry, there's no further progress yet because I was kind of busy last week. Will come back this week sir.