Feedback-ID: ia2694551:Fastmail
Date: Mon, 11 Nov 2024 11:09:53 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: CREATE SCHEMA ... CREATE M.V. support
Message-ID: <202411111009.ckna4vp7ahyk@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CALdSSPgxcRkooZ2iQ5A7XhYoexVAdbiT6znZDqJTE8hxUVjz_A@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202411111009.ckna4vp7ahyk%40alvherre.pgsql>
Precedence: bulk

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)