Re: Having a problem with RoR-3.1.1 and Pg-9.1

On 02/28/2012 10:52 AM, James B. Byrne wrote:
>
> On Tue, February 28, 2012 13:28, Adrian Klaver wrote:
>
>>>
>>> Why does this not work?
>>>
>>> => \c test
>>> You are now connected to database "test" as user "devl".
>>> ALTER EXTENSION plpgsql OWNER TO devl;
>>> ERROR: syntax error at or near "OWNER"
>>> LINE 1: ALTER EXTENSION plpgsql OWNER TO devl;
>>> => \c - postgres
>>> You are now connected to database "test" as user
>>> "postgres".
>>> ALTER EXTENSION plpgsql OWNER TO devl;
>>> ERROR: syntax error at or near "OWNER"
>>> LINE 1: ALTER EXTENSION plpgsql OWNER TO devl;
>>>
>>
>> There is no OWNER clause in ALTER EXTENSION.
>> If you are trying to do what I showed, you need to as
>> the'postgres' user
>> DROP EXTENSION plpgsl in database 'test' and then become
>> owner 'dev1'
>> and CREATE EXTENSION plpgsl
>>
>
> And yet PGAdmin3-1.14.2 shows this in the sql pane when
> the plpgsql object is selected:
>
> - Extension: plpgsql
>
> -- DROP EXTENSION plpgsql;
>
> CREATE EXTENSION plpgsql
> SCHEMA pg_catalog
> VERSION "1.0";
> ALTER EXTENSION plpgsql
> OWNER TO postgres;
>
> It seems to me that this arrangement of including
> programming languages in a database is far more awkward
> than it needs be. Since ALTER EXTENSION is a PostgreSQL
> extension to SQL could not the ability to alter the owner
> of an extension be provided as well?
>

The PgAdmin folks would be better able to help you with the exact reason
for the above, but I suspect they really meant:

http://www.postgresql.org/docs/9.1/interactive/sql-alterlanguage.html

ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner

So in the category I have learned something new:

template1=# \dL
List of languages
Name | Owner | Trusted
---------+----------+---------
plpgsql | postgres | t
(1 row)

template1=# ALTER LANGUAGE plpgsql owner to aklaver;
ALTER LANGUAGE
template1=# \dL
List of languages
Name | Owner | Trusted
---------+---------+---------
plpgsql | aklaver | t
(1 row)

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com