--- Pg/Scheme.pm	2003/08/12 18:45:06	1.1
+++ Pg/Scheme.pm	2003/08/12 20:38:06	1.2
@@ -16,6 +16,14 @@
                
 @EXPORT_OK	= qw(
 				&list_tables
+				&get_table_oid
+				&explain
+				&pg_attribute
+				&cols
+				&cols_notnull
+				&cols_null
+				&cols_pk
+				&cols_notpk
 		);
 
 my $debug;
@@ -88,11 +96,13 @@
 	return $row->{oid};
 }
 
-sub explain_table {
+sub explain {
 	my $self = shift;
 	my $table = shift;
 
-	my @explain;
+	# XXX if explained, return just results
+	# return $self->{'explained'}->{$table}->{'pg_attribute'} if ($self->{'explained'}->{$table}->{'pg_attribute'});
+	my @pg_attribute;
 
 	my $oid = $self->get_table_oid($table);
 
@@ -105,14 +115,159 @@
 	ORDER BY a.attnum
 	";
 
+	my @cols;	# all colums (for insert)
+	my @cols_null;	# colums compared by a=b or a is null and b is null
+	my @cols_notnull;# colums compared by a=b
+
 	my $sth = $self->{'dbh'}->prepare($sql);
 	$sth->execute($oid) || die;
 	while(my $row = $sth->fetchrow_hashref()) {
 		# attname | format_type | attnotnull | atthasdef | attnum
-		push @explain, $row;
+		push @pg_attribute, $row;
+		push @cols,$row->{attname};
+
+		if ($row->{attnotnull}) {
+			push @cols_notnull,$row->{attname};
+		} else {
+			push @cols_null,$row->{attname};
+		}
+
+	}
+
+	@{$self->{'explained'}->{$table}->{'pg_attribute'}} = @pg_attribute;
+	@{$self->{'explained'}->{$table}->{'cols'}} = @cols;
+	@{$self->{'explained'}->{$table}->{'cols_notnull'}} = @cols_notnull;
+	@{$self->{'explained'}->{$table}->{'cols_null'}} = @cols_null;
+
+	# all, just for safe keeping
+	@{$self->{'explained'}->{$table}->{'cols_notpk'}} = @cols;
+
+	# now, try to find primary key
+
+	my @cols_pk;	# columns which are primary key
+	my @cols_notpk;
+	my %in_pk;
+
+	$sql="
+SELECT
+	i.indexrelid as indexrelid, i.indrelid as indrelid,
+	count(a.attname) as cols_in_pk
+FROM
+	pg_catalog.pg_class c,
+	pg_catalog.pg_index i,
+	pg_catalog.pg_attribute a
+WHERE
+	c.oid = i.indrelid
+	and i.indisunique
+	and c.relname = '$table'
+	and a.attrelid = i.indexrelid
+GROUP BY
+	i.indexrelid, i.indrelid, c.relname, i.indisprimary, i.indisunique
+ORDER BY
+	cols_in_pk ASC, i.indisprimary DESC, i.indisunique DESC, c.relname DESC
+";
+	print STDERR "DEBUG: $sql\n" if ($debug);
+	$sth = $self->{'dbh'}->prepare($sql);
+	$sth->execute() || die;
+	my $row = $sth->fetchrow_hashref();
+	if ($row) {
+		$sql="
+		select a1.attname as attname from pg_attribute a1, pg_attribute a2 where a1.attrelid = ".$row->{indexrelid}." and a2.attrelid=".$row->{indrelid}." and a1.attname = a2.attname and a2.attnotnull";
+		
+		my $sth2 = $self->{'dbh'}->prepare($sql);
+		print STDERR "DEBUG: $sql\n" if ($debug);
+		$sth2->execute() || die;
+		while (my $row2 = $sth2->fetchrow_hashref()) {
+			push @cols_pk,$row2->{attname};
+			$in_pk{$row2->{attname}}++;
+		}
+		
+	}
+
+	foreach my $col (@cols) {
+		push @cols_notpk,$col if (! $in_pk{$col});
+	}
+
+	@{$self->{'explained'}->{$table}->{cols_pk}} = @cols_pk;
+	@{$self->{'explained'}->{$table}->{cols_notpk}} = @cols_notpk;
+
+	return @pg_attribute;
+}
+
+# return all rows in PostgreSQL format
+# attname | format_type | attnotnull | atthasdef | attnum
+#
+# somewhat internal function, but still usefull if you want to
+# do tweaking of columns your way
+#
+sub pg_attribute {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
+	}
+
+	return $self->{'explained'}->{$table}->{pg_attribute};
+}
+
+# return columns in given table
+sub cols {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
+	}
+
+	return $self->{'explained'}->{$table}->{cols};
+}
+
+# return not null columns in given table
+sub cols_notnull {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
+	}
+
+	return $self->{'explained'}->{$table}->{cols_notnull};
+}
+
+# return columns which *can* be null in given table
+sub cols_null {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
+	}
+
+	return $self->{'explained'}->{$table}->{cols_null};
+}
+
+# return primary key columns
+sub cols_pk {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
+	}
+
+	return $self->{'explained'}->{$table}->{cols_pk};
+}
+
+# return columns not in primary key
+sub cols_notpk {
+	my $self = shift;
+	my $table = shift;
+
+	if (! $self->{'explained'}->{$table}) {
+		$self->explain($table);
 	}
-	$sth->finish();
 
-	return @explain;
+	return $self->{'explained'}->{$table}->{cols_notpk};
 }
 1;