CODEWITHSUNDEEP
databasepostgresql
Mukhammadsher
Mukhammadsher

Reputation: 192

How to get user defined function comment in postgreSQL

I'm trying to get function comment where I saved basic info that I need in my program. There are many functions that I have created name of all starts with 'stat_' and i'm retrieving their names using code below. 

SELECT routines.routine_name
FROM information_schema.routines
LEFT JOIN information_schema.parameters ON 
routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='public' AND routines.routine_name LIKE 
'stat_%' ORDER BY routines.routine_name, parameters.ordinal_position;

Now I have all function names, and I need to get their comments. I could not find solution, if you know please share.

Upvotes: 1

Views: 2010

Answers (2)

CodeMan
CodeMan

Reputation: 706

SELECT SPECIFIC_NAME AS SpName
FROM information_schema.routines
WHERE routines.specific_schema='public' AND routines.routine_name LIKE 'stat_%' 
ORDER BY routines.routine_name

then you can get source of your object by this command:

EXEC sp_helptext 'ObjectName';

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Comments can be retrieved via pg_description by the object id.

SELECT p.proname,
       p.proargtypes,
       d.description
       FROM pg_proc p
            LEFT JOIN pg_description d
                      ON d.objoid = p.oid
       WHERE p.proname LIKE 'stat$_%' ESCAPE '$';

Upvotes: 2

Related Questions