Thread: default values for views
The attached patch allows views to have default values. You can't specify a default value within a CREATE VIEW statement, it must be done using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the view has already been created. Most of the hard work was done by Tom Lane, I just patched pg_dump and updated the documentation. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Attachment
Neil Conway <nconway@klamath.dyndns.org> writes:
+ if (tblinfo[i].adef_expr[j] != NULL && tblinfo[i].inhAttrDef[j] == 0)
+ appendPQExpBuffer(q, "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;\n",
+ tblinfo[i].relname, tblinfo[i].attnames[j],
+ tblinfo[i].adef_expr[j]);
I think you need some fmtId calls here, else it will fail on mixed-case
names and suchlike.
regards, tom lane
On Mon, 15 Apr 2002 18:24:43 -0400 "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Neil Conway <nconway@klamath.dyndns.org> writes: > + if (tblinfo[i].adef_expr[j] != NULL && tblinfo[i].inhAttrDef[j] == 0) > + appendPQExpBuffer(q, "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;\n", > + tblinfo[i].relname, tblinfo[i].attnames[j], > + tblinfo[i].adef_expr[j]); > > I think you need some fmtId calls here, else it will fail on mixed-case > names and suchlike. Ah, thanks for spotting that, Tom -- sorry for being careless. A revised patch is attached. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Attachment
Neil Conway wrote: > The attached patch allows views to have default values. You can't > specify a default value within a CREATE VIEW statement, it must be > done using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the > view has already been created. Most of the hard work was done by > Tom Lane, I just patched pg_dump and updated the documentation. I am confused. I didn't think we could insert into views. Can we? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, 15 Apr 2002 18:59:17 -0400 (EDT) "Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > Neil Conway wrote: > > The attached patch allows views to have default values. You can't > > specify a default value within a CREATE VIEW statement, it must be > > done using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the > > view has already been created. Most of the hard work was done by > > Tom Lane, I just patched pg_dump and updated the documentation. > > I am confused. I didn't think we could insert into views. Can we? No, but you can if you create a rule to handle the insertion for you properly. There is more discussion of this patch on -hackers, in the thread "rules and default values". Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Neil Conway wrote: > On Mon, 15 Apr 2002 18:59:17 -0400 (EDT) > "Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > > Neil Conway wrote: > > > The attached patch allows views to have default values. You can't > > > specify a default value within a CREATE VIEW statement, it must be > > > done using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the > > > view has already been created. Most of the hard work was done by > > > Tom Lane, I just patched pg_dump and updated the documentation. > > > > I am confused. I didn't think we could insert into views. Can we? > > No, but you can if you create a rule to handle the insertion for you > properly. > > There is more discussion of this patch on -hackers, in the thread > "rules and default values". Oh, so somehow this patch interacts with the rule insertion code to handle it. OK. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Mon, 15 Apr 2002 18:45:48 -0400 "Neil Conway" <nconway@klamath.dyndns.org> wrote: > On Mon, 15 Apr 2002 18:24:43 -0400 > "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > Neil Conway <nconway@klamath.dyndns.org> writes: > > + if (tblinfo[i].adef_expr[j] != NULL && tblinfo[i].inhAttrDef[j] == 0) > > + appendPQExpBuffer(q, "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;\n", > > + tblinfo[i].relname, tblinfo[i].attnames[j], > > + tblinfo[i].adef_expr[j]); > > > > I think you need some fmtId calls here, else it will fail on mixed-case > > names and suchlike. > > Ah, thanks for spotting that, Tom -- sorry for being careless. > > A revised patch is attached. Apparently, you need to make two calls to appendPQExpBuffer() to use fmtId() twice, because it uses a static buffer (thanks for spotting this Tom). Another revision of the patch is attached. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Attachment
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Oh, so somehow this patch interacts with the rule insertion code to
> handle it. OK.
Right, the idea is to make an ON INSERT rule for a view act as though
there were default values for the columns of the view.
regards, tom lane
Neil Conway <nconway@klamath.dyndns.org> writes:
> Apparently, you need to make two calls to appendPQExpBuffer() to
> use fmtId() twice, because it uses a static buffer (thanks for
> spotting this Tom).
I think people have been bit by that before. It's fairly easy to miss
at present, because fmtId only uses the static buffer if it decides to
quote the name. If your test case doesn't exercise that path, you'll
not notice the problem.
We could improve the odds at a cost of a small number of cycles, by
having fmtId copy the name into its static buffer even if it doesn't
need to quote. Then the buffer would always be used, and erroneous
reuse of fmtId should be pretty obvious.
(In case anyone's about to suggest that we avoid the problem by
malloc'ing the result, the trouble with that approach is it means
memory leakage --- at least unless the call sites are uglified
quite a lot to free the returned strings after use. I like this
way better; but we should try to make it a tad less error-prone.)
Any objections?
regards, tom lane
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Neil Conway wrote:
> On Mon, 15 Apr 2002 18:45:48 -0400
> "Neil Conway" <nconway@klamath.dyndns.org> wrote:
> > On Mon, 15 Apr 2002 18:24:43 -0400
> > "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> > > Neil Conway <nconway@klamath.dyndns.org> writes:
> > > + if (tblinfo[i].adef_expr[j] != NULL && tblinfo[i].inhAttrDef[j] == 0)
> > > + appendPQExpBuffer(q, "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;\n",
> > > + tblinfo[i].relname, tblinfo[i].attnames[j],
> > > + tblinfo[i].adef_expr[j]);
> > >
> > > I think you need some fmtId calls here, else it will fail on mixed-case
> > > names and suchlike.
> >
> > Ah, thanks for spotting that, Tom -- sorry for being careless.
> >
> > A revised patch is attached.
>
> Apparently, you need to make two calls to appendPQExpBuffer() to
> use fmtId() twice, because it uses a static buffer (thanks for
> spotting this Tom).
>
> Another revision of the patch is attached.
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Patch applied. Thanks. --------------------------------------------------------------------------- Neil Conway wrote: > The attached patch allows views to have default values. You can't > specify a default value within a CREATE VIEW statement, it must be > done using ALTER TABLE ... ALTER COLUMN ... SET DEFAULT after the > view has already been created. Most of the hard work was done by > Tom Lane, I just patched pg_dump and updated the documentation. > > Cheers, > > Neil > > -- > Neil Conway <neilconway@rogers.com> > PGP Key ID: DB3C29FC [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Oops, previous version backed out. New patch applied. Doc patch again already covered in the current SGML. --------------------------------------------------------------------------- Neil Conway wrote: > On Mon, 15 Apr 2002 18:45:48 -0400 > "Neil Conway" <nconway@klamath.dyndns.org> wrote: > > On Mon, 15 Apr 2002 18:24:43 -0400 > > "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > > Neil Conway <nconway@klamath.dyndns.org> writes: > > > + if (tblinfo[i].adef_expr[j] != NULL && tblinfo[i].inhAttrDef[j] == 0) > > > + appendPQExpBuffer(q, "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s;\n", > > > + tblinfo[i].relname, tblinfo[i].attnames[j], > > > + tblinfo[i].adef_expr[j]); > > > > > > I think you need some fmtId calls here, else it will fail on mixed-case > > > names and suchlike. > > > > Ah, thanks for spotting that, Tom -- sorry for being careless. > > > > A revised patch is attached. > > Apparently, you need to make two calls to appendPQExpBuffer() to > use fmtId() twice, because it uses a static buffer (thanks for > spotting this Tom). > > Another revision of the patch is attached. > > Cheers, > > Neil > > -- > Neil Conway <neilconway@rogers.com> > PGP Key ID: DB3C29FC [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026