On Mon, Aug 16, 2021 at 7:01 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Peter Smith <smithpb2...@gmail.com> writes: > > Then the question from Peter E. [2] "Why can't I have a publication > > that publishes tables t1, t2, t3, *and* schemas s1, s2, s3." would > > have an intuitive solution like: > > > CREATE PUBLICATION pub1 > > FOR TABLE t1,t2,t3 AND > > FOR ALL TABLES IN SCHEMA s1,s2,s3; > > That seems a bit awkward, since the existing precedent is > to use commas. >
AFAICS, the closest to this proposal we have is Grant/Revoke syntax where we can give privilege on individual objects and all objects in the schema. Is that you are referring to existing precedent or something else? > We shouldn't need more than one FOR noise-word, > either. So I was imagining syntax more like, say, > > CREATE PUBLICATION pub1 FOR > TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2, > SEQUENCE seq1,seq2, ALL SEQUENCES IN SCHEMA s3,s4; > > Abstractly it'd be > > createpub := CREATE PUBLICATION pubname FOR cpitem [, ... ] [ WITH ... ] > > cpitem := ALL TABLES | > TABLE name | > ALL TABLES IN SCHEMA name | > ALL SEQUENCES | > SEQUENCE name | > ALL SEQUENCES IN SCHEMA name | > name > > The grammar output would need some post-analysis to attribute the > right type to bare "name" items, but that doesn't seem difficult. > The current patch (v26-0002-Added-schema-level-support-for-publication at [1]) implements this syntax in roughly the way you have proposed here. But, one thing I find a bit awkward is how it needs to keep a separate flag to distinguish between names of different objects for the post-analysis phase. The reason is that in CREATE PUBLICATION syntax [2] one could supply additional decorators like *, ONLY with table name but the same shouldn't be allowed be with schema name or other object names. Is that okay or do you have any better ideas about the same? OTOH, if we implement something like Grant/Revoke where we can give privilege on individual objects and all objects in the schema but not in the same statement then such special flags won't be required to distinguish different object names and we can build something on the lines of current "privilege_target:" in gram.y. [1] - https://www.postgresql.org/message-id/CALDaNm3EwAVma8n4YpV1%2BQWiccuVPxpqNfbbrUU3s3XTHcTXew%40mail.gmail.com [2] - https://www.postgresql.org/docs/devel/sql-createpublication.html -- With Regards, Amit Kapila.
