Thread: How to see a RULE definition?
I am a Postgresql convert from MySQL so I am fairly new to this system but I am catching on quick. I had a question that I couldn't find in any of the docs or in the FAQ's. I was creating a rule on a view and was wondering if there was a way to look at the rule definition once it has been saved? I envision myself creating a few rules here and there but if I move off this DB project and someone else takes over I would like them to have a way to see what I did. I was able to figure out that all the rules for a particular DB are stored in the pg_rewrite table but that seems almost unreadable for a human. What I would like is a command that spits out the create syntax I used when the rule was first created. On that same line of thinking, is there a command to get a list of all rules that have been put on a table? I guess if all else fails I will just have to pull up a good old text editor and detail exactly what rules I build in. Thanks, Josh Goodman
You can use:
pgdump -s > outfile
which will dump all the schema for the database.
It should be in there somewhere ;)
Regards
Ben
> -----Original Message-----
> From: Josh Goodman [mailto:jogoodma@lanl.gov]
> Sent: 11 January 2001 16:16
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to see a RULE definition?
>
>
>
> I am a Postgresql convert from MySQL so I am fairly new to this
> system but I am catching on quick. I had a question that I
> couldn't find
> in any of the docs or in the FAQ's. I was creating a rule on
> a view and
> was wondering if there was a way to look at the rule
> definition once it
> has been saved?
>
> I envision myself creating a few rules here and there but if I
> move off this DB project and someone else takes over I would
> like them to
> have a way to see what I did. I was able to figure out that
> all the rules
> for a particular DB are stored in the pg_rewrite table but that seems
> almost unreadable for a human. What I would like is a
> command that spits
> out the create syntax I used when the rule was first created. On that
> same line of thinking, is there a command to get a list of
> all rules that
> have been put on a table?
>
> I guess if all else fails I will just have to pull up a good old
> text editor and detail exactly what rules I build in.
>
>
> Thanks,
> Josh Goodman
>
>
>
Josh Goodman <jogoodma@lanl.gov> writes: > ... I was able to figure out that all the rules > for a particular DB are stored in the pg_rewrite table but that seems > almost unreadable for a human. What I would like is a command that spits > out the create syntax I used when the rule was first created. See the (woefully undocumented) pg_get_ruledef() function. pg_dump uses this. > On that > same line of thinking, is there a command to get a list of all rules that > have been put on a table? A join of pg_rewrite against pg_class should do it for you ... regards, tom lane
You can use:
pg_dump -s dbase > outfile
which will dump all the schema for the database.
It should be in there somewhere ;)
Regards
Ben
> -----Original Message-----
> From: Josh Goodman [mailto:jogoodma@lanl.gov]
> Sent: 11 January 2001 16:16
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to see a RULE definition?
>
>
>
> I am a Postgresql convert from MySQL so I am fairly new to this
> system but I am catching on quick. I had a question that I
> couldn't find
> in any of the docs or in the FAQ's. I was creating a rule on
> a view and
> was wondering if there was a way to look at the rule
> definition once it
> has been saved?
>
> I envision myself creating a few rules here and there but if I
> move off this DB project and someone else takes over I would
> like them to
> have a way to see what I did. I was able to figure out that
> all the rules
> for a particular DB are stored in the pg_rewrite table but that seems
> almost unreadable for a human. What I would like is a
> command that spits
> out the create syntax I used when the rule was first created. On that
> same line of thinking, is there a command to get a list of
> all rules that
> have been put on a table?
>
> I guess if all else fails I will just have to pull up a good old
> text editor and detail exactly what rules I build in.
>
>
> Thanks,
> Josh Goodman
>
>
>
try this: select * from pg_rules where tablename = ... tamsin > On that > same line of thinking, is there a command to get a list of > all rules that > have been put on a table?
On Thu, 11 Jan 2001, Trewern, Ben wrote: > You can use: > pgdump -s > outfile > > which will dump all the schema for the database. > It should be in there somewhere ;) Or, for 7.1, use the new features of pg_dump to dump to the non-text-file formats, which will allow you instantly and selectively look at particular parts of your db dump, rather than worming your way through yourself. Better still: You can also SELECT * FROM pg_rules WHERE rulename='my_rule'; (at least in 7.1 betas; don't remember about 7.0.x) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Tom Lane wrote: > Josh Goodman <jogoodma@lanl.gov> writes: > > ... I was able to figure out that all the rules > > for a particular DB are stored in the pg_rewrite table but that seems > > almost unreadable for a human. What I would like is a command that spits > > out the create syntax I used when the rule was first created. > > See the (woefully undocumented) pg_get_ruledef() function. pg_dump uses > this. Or just SELECT * FROM pg_rules; > A join of pg_rewrite against pg_class should do it for you ... The above *IS* the join against these two. And there are pg_tables and pg_views as well. Jan :-) -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Joel Burton wrote: > On Thu, 11 Jan 2001, Trewern, Ben wrote: > > > You can use: > > pgdump -s > outfile > > > > which will dump all the schema for the database. > > It should be in there somewhere ;) > > Or, for 7.1, use the new features of pg_dump to dump to the non-text-file > formats, which will allow you instantly and selectively look at particular > parts of your db dump, rather than worming your way through yourself. > > Better still: > > You can also SELECT * FROM pg_rules WHERE rulename='my_rule'; > > (at least in 7.1 betas; don't remember about 7.0.x) Was already there - for sure. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #