# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1409594619 -7200
# Node ID 0f6df6850ba6d5e7a479d170ba38b8f8595d018a
# Parent  8f0e2d4177021131c27455a8b7e0b2880f34b186
prototyp: users_groups – buggy:

second run in UNION:
    WARNING:  SQL: XXX skupina je schizoidní 0
groups are not iterater for second time in one query (unioned)

Also this:
    SELECT * FROM users; SELECT * FROM users;
returns weird results – first resultset is OK, second one is empty

diff -r 8f0e2d417702 -r 0f6df6850ba6 prototyp/prototyp.sql
--- a/prototyp/prototyp.sql	Mon Sep 01 18:10:25 2014 +0200
+++ b/prototyp/prototyp.sql	Mon Sep 01 20:03:39 2014 +0200
@@ -81,6 +81,10 @@
 
 -- user groups: --------------------------------------------------------------
 
+DROP VIEW IF EXISTS users_groups;
+DROP VIEW IF EXISTS users_groups_primary;
+DROP VIEW IF EXISTS users_groups_secondary;
+
 DROP VIEW IF EXISTS groups;
 DROP FUNCTION IF EXISTS groups();
 DROP TYPE IF EXISTS unix_sql_api_groups;
@@ -92,11 +96,13 @@
 );
 
 CREATE OR REPLACE FUNCTION groups()
-RETURNS SETOF unix_sql_api_groups AS $$
+RETURNS SETOF unix_sql_api_groups STABLE AS $$
 	use strict;
 	use warnings;
 
 	use User::grent;
+	
+	my $i = 0;
 
 	while (my $group = getgrent()) {
 		return_next({
@@ -104,7 +110,9 @@
 			name => $group->name,
 			members => [@{$group->members}]
 		});
+		elog(NOTICE, "skupina je schizoidní " . $i++);
 	}
+	elog(NOTICE, "XXX skupina je schizoidní " . $i++);
 	
 	elog(NOTICE, "members field does not contain users who have this group as primary one");
 	
@@ -117,7 +125,7 @@
 COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one';
 
 
--- user: ---------------------------------------------------------------------
+-- users: --------------------------------------------------------------------
 
 DROP VIEW IF EXISTS users;
 DROP FUNCTION IF EXISTS users();
@@ -135,13 +143,15 @@
 );
 
 CREATE OR REPLACE FUNCTION users()
-RETURNS SETOF unix_sql_api_users AS $$
+RETURNS SETOF unix_sql_api_users STABLE AS $$
 	use strict;
 	use warnings;
 	
 	use encoding "UTF-8";
 	use User::pwent;
 	
+	my $i = 0;
+	
 	while (my $user = getpwent()) {
 		return_next({
 			id => $user->uid,
@@ -152,10 +162,12 @@
 			home => $user->dir,
 			shell => $user->shell,
 			# expire => $user->expire
-			
 		});
+		elog(NOTICE, "uživatel je schizoidní " . $i++);
 	}
 	
+	elog(NOTICE, "XXX uživatel je schizoidní " . $i++);
+	
 	return undef;
 $$ LANGUAGE plperlu;
 
@@ -163,3 +175,37 @@
 	SELECT * FROM users()
 ;
 
+
+-- users_groups: -------------------------------------------------------------
+
+CREATE OR REPLACE VIEW users_groups_primary AS
+	SELECT
+		u1.id AS uid,
+		u1.gid AS gid,
+		u1.name AS user,
+		g1.name AS group,
+		true AS primary
+	FROM users AS u1
+	JOIN groups AS g1 ON (u1.gid = g1.id)
+;
+
+CREATE OR REPLACE VIEW users_groups_secondary AS
+	SELECT
+		u2.id AS uid,
+		g2.*,
+		false AS primary
+	FROM
+		(SELECT
+			id AS gid,
+			unnest(members) AS user,
+			name AS group
+		FROM groups) AS g2
+	JOIN users AS u2 ON (u2.name = g2.user)
+;
+
+CREATE OR REPLACE VIEW users_groups AS
+	SELECT * FROM users_groups_primary
+	UNION
+	SELECT * FROM users_groups_secondary
+;
+