Re: Need help extripating plpgsql

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 02/22/2013 11:59 AM, Kevin Grittner wrote:
>> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>>
>>> At this point I am not sure how to do this with out creating role
>>> that has superuser privileges.
>>
>> Something like this?:
>>
>> -- Set up the template using database superuser.
>> create database template2;
>> \c template2
>> drop extension plpgsql;
>> vacuum freeze analyze;
>> \c postgres
>> update pg_database set datistemplate = true
>>     where datname = 'template2';
>> checkpoint;
>>
>> -- Create a user who can own the database and plpgsql.
>> create user bob with createdb;
>> set role bob;
>> create database bob template template2;
>> \c bob
>> create extension plpgsql;
>
> If you do as above plpgsql is created as with owner postgres.
>
> To get owner to be bob you need to do:
>
> \c bob
> set role bob;
> create extension plpgsql;

Good point, I forgot that the user was reset by \c.

> Either way you still get the error on the COMMENT which is what is
> tripping up the OP.

The good news is that it seems to be fixed on HEAD:

test=# drop database bob;
DROP DATABASE
test=# set role bob;
SET
test=> create database bob template template2;
CREATE DATABASE
test=> \c bob bob
You are now connected to database "bob" as user "bob".
bob=> create extension plpgsql;
CREATE EXTENSION
bob=> \dL
                    List of languages
  Name   | Owner | Trusted |         Description         
---------+-------+---------+------------------------------
 plpgsql | bob   | t       | PL/pgSQL procedural language
(1 row)

--

Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company