Re: CREATE SCHEMA ... CREATE M.V. support

Hello Kirill

On 2024-Nov-11, Kirill Reshke wrote:

> I was exploring the PostgreSQL parser and discovered a very
> interesting feature. Users can create schema along with schema objects
> in single SQL.

Yeah, it's pretty cool.

> Support for materialized views began in 9.3. Perhaps, then, this is
> simply something that was overlooked for support?

Yeah, I don't know why but people seem generally uninterested in
expanding support of commands under CREATE SCHEMA, which I think is a
pity. However, keep in mind that the set of commands allowed is
dictated by the SQL standard, which says

<schema definition> ::=
CREATE SCHEMA <schema name clause>
[ <schema character set or path> ]
[ <schema element>... ]

<schema element> ::=
<table definition>
| <view definition>
| <domain definition>
| <character set definition>
| <collation definition>
| <transliteration definition>
| <assertion definition>
| <trigger definition>
| <user-defined type definition>
| <user-defined cast definition>
| <user-defined ordering definition>
| <transform definition>
| <schema routine>
| <sequence generator definition>
| <grant statement>
| <role definition>

Materialized views are not in the SQL standard, so if we do decide to
support them under CREATE SCHEMA, it would be an extension to the
standard. (IMO it's quite a natural one at that.)

> It appears that supporting this is as simple as changing this parser's
> non-terminal [2]. However, perhaps there are justifications for why we
> forbid this?
>
> [2] https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/parser/gram.y?h=14e87ffa5c543b5f30ead7413084c25f7735039f#n1580

I don't think so, or at least I'm not aware of them.

Looking at the object list in the standard document, it looks like we're
missing quite some stuff there.

> P.S. is this the correct place to make this question?

No, this is more a pgsql-hackers question.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)