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
Show quoted text
-----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
Import Notes
Resolved by subject fallback
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
Show quoted text
-----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
Import Notes
Resolved by subject fallback
try this:
select * from pg_rules where tablename = ...
tamsin
Show quoted text
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 > outfilewhich 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 > outfilewhich 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 #