Hi all

Is it too late to add a feature to pg_dump for 6.4??

I just spent most of the day learning pg_dump and modifing it so it would
dump views also.

This is the first time I have ever contributed any code changes, so I'm
not sure of how to submit it.

The diff's and a readme as a tgz file are attached.

Thanks
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!
Sat, 3 Oct 1998 16:25:30 -0400 (EDT)
Terry Mackintosh <terry@terrym.com>
Dumping of views -- done!
> Hi all
> 
> Is it too late to add a feature to pg_dump for 6.4??
> 
> I just spent most of the day learning pg_dump and modifing it so it would
> dump views also.
> 
> This is the first time I have ever contributed any code changes, so I'm
> not sure of how to submit it.
> 
> The diff's and a readme as a tgz file are attached.
> 
> Thanks
> Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
> sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

I though we dumped views already.  If not, I am inclinded to apply it,
but want to see what others say.  I thought Jan was working on this, or
was that just rewrite rules?  Are you using his code?  I know there is
an item on the Open Items list that talks about this:

	have psql dump out rules text with new function

Is that what this is?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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


Sat, 3 Oct 1998 21:06:44 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Dumping of views -- done!
Hi Bruce

On Sat, 3 Oct 1998, Bruce Momjian wrote:

> > Hi all
> > 
> > Is it too late to add a feature to pg_dump for 6.4??

> I though we dumped views already.  If not, I am inclinded to apply it,

OH, ... well, if it does, I sure would like to know how, I missed it.

> but want to see what others say.  I thought Jan was working on this, or
> was that just rewrite rules?  Are you using his code?  I know there is

I tryed to stay as much in style with the code that was there as possible.

> an item on the Open Items list that talks about this:
> 
> 	have psql dump out rules text with new function
> Is that what this is?

I'm not sure that is the same, what my change does is add:
CREATE VIEW viewname AS select clause......;
to the dump file.

I did notice that if used with '-z' it also puts the revoke grant
statments there too.  Not my doing, just nice.

At this time there are no provisions (by me any way) to dump any
associated update/delete rules on the view.  Unless, like -z above, it
already works?

To the best of my knowlage this feature does not yet exist, if any one
else is working on it, I don't know.

> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@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
> 

Have a great day
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!


Sat, 3 Oct 1998 22:49:14 -0400 (EDT)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] Dumping of views -- done!
Hi Bruce
 
On Sat, 3 Oct 1998, Bruce Momjian wrote:

> I though we dumped views already.  If not, I am inclinded to apply it,

Your comment gave me cause to go read the pg_dump man page again, here is
what it has to say:

       rules and views
              pg_dump  does not understand user-defined rules and
              views and will fail to dump them  properly.   (This
              is  due  to the fact that rules are stored as plans
              in the catalogs and not textually)


Now, I did not (yet) do any thing about rules, only views.

Have a great day
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!


Sat, 3 Oct 1998 23:11:04 -0400 (EDT)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] Dumping of views -- done!
On Sat, 3 Oct 1998, Bruce Momjian wrote:

> > Hi all
> > 
> > Is it too late to add a feature to pg_dump for 6.4??
> > 
> > I just spent most of the day learning pg_dump and modifing it so it would
> > dump views also.
> > 
> > This is the first time I have ever contributed any code changes, so I'm
> > not sure of how to submit it.
> > 
> > The diff's and a readme as a tgz file are attached.
> > 
> > Thanks
> > Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
> > sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.
> 
> I though we dumped views already.  If not, I am inclinded to apply it,
> but want to see what others say. 

As this does not affect, in any way, shape or form, the backend...I'd be
inclined to say go for it also...

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org                    
scrappy@{postgresql|isc}.org                       ICQ#7615664


Mon, 5 Oct 1998 00:56:08 -0400 (EDT)
"Marc G. Fournier" <scrappy@hub.org>
Re: [HACKERS] Dumping of views -- done!
Terry Mackintosh wrote:
>
> Hi Bruce
>
> On Sat, 3 Oct 1998, Bruce Momjian wrote:
>
> > > Hi all
> > >
> > > Is it too late to add a feature to pg_dump for 6.4??
>
> > I though we dumped views already.  If not, I am inclinded to apply it,
>
> OH, ... well, if it does, I sure would like to know how, I missed it.
>

    No we didn't. I just mentioned that it would be possible with
    the new rule utility functions.

> > but want to see what others say.  I thought Jan was working on this, or
> > was that just rewrite rules?  Are you using his code?  I know there is
>
> I tryed to stay as much in style with the code that was there as possible.
>

    Took a look at the patch. Terry's using the new  system  view
    pg_views for it. But...

> > an item on the Open Items list that talks about this:
> >
> >  have psql dump out rules text with new function
> > Is that what this is?
>
> I'm not sure that is the same, what my change does is add:
> CREATE VIEW viewname AS select clause......;
> to the dump file.
>
> I did notice that if used with '-z' it also puts the revoke grant
> statments there too.  Not my doing, just nice.
>
> At this time there are no provisions (by me any way) to dump any
> associated update/delete rules on the view.  Unless, like -z above, it
> already works?
>
> To the best of my knowlage this feature does not yet exist, if any one
> else is working on it, I don't know.

    Not  me.  But  I  think  I should assist since I'm one of the
    rulegurus here on the list.

    IMHO the better way would be to dump views as regular  CREATE
    TABLE  statements  and  just omit the data (views have none).
    After all tables are restored and  all  functions,  operators
    and  operator classes are created, we should dump rules using
    the pg_rules view.

    The checks in pg_dump if a  table  is  a  view  rely  on  the
    existence  of  a  rule  named _RETtablename. I'll add another
    check to rewriteDefine.c making sure that a rewrite  rule  ON
    SELECT  follows  this  convention  (rules  ON  SELECT are now
    restricted to view rules at all so it would  make  sense  for
    me).

    I'll  add  double  quotes around identifiers in the output of
    pg_get_ruledef() and pg_get_viewdef().

    Terry, would you mind to implement dumping of views again  in
    the  above  mentioned  way? This would cover rewrite rules at
    all.  A good place to dump rules would be where triggers  get
    dumped  (if  they are dumped at all). Dumping all rules would
    automagically turn the formerly created tables into views.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 5 Oct 1998 10:35:27 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Dumping of views -- done!
Hi Jan

On Mon, 5 Oct 1998, Jan Wieck wrote:

>     Took a look at the patch. Terry's using the new  system  view
>     pg_views for it. But...
> 
>     IMHO the better way would be to dump views as regular  CREATE
>     TABLE  statements  and  just omit the data (views have none).
>     After all tables are restored and  all  functions,  operators
>     and  operator classes are created, we should dump rules using
>     the pg_rules view.
> 
Well, if you see need to do it differant, you most certainly know more
about it then me, so sure.

I am confused though ....
How is "CREATE TABLE ...." going to become "CREATE VIEW ....."????

Note also, that the way I did it kept in mind that some day we will have
outer joins, at which time most of the code I put in getTables() can come
out, and just make a very minor change to the main query to get the view
info all at one time.

If there is a cleaner way to do it though, then that is the way to do it,
I just do not understand what  it is you said.

I am lacking time today to really think about it, but will take time
tonight maybe to look at pg_rules and such.

Have a great day
Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!


Mon, 5 Oct 1998 09:46:18 -0400 (EDT)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] Dumping of views -- done!
> Hi all
> 
> Is it too late to add a feature to pg_dump for 6.4??
> 
> I just spent most of the day learning pg_dump and modifing it so it would
> dump views also.
> 
> This is the first time I have ever contributed any code changes, so I'm
> not sure of how to submit it.
> 
> The diff's and a readme as a tgz file are attached.

Applied, and I have updated the manual pages, and the sgml docs.  Yoo,
whooo.



-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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


Mon, 5 Oct 1998 23:10:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Dumping of views -- done!
> Applied, and I have updated the manual pages, and the sgml docs.  Yoo,
> whooo.

That sgml is pretty exciting, eh? Now calm down and get back to work :)

                       - Tom

Tue, 06 Oct 1998 04:40:06 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Dumping of views -- done!
> > Applied, and I have updated the manual pages, and the sgml docs.  Yoo,
> > whooo.
> 
> That sgml is pretty exciting, eh? Now calm down and get back to work :)

I actually have a hardcover SGML book, called "Readme.1st:  SGML for
Writers and Editors".

I have not really looked through it, but it was only $7.  It is a
Prentice Hall book, 1996.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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


Wed, 7 Oct 1998 20:08:37 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Dumping of views -- done!
> I actually have a hardcover SGML book, called "Readme.1st:  SGML for
> Writers and Editors".

Without having seen the book, I will make the comment that the SGML
books I've run into (not very many, but that won't stop me from
generalizing :) tend to tell you what you could do with SGML, rather
than telling you how to do something with the particular implementation
we are using (DocBook).

The fellow who wrote and maintains the "Modular Style Sheets", Norm
Walsh, is in the middle of writing a book (Nutshell? can't remember) on
DocBook, and presumably his style sheets.

The most useful single information source I've found so far, other than
stealing markup from other parts of the documentation, is the DocBook
element dictionary at the O'Reilly site:

  http://www.ora.com/homepages/dtdparse/docbook/3.0/

I hope this is mentioned in our docguide...

                     - Tom

Thu, 08 Oct 1998 07:26:18 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Dumping of views -- done!

I haven't seen any comments on this yet...am tempted to add it in,
considering we have another 2 weeks before release, and it doesn't affect
the backend...

Anyone want to comment?  If I hear nothing by, say, Monday morning, I'll
apply these...





On Sat, 3 Oct 1998, Terry Mackintosh wrote:

> Hi all
> 
> Is it too late to add a feature to pg_dump for 6.4??
> 
> I just spent most of the day learning pg_dump and modifing it so it would
> dump views also.
> 
> This is the first time I have ever contributed any code changes, so I'm
> not sure of how to submit it.
> 
> The diff's and a readme as a tgz file are attached.
> 
> Thanks
> Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
> sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.
> 
> Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
> -------------------------------------------------------------------
> Success Is A Choice ... book by Rick Patino, get it, read it!
> 

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org                    
scrappy@{postgresql|isc}.org                       ICQ#7615664


Mon, 12 Oct 1998 15:53:59 -0400 (EDT)
"Marc G. Fournier" <scrappy@hub.org>
Re: [HACKERS] Dumping of views -- done!
Hi Marc

On Mon, 12 Oct 1998, Marc G. Fournier wrote:

> 
> I haven't seen any comments on this yet...am tempted to add it in,
> considering we have another 2 weeks before release, and it doesn't affect
> the backend...

Hugh?-)
There were lots, any way it prompted Jan to do some major work that did
this and much more, so it is all done as far as I know.

Thanks for asking though, have a great night.

> Anyone want to comment?  If I hear nothing by, say, Monday morning, I'll
> apply these...
> 
> On Sat, 3 Oct 1998, Terry Mackintosh wrote:
> 
> > Hi all
> > 
> > Is it too late to add a feature to pg_dump for 6.4??

Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!


Mon, 12 Oct 1998 21:26:07 -0400 (EDT)
Terry Mackintosh <terry@terrym.com>
Re: [HACKERS] Dumping of views -- done!
>
>
> I haven't seen any comments on this yet...am tempted to add it in,
> considering we have another 2 weeks before release, and it doesn't affect
> the backend...
>
> Anyone want to comment?  If I hear nothing by, say, Monday morning, I'll
> apply these...
>

    Stop - don't apply.

    Dumping  of views is already done in the current CVS. Pg_dump
    now outputs CREATE TABLE for view relations too, but no data.
    At the end of the schema (after data for real tables has been
    filled in) it outputs commands to recreate ALL rewrite rules,
    silently turning the tables into the views they where at dump
    time.

>
>
>
>
> On Sat, 3 Oct 1998, Terry Mackintosh wrote:
>
> > Hi all
> >
> > Is it too late to add a feature to pg_dump for 6.4??
> >
> > I just spent most of the day learning pg_dump and modifing it so it would
> > dump views also.
> >
> > This is the first time I have ever contributed any code changes, so I'm
> > not sure of how to submit it.
> >
> > The diff's and a readme as a tgz file are attached.
> >
> > Thanks
> > Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
> > sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.
> >
> > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
> > -------------------------------------------------------------------
> > Success Is A Choice ... book by Rick Patino, get it, read it!
> >
>
> Marc G. Fournier                               scrappy@hub.org
> Systems Administrator @ hub.org
> scrappy@{postgresql|isc}.org                       ICQ#7615664
>
>
>


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Tue, 13 Oct 1998 09:10:31 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Dumping of views -- done!
On Tue, 13 Oct 1998, Jan Wieck wrote:

> >
> >
> > I haven't seen any comments on this yet...am tempted to add it in,
> > considering we have another 2 weeks before release, and it doesn't affect
> > the backend...
> >
> > Anyone want to comment?  If I hear nothing by, say, Monday morning, I'll
> > apply these...
> >
> 
>     Stop - don't apply.
> 
>     Dumping  of views is already done in the current CVS. Pg_dump
>     now outputs CREATE TABLE for view relations too, but no data.
>     At the end of the schema (after data for real tables has been
>     filled in) it outputs commands to recreate ALL rewrite rules,
>     silently turning the tables into the views they where at dump
>     time.

	D'oh...I hate losing a hard drive :(  catching up takes soooo long
to do :(

> 
> >
> >
> >
> >
> > On Sat, 3 Oct 1998, Terry Mackintosh wrote:
> >
> > > Hi all
> > >
> > > Is it too late to add a feature to pg_dump for 6.4??
> > >
> > > I just spent most of the day learning pg_dump and modifing it so it would
> > > dump views also.
> > >
> > > This is the first time I have ever contributed any code changes, so I'm
> > > not sure of how to submit it.
> > >
> > > The diff's and a readme as a tgz file are attached.
> > >
> > > Thanks
> > > Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
> > > sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.
> > >
> > > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
> > > -------------------------------------------------------------------
> > > Success Is A Choice ... book by Rick Patino, get it, read it!
> > >
> >
> > Marc G. Fournier                               scrappy@hub.org
> > Systems Administrator @ hub.org
> > scrappy@{postgresql|isc}.org                       ICQ#7615664
> >
> >
> >
> 
> 
> Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #======================================== jwieck@debis.com (Jan Wieck) #
> 
> 

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org                    
scrappy@{postgresql|isc}.org                       ICQ#7615664


Tue, 13 Oct 1998 10:33:15 -0400 (EDT)
"Marc G. Fournier" <scrappy@hub.org>
Re: [HACKERS] Dumping of views -- done!