updatable view

On Tue, Jun 11, 2013 at 3:32 AM, Sajeev Mayandi <Sajeev_Mayandi@symantec.com

wrote:

Hi,

I am using 9.2. The documentation says that there is no support for
updatable view. It suggesting to use the triggers or rule. Can somebody
paste the sample code for the same.

Very well summarized by Craig on SO with links (code/implementation).

Below link helps you about "how to" on the updatable views.

http://stackoverflow.com/questions/13151566/cannot-update-view

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Hello,

Is there a query out there where I can get a list of permissions associated to a schema?
Something like the below query that I can do for a table, but for a schema instead?

SELECT grantee, privilege_type
FROM information_schema.role_table_grants

WHERE table_name='sites' order by grantee, privilege_type;

I'm not seeing anything on the net or anything useful in information_schema like a
'role_schema_grants' view, and it doesn't appear I can do a \dp on a schema.
Nor the query psql uses for \dp on a table doesn't seem to have a nice way to
convert it to a schema permissions list. The secret is escaping me. :(

-ds

Background: -----------------

In pg_log, I'm seeing an error I'd like to clean up.

2013-08-15 13:00:32 GMT ERROR: permission denied for schema public at character 98
2013-08-15 13:00:32 GMT STATEMENT: select s.id, s.name, s.activate_at, s.old_sitecode, s.latitude, s.longitude, s.elevation from
public.site s where new_schoolid = $1

I get this error on a development system that is a spin off of a production system. The production system doesn't ever produce this
error. Doing a \dp between production and development the sites table shows no difference. I'd like to do the same for the public
schema. The public schema by default is open to all imho, and by explicitly opening it up ( to who I don't know) I may be just
removing a symptom.. but not the real problem.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/16/2013 10:27 AM, David Salisbury wrote:

Hello,

Is there a query out there where I can get a list of permissions
associated to a schema?
Something like the below query that I can do for a table, but for a
schema instead?

SELECT grantee, privilege_type

FROM information_schema.role_table_grants
WHERE table_name='sites' order by grantee,
privilege_type;

I'm not seeing anything on the net or anything useful in
information_schema like a
'role_schema_grants' view, and it doesn't appear I can do a \dp on a
schema.
Nor the query psql uses for \dp on a table doesn't seem to have a nice
way to
convert it to a schema permissions list. The secret is escaping me. :(

aklaver@killi:~> psql -d test -U postgres -E
psql (9.0.13)
Type "help" for help.

test=# \dn+
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE (n.nspname !~ '^pg_temp_' OR
n.nspname = (pg_catalog.current_schemas(true))[1])
ORDER BY 1;
**************************

List of schemas
Name | Owner | Access privileges |
Description
--------------------+----------+----------------------+----------------------------------
information_schema | postgres | postgres=UC/postgres+|
| | =U/postgres |
pg_catalog | postgres | postgres=UC/postgres+| system catalog
schema
| | =U/postgres |
pg_toast | postgres | | reserved schema
for TOAST tables
pg_toast_temp_1 | postgres | |
public | postgres | postgres=UC/postgres+| standard public
schema
| | =UC/postgres |
(5 rows)

-ds

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general