2 --SELECT dropIfExists('VIEW', 'usr');
3 CREATE OR REPLACE VIEW usr AS
4 SELECT usesysid::integer AS usr_id,
5 usename::text AS usr_username,
6 COALESCE((SELECT usrpref_value FROM usrpref WHERE usrpref_username=usename AND usrpref_name='propername'), '') AS usr_propername,
7 null::text AS usr_passwd,
8 COALESCE((SELECT CAST(usrpref_value AS INTEGER) FROM usrpref WHERE usrpref_username=usename AND usrpref_name='locale_id'),
9 COALESCE((SELECT locale_id FROM locale WHERE lower(locale_code) = 'default' LIMIT 1), (SELECT locale_id FROM locale ORDER BY locale_id LIMIT 1))) AS usr_locale_id,
10 COALESCE((SELECT usrpref_value FROM usrpref WHERE usrpref_username=usename AND usrpref_name='initials'), '') AS usr_initials,
11 COALESCE((SELECT CASE WHEN usrpref_value='t' THEN true ELSE false END FROM usrpref WHERE usrpref_username=usename AND usrpref_name='agent'), false) AS usr_agent,
12 COALESCE((SELECT CASE WHEN usrpref_value='t' THEN true ELSE false END FROM usrpref WHERE usrpref_username=usename AND usrpref_name='active'), userCanLogin(usename)) AS usr_active,
13 COALESCE((SELECT usrpref_value FROM usrpref WHERE usrpref_username=usename AND usrpref_name='email'), '') AS usr_email,
14 COALESCE((SELECT usrpref_value FROM usrpref WHERE usrpref_username=usename AND usrpref_name='window'), '') AS usr_window
18 REVOKE ALL ON TABLE usr FROM PUBLIC;
19 GRANT ALL ON TABLE usr TO GROUP xtrole;