postgresql extracting function source

Question

Need to extract the source of a PostgreSQL function using SQL.

I am seeing this odd behavior with one of the function, all other functions (around 200+ ) work absolutely fine.

When I run the following statement, it works:

select prosrc  from pg_proc where proname= 'accounts_count';

However when I run the following, it returns an empty string:

select  routine_definition
from    information_schema.routines
where   specific_name = 'accounts_count_66243'

PostgreSQL version 8.3. I have tried using both pgAdmin III and psql.

Not a show stopper, but would be useful to know why this might be.

Any ideas anyone?

Since 8.4, there is pg_get_functiondef(). Maybe another reason to update to a more up-to-date version? — user330315
– user330315, Commented Sep 30, 2012 at 9:07

Pavel Stehule · Accepted Answer · 2012-09-30 08:40:14Z

2

if you look on definition of information_schema.routines, then you can find following filter:

FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt
 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid 
   AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) 
    OR has_function_privilege(p.oid, 'EXECUTE'::text));

so my theory:

there are some issue in rights and ownership of related function (probably) - try to use different account for validation of this theory (postgres is best)

answered Sep 30, 2012 at 8:40

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ManiP Over a year ago

Well done. It turned out to be a rights issue. The function owner was set to a different user. It appears if the function is not owned by the logged in user, information_schema.routines does not surface the source while pg_proc does. Odd.

Collectives™ on Stack Overflow

postgresql extracting function source

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related