Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a number of GRANTs I need to make in a Postgres databas -- Is it possible to put statements like

GRANT SELECT ON TABLE tablename_xxx TO xxx_group;

inside of a Postgres SQL function?

share|improve this question
"in side the postgres function." <-- The what?? – Chris S Jan 25 '12 at 21:12
I mean - inside the user defined function – Trip Jan 25 '12 at 21:20
Yep, you can do that. Don't know why you would want to, but you can. – Chris S Jan 25 '12 at 21:25
Thankyou Chris Basically I have a set of Grant commands I want to write a loop and pass the table names as parameters so that the function can be very minimal – Trip Jan 25 '12 at 22:32

I believe you can do this, but in practice you are probably better off writing an external script to do it instead: Having a Postgres function (pl/pgsql or similar) hanging around just to do GRANTs is overkill, where a simple shell script (or PowerShell if you're on Windows) could do the job.

I consider GRANT to be part of the DDL functionality of Postgres, and it's generally a good practice to not write functions that use DDL statements (Just as you would probably not write a function to create tables).

share|improve this answer
You are correct, as per the suggestion I wrote the external script and its working great. Thanku – Trip Feb 10 '12 at 18:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.