-- function to grant privileges on all tables views and sequences in a
-- schema to a grantee.
-- call it like this: SELECT grant_all(schema_name, grantee_name, privileges);
--
-- privileges can be anything the the SQL GRANT command takes
-- e.g. 'ALL'
-- or 'SELECT'
-- or 'SELECT, INSERT, UPDATE, DELETE'


CREATE OR REPLACE FUNCTION grant_all(text, text, text) 
RETURNS void
LANGUAGE plperl AS $$

	my $schema = shift;
	my $grantee = shift;
	my $privs = shift;
	my $object_query = qq{
	
		SELECT 	c.relname as "Name"
		FROM 	pg_catalog.pg_class c
     			LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     			LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		WHERE 	c.relkind IN ('r','v','S')
      			AND n.nspname = '$schema'
      			AND pg_catalog.pg_table_is_visible(c.oid)
				AND u.usename = current_user

	};
	my $rv = spi_exec_query($object_query);
	if ($rv->{status} !~ /^SPI_OK_/ )
	{
		elog(ERROR,"got status $rv->{status} when querying catalog");
	}
	foreach my $i (0.. $rv->{processed}-1)
	{
		my $row = $rv->{rows}[$i];
		my $thing = $row->{Name};

		my $grant_query = qq{

				GRANT $privs 
					ON "$schema"."$thing" 
					TO $grantee 
		
		};

		my $grant_rv = spi_exec_query($grant_query);
		if ($grant_rv->{status} !~ /^SPI_OK_/  )
		{
			elog (ERROR,"got $rv->{status}  when trying to grant $privs " .
					"on \"$schema\".\"$thing\" to $grantee");
		}
		else
		{
			elog(NOTICE,
				"Granted $privs to $grantee on \"$schema\".\"$thing\"");
		}
		
	}

$$;

-- define scores table


CREATE TABLE scores (
	name text NOT NULL, 
	class text NOT NULL, 
	score integer
); 

-- function to populate scores table with test data
-- call it like this: select populate_scores();

CREATE OR REPLACE FUNCTION populate_scores() 
RETURNS void
LANGUAGE plperl AS $$

	my @names = qw(Fred May Joe Ellen Bill Anne);
	my @classes = qw(red yellow green);
	foreach my $name (@names)
	{
		foreach my $class(@classes)
		{
			my $score = int(rand()*100);
			my $query = qq{

				INSERT INTO scores
				VALUES ('$name','$class',$score)

			};
			my $rv = spi_exec_query($query);
			if ($rv->{status} !~ /^SPI_OK_/  )
			{
				elog (ERROR,"got $rv->{status}  when populating scores");
			}
		}
	}

$$;	

-- type for the set returned by get_ranks() - the same as scores but
-- with a rank column added

CREATE TYPE class_ranks AS (
	name text, 
	class text, 
	score integer, 
	class_rank integer
);

-- get the rank data - version for PostgreSQL version 8.0

CREATE OR REPLACE FUNCTION get_ranks() 
RETURNS SETOF class_ranks
LANGUAGE plperl AS $$

	my $query = q{

		SELECT 	name, class, score
		FROM 	scores
		ORDER BY class, score desc, name

	};

	my $rank = 0;
	my $prev_class = "";
	my $rv = spi_exec_query($query);
	if ($rv->{status} !~ /^SPI_OK_/  )
	{
		elog (ERROR,"got $rv->{status}  when selecting scores");
	}
	my $result = [];
	foreach my $i (0 .. $rv->{processed} -1)
	{
		my $row = $rv->{rows}[$i];
		$rank = 0 if ($row->{class} ne $prev_class);
		$prev_class = $row->{class};
		$rank++;
		$row->{class_rank} = "$rank";
		push(@$result,$row);
	}
	return $result;

$$;

-- get the rank data - version for PostgreSQL >= 8.1
-- uses the API that avoids large memory issues, and is also faster

CREATE OR REPLACE FUNCTION get_ranks_revised() 
RETURNS SETOF class_ranks
LANGUAGE plperl AS $$

	my $query = q{

		SELECT 	name, class, score
		FROM 	scores
		ORDER BY class, score desc, name

	};

	my $rank = 0;
	my $prev_class = "";
	my $handle = spi_query($query);
	while (my $row = spi_fetchrow($handle))
	{
		$rank = 0 if ($row->{class} ne $prev_class);
		$prev_class = $row->{class};
		$rank++;
		$row->{class_rank} = "$rank";
		return_next($row);
	}
	return undef;


$$;