Thread: How to see a RULE definition?

How to see a RULE definition?

From

Josh Goodman

Date:

11 January 2001, 14:16:32

    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

RE: How to see a RULE definition?

From

"Trewern, Ben"

Date:

11 January 2001, 14:54:56

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
>
>
>

Re: How to see a RULE definition?

From

Tom Lane

Date:

11 January 2001, 14:59:55

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

RE: How to see a RULE definition?

From

"Trewern, Ben"

Date:

11 January 2001, 15:02:49

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

RE: How to see a RULE definition?

From

"Tamsin"

Date:

11 January 2001, 15:08:09

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?

RE: How to see a RULE definition?

From

Joel Burton

Date:

11 January 2001, 15:12:28

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

Re: How to see a RULE definition?

From

Jan Wieck

Date:

11 January 2001, 18:14:32

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 #

Re: How to see a RULE definition?

From

Jan Wieck

Date:

11 January 2001, 18:26:52

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 #