1.1 --- a/prototyp/prototyp.sql	Mon Sep 01 18:10:25 2014 +0200
     1.2 +++ b/prototyp/prototyp.sql	Mon Sep 01 20:03:39 2014 +0200
     1.3 @@ -81,6 +81,10 @@
     1.4  
     1.5  -- user groups: --------------------------------------------------------------
     1.6  
     1.7 +DROP VIEW IF EXISTS users_groups;
     1.8 +DROP VIEW IF EXISTS users_groups_primary;
     1.9 +DROP VIEW IF EXISTS users_groups_secondary;
    1.10 +
    1.11  DROP VIEW IF EXISTS groups;
    1.12  DROP FUNCTION IF EXISTS groups();
    1.13  DROP TYPE IF EXISTS unix_sql_api_groups;
    1.14 @@ -92,11 +96,13 @@
    1.15  );
    1.16  
    1.17  CREATE OR REPLACE FUNCTION groups()
    1.18 -RETURNS SETOF unix_sql_api_groups AS $$
    1.19 +RETURNS SETOF unix_sql_api_groups STABLE AS $$
    1.20  	use strict;
    1.21  	use warnings;
    1.22  
    1.23  	use User::grent;
    1.24 +	
    1.25 +	my $i = 0;
    1.26  
    1.27  	while (my $group = getgrent()) {
    1.28  		return_next({
    1.29 @@ -104,7 +110,9 @@
    1.30  			name => $group->name,
    1.31  			members => [@{$group->members}]
    1.32  		});
    1.33 +		elog(NOTICE, "skupina je schizoidní " . $i++);
    1.34  	}
    1.35 +	elog(NOTICE, "XXX skupina je schizoidní " . $i++);
    1.36  	
    1.37  	elog(NOTICE, "members field does not contain users who have this group as primary one");
    1.38  	
    1.39 @@ -117,7 +125,7 @@
    1.40  COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one';
    1.41  
    1.42  
    1.43 --- user: ---------------------------------------------------------------------
    1.44 +-- users: --------------------------------------------------------------------
    1.45  
    1.46  DROP VIEW IF EXISTS users;
    1.47  DROP FUNCTION IF EXISTS users();
    1.48 @@ -135,13 +143,15 @@
    1.49  );
    1.50  
    1.51  CREATE OR REPLACE FUNCTION users()
    1.52 -RETURNS SETOF unix_sql_api_users AS $$
    1.53 +RETURNS SETOF unix_sql_api_users STABLE AS $$
    1.54  	use strict;
    1.55  	use warnings;
    1.56  	
    1.57  	use encoding "UTF-8";
    1.58  	use User::pwent;
    1.59  	
    1.60 +	my $i = 0;
    1.61 +	
    1.62  	while (my $user = getpwent()) {
    1.63  		return_next({
    1.64  			id => $user->uid,
    1.65 @@ -152,10 +162,12 @@
    1.66  			home => $user->dir,
    1.67  			shell => $user->shell,
    1.68  			# expire => $user->expire
    1.69 -			
    1.70  		});
    1.71 +		elog(NOTICE, "uživatel je schizoidní " . $i++);
    1.72  	}
    1.73  	
    1.74 +	elog(NOTICE, "XXX uživatel je schizoidní " . $i++);
    1.75 +	
    1.76  	return undef;
    1.77  $$ LANGUAGE plperlu;
    1.78  
    1.79 @@ -163,3 +175,37 @@
    1.80  	SELECT * FROM users()
    1.81  ;
    1.82  
    1.83 +
    1.84 +-- users_groups: -------------------------------------------------------------
    1.85 +
    1.86 +CREATE OR REPLACE VIEW users_groups_primary AS
    1.87 +	SELECT
    1.88 +		u1.id AS uid,
    1.89 +		u1.gid AS gid,
    1.90 +		u1.name AS user,
    1.91 +		g1.name AS group,
    1.92 +		true AS primary
    1.93 +	FROM users AS u1
    1.94 +	JOIN groups AS g1 ON (u1.gid = g1.id)
    1.95 +;
    1.96 +
    1.97 +CREATE OR REPLACE VIEW users_groups_secondary AS
    1.98 +	SELECT
    1.99 +		u2.id AS uid,
   1.100 +		g2.*,
   1.101 +		false AS primary
   1.102 +	FROM
   1.103 +		(SELECT
   1.104 +			id AS gid,
   1.105 +			unnest(members) AS user,
   1.106 +			name AS group
   1.107 +		FROM groups) AS g2
   1.108 +	JOIN users AS u2 ON (u2.name = g2.user)
   1.109 +;
   1.110 +
   1.111 +CREATE OR REPLACE VIEW users_groups AS
   1.112 +	SELECT * FROM users_groups_primary
   1.113 +	UNION
   1.114 +	SELECT * FROM users_groups_secondary
   1.115 +;
   1.116 +