-- View: vu_struct_physique_by_login

-- DROP VIEW vu_struct_physique_by_login;
CREATE OR REPLACE VIEW vu_struct_physique_by_login AS 

SELECT "struct_physique_CPNRC".id_struct_physique,
	"struct_physique_CPNRC".appelation,
	user_cen.memberid,
	user_cen.rolname
	
FROM "struct_physique_CPNRC", (SELECT pg_auth_members.roleid AS groupid,
	pg_auth_members.member AS memberid,
	pg_authid.rolname
	FROM pg_auth_members, pg_authid
	WHERE roleid IN (SELECT ref_roleid FROM "struct_physique_CPNRC")
	AND pg_auth_members.member=pg_authid.oid) AS user_cen
WHERE
	"struct_physique_CPNRC".ref_roleid=user_cen.groupid;

ALTER TABLE vu_struct_physique_by_login OWNER TO postgres;
GRANT ALL ON TABLE vu_struct_physique_by_login TO postgres;
GRANT SELECT ON TABLE vu_struct_physique_by_login TO cpnrc;
GRANT ALL ON TABLE vu_struct_physique_by_login TO geomatique;