list rules in postgres

Anon

Anyone know of a way to list any/all rules that have been create in a database? tia.

Anon

I was going to tell you to use psql -E to show you what queries psql issues to list rules, and guess what? <i>psql doesn't have a way to list rules!</b> Bummer...

Anon

Try this:

List all views:

psql -c '\dv' databasename

List view definition:

psql -c '\d viewname' databasename

you can psql -c just about any \ command.

Anon

Are rules listed as views?

Anon

thats part of what I have ben trying to determine. i did \dv but that didnt work, but \d 'viewname' does produce a View and Query information for one of my tables(well, i guess it is really a view), which is supposed to be created based on a rule.

In another db it doesnt display this, so I guess this could determine if a rule exists, but only if you already know the rule name. What might be handy is a \dr command to display all rules on a current database.

Anon

one possible solution would be to do: pg_dump -s dbname > filename

this dumps out the database schema, listing any rules that have been created.

Anon

Do that with a --schema-only switch and you're right!

pg_dump --schema-only dbname -t tablename >data.dmp

should do it.

Anon

did you try the other \d commands?
legal second letters are:

tisvpSladfoT

So, it might be one of those. Do a \? to see them all.

Did you look at the \dS tables? I think there's a pg_rules table, but I don't know if rules go in there or not.

If so then a select * from pg_rules; would get the rules.