Re: Retrieving comment of rules and triggers

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Thu, Jun 9, 2016 at 5:03 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Thu, Jun 9, 2016 at 5:01 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum <magnum11200@xxxxxxxxx> wrote:

Hi, is there a way to retrieve the comment of rules and triggers. I worked it out on functions, tables, views but am kind of stuck with rules and triggers.

​

https://www.postgresql.org/docs/devel/static/functions-info.html

​Table ​9-63. Comment Information Functions

obj_description(object_oid, catalog_name)

Catalog Names:

pg_trigger

pg_rules

The source catalog for the data is:

pg_description

I'm suspect of your existing solution given that this is supposed to be a universal interface for comment data...

David J.

-- To get all rule comments
SELECT DISTINCT r.rulename,
       c.description
  FROM pg_rewrite r
  JOIN pg_description c ON c.objoid = r.oid;

-- To get all trigger comments

SELECT DISTINCT t.tgname,
       c.description
  FROM pg_trigger t
  JOIN pg_description c ON c.objoid = t.oid;


--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

FYI, in the future, it is always best to give your version of PostgreSQL & O/S, as the developers have a nasty habit of changing

pg_catalog tables/columns.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.