#! /usr/local/bin/python

# pg_xtab.py - Create a crosstab table in a PostgreSQL database
# written by Andrew L. Gould, andrewgould@yahoo.com
# June 26, 2003


import pg

#User defined connection variables
hostname = 'localhost'   #Database server host name or ip address
portno = 5432            #Database server port number
opt_var = None           #Options to be sent to the server
tty_var = None           #Optional tty for debug output
dbname = ''              #Database name
username = ''            #Database user name
userpasswd = ''          #Database user password

#User defined xtab query variables
#All fields must come from the same table
tablename = ''               #Database table to be queried
rowhdr = ''                  #String list of fields, separated by commas, to be used as row headers
colhdr = ''	              #One field, whose values will become column names
valfld = ''	              #One field, whose values will be aggregated and displayed in xtab
aggrfunc = ''	            #Aggregate function, without parentheses, to use on valfld
tmpflag = 0	              #Should new table be temporary? 1==temporary table, 0==permanent table
xtab_name = ''	           #Name for new, destination table
col_where_crit = 'where '    #Add criteria for column header field only, else leave as-is
where_crit = 'where '	    #Add 'where' criteria, else leave as-is.
having_crit = 'having '	  #Add 'having' criteria, else leave as-is.

##END OF USER DEFINED VARIABLES

#Manipulate some variables for easier processing
if tmpflag == 0:
	dest = xtab_name
else: dest = 'temp ' + xtab_name

if len(col_where_crit) > 6:
	cwcrit = col_where_crit
else: cwcrit = ''

if len(where_crit) > 6:
	wcrit = where_crit
else: wcrit = ''

if len(having_crit) > 7:
	hcrit = having_crit
else: hcrit = ''


#Connect to database server
dbconn = pg.connect(dbname, hostname, portno, opt_var, tty_var, username, userpasswd)

#Get list of crosstab column headers
colqry = 'select ' + colhdr + ' from ' + tablename + ' ' +  cwcrit + ' group by ' + colhdr + ' ';

col_list_a = dbconn.query(colqry)
col_list_b = col_list_a.getresult()
col_qry_str = ''

#Build code to place values in correct columns
for n in col_list_b:
	if n[0] == None:
		col_qry_str = col_qry_str + aggrfunc + '(case when ' + colhdr + ' is null then ' + valfld + ' end) as \"null_value\", '
	else:
		col_qry_str = col_qry_str + aggrfunc + '(case when ' + colhdr + '::text = \'' +  str(n[0]) + '\' then ' + valfld + ' end) as ' + '\"' + str(n[0]) + '\", '
col_qry_str = col_qry_str[:-2]

#Build the final query string
query_str = 'select ' + rowhdr + ' , ' + col_qry_str + 'into ' + dest + ' from ' + tablename + ' ' + wcrit + ' group by ' + rowhdr + ' ' + hcrit + ';'

#Send the final query to the database
xtab_qry = dbconn.query(query_str)

#Close the database connection
dbconn.close()