Re: How to create read-only view on 9.3

Hi Szymon,

Thank you for response.

>> Could you show an example?
>
I do below things on one server.
The path to database cluster and port are
different with each other.

[9.2.4]
initdb --no-locale -E UTF8
pg_ctl start
createdb testdb
psql testdb -c "create table tbl(i int)"
psql testdb -c "insert into tbl values (generate_series(1,10))"
psql testdb -c "create view v as select * from tbl"

[9.3beta2]
pg_dump -p <port of 9.2.4> testdb > /tmp/92dmp.dmp
initdb --no-locale -E UTF8
pg_ctl start
createdb testdb
psql testdb -f /tmp/92dmp.dmp

After all, the view v became updatable view.

-------
$ psql testdb
psql (9.3beta2)
Type "help" for help.

testdb=# select * from v;
i
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

testdb=# insert into v values (11);
INSERT 0 1
testdb=# select * from v;
i
----
1
2
3
4
5
6
7
8
9
10
11
(11 rows)

regards,
--------------------
NTT Software Corporation
Tomonari Katsumata

(2013/08/13 19:16), Szymon Guz wrote:
> On 13 August 2013 11:43, Tomonari Katsumata <
> katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>
>> Hi,
>>
>> Could anyone tell me how to create read-only view on
>> PostgreSQL 9.3 ?
>>
>> I've been testing updatable views and noticed that
>> all simple views are updatable.
>>
>> When I use pg_dump for upgrading from PostgreSQL 9.2
>> to PostgreSQL 9.3 and if the databse has views,
>> all views are updatable on the restored database.
>>
>> I want to make these views read-only like PostgreSQL9.2.
>> How can I do this? Should I make access control on users ?
>> (Sorry, I couldn't find any explanations on document.)
>>
>> regards,
>> --------------------
>> NTT Software Corporation
>> Tomonari Katsumata
>>
>>
>>
>> Could you show an example?
>
> Szymon
>