//funcgetsqlreturnrow.c
//
//Example written by Lee Lofgren (Savage, MN) of Accounting Enhancements Inc.
//5/16/2003
//lee@lofgrens.org
//
//Get a record through SQL and return 1 row example.
//This example shows how to retrieve the first record in a table through SQL and return a row of columns.
//
//To Compile:   NOTE: Use include directory from postgresql source instead of one in /usr/include/pgsql/server
//  because the make install in postgresql-7.3.2 doesn't update the include directory.
//  it does put the lib files in the /usr/lib directory now whereas it used to put them in /usr/lib/pgsql
//
//gcc -c -I/usr/local/postgresql-7.3.2/src/include funcgetsqlreturnrow.c    makes funcgetsqlreturnrow.o
//gcc -shared -o getsqlreturnrow.so getsqlreturnrow.o   makes shared library
//
//
//SQL Commands to install function:
//CREATE TYPE getsqlreturnrow_type AS ("Table Name" text, "Column Name" text, "Contents of Column" text);
//CREATE FUNCTION getsqlreturnrow(varchar, varchar) RETURNS "getsqlreturnrow_type" AS '/workdir/funcgetsqlreturnrow.so','getsqlreturnrow' LANGUAGE 'C';
//
//To use: SELECT * FROM getsqlreturnrow('pg_user','usename');
//
//WARNIG: Common mistake
//SELECT getsqlreturnrow('pg_proc','proname');   <--This returns ERROR: Cannot display a value of type RECORD
// because the function returns multiple columns!!!
//
//If, when running SELECT statement, you get Function getsqlreturnrow("unknown", "unknown") does not exist
//Then specify the variable type in the parameter list as follows:
//SELECT * FROM getsqlreturnrow(varchar 'pg_proc', varchar 'proname');
//I've seen this problem when accessing PostgreSQL from MSAccess more so than from psql.
//
//CREATE TYPE is used to define the number of return columns and their respective names
//for RETURNS portion of  CREATE FUNCTION (<arguments variable types>) RETURNS <name used in CREATE TYPE> AS <directory and name of C program compiled as a shared library usually ending in .so>,<name of routine in c program> LANGUAGE 'C'
//
//A good place to do research is in the following directories
///usr/local/postgresql-7.3.2/src/include (For available functions)
///usr/local/postgresql-7.3.2/contrib (For general C program examples)
///usr/local/postgresql-7.3.2/src/backend (For other useful C program examples)
//
//I suggest writing a search routine in bash to help look for example stuff
//The following script will make searching for things like "PG_FUNCTION_INFO_V1" easier
// Create file:  /usr/bin/search  
//   should contain           find . -exec grep -FHIins "$1" {} \;        <---the i in the grep command means ignore case so this isn't case sensitive
// chmod a+x /usr/bin/search        so has executable rights
//
//now when your in the /usr/local/postgresql-7.3.2/src/include directory
//and you want to know what PG_FUNCTION_INFO_V1 does:
//search "PG_FUNCTION_INFO_V1" returns 
// ./fmgr.h:258: *      PG_FUNCTION_INFO_V1(function_name);
// ./fmgr.h:278:#define PG_FUNCTION_INFO_V1(funcname) \
//
// or you can     search HeapTuple | less   to allow scrolling through all of the answers
// which you can't do when you use find . -exec grep -FHs HeapTuple {} \;|less   <--- Doesn't work!
//
//

#include <string.h>
#include <time.h>
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "access/heapam.h"
#include "access/transam.h"
#include "executor/executor.h"
#include "executor/spi.h"
#include "utils/builtins.h"
#include <string.h>

//#include "miscadmin.h" <-- This contains some global variables such as (char *DatabaseName = <name of database>) to which you have access


PG_FUNCTION_INFO_V1(getsqlreturnrow);

Datum
getsqlreturnrow(PG_FUNCTION_ARGS)
{
	//Parameters
	text	*tableNameArg=PG_GETARG_TEXT_P(0); //text type variables are struct {int4 length;char *string}. Char *string is not NULL terminated. length represents the length of the structure so includes the length of int4 
	text	*columnNameArg=PG_GETARG_TEXT_P(1);//The structure is not gaurantied so macros have been provided. VARSIZE(variable) = (int)length. VARHDRSZ = sizeof(length). VARDATA(variable)=(char *)string. VARSIZE(variable)-VARHDRSZ=length-of-string 
	
	//sql command variables
	char *sql=NULL;
	int sqlAlloc=0;
	int sqlLen=0;
	
	//selectedTable variables
	SPITupleTable *selectedTableTupleTable;
	TupleDesc selectedTableTupleDescription;
	HeapTuple selectedTableTuple;
	int selectedTableNumRows;
	
	//return result variables
	TupleDesc	resultTupleDescription; //Needed for column headers
	TupleTableSlot	*resultTupleTableSlot; //Needed for heap space to store info
	AttInMetadata	*resultAttributesInMetaData; //Needed to convert C strings to Tuple
	HeapTuple	resultHeapTuple;//Location of tuple on heap. Place where tuple gets placed when we fill it with data.
	char **result;//General purpuse buffer to show how to use palloc()
	Datum	resultDatum;
	enum {resultTableName,resultColumnName,resultContentsColumn}; //Using enum to assign resultTableName=0;resultColumnName=1;resultContentsColumn=2; It is much easier to read code such as result[resultColumnName] over result[1]
	
	char *genCharPtr; //Used to temporarily hold results from SPI_getvaue while making sure NULL wasn't returned.
	char *genSpiFnumberCharPtr; //Used to termorarily hold char * version of fieldnames for SPI_fnumber
	int status; //Result of SPI_exec()
	
	//*********START***********
	
	SPI_connect();//Open connection to backend
	
	sqlLen=strlen("SELECT * FROM \"%.*s\"")+VARSIZE(tableNameArg)-VARHDRSZ+1; //Find out how much room is needed for SELECT statement. Since %.*s isn't in the final string the alloced space is a little larger than needed, but changes copy-pasted to the sprintf statement are less likely to have typos.
	if (sqlAlloc<sqlLen){if(sql!=NULL){pfree(sql);} sql=palloc(sqlLen);} //If not enough alloced for sql then realloc more space. Since this is the first (and only) need for sql I didn't need to track my alloc size or use pfree, but this comes in handy if I had multiple queries!
	
	sprintf(sql,"SELECT * FROM \"%.*s\"",VARSIZE(tableNameArg)-VARHDRSZ,VARDATA(tableNameArg)); //Since VARDATA(tableNameArg) isn't NULL terminated, use the %.* precision option to select length of char string as part of the argument list.
	
	elog(INFO,"Sending SQL=> %s\n",sql);//Send message to the front end. If your using psql, you'll see these messages on the screen
	
	status=SPI_exec(sql,1);//process SQL statement. need heap space for only 1 return record since we don't care about the rest of the set.
	//Note: Using SPI_exec(sql,1) to return one record in statements like SELECT * FROM "tableABC" WHERE "abcCol"='hi' ORDER BY DESC will first construct the whole list limited by WHERE requirement, sort the list, then return the top record so you don't have to worry about SPI_exec(sql,1) returning the wrong first record by limiting the returns.. 
	
	elog(INFO,"Records found: %d\n",SPI_processed);
	
	selectedTableNumRows=0; if(status==SPI_OK_SELECT) {selectedTableNumRows=SPI_processed;} //Keep track of number of rows returned for no particular reason.
	
	if (status!=SPI_OK_SELECT||SPI_processed<1) {elog(ERROR,"selectedTable: Record not found. SQL = %s\n",sql);SPI_finish();PG_RETURN_NULL();}//Missing record
	selectedTableTupleTable=SPI_tuptable;//SPI_exec() set global SPI_ variables, save them in local variables so we can reuse SPI_exec without loosing previous results
	selectedTableTupleDescription=SPI_tuptable->tupdesc;//Get tuple description
	selectedTableTuple=selectedTableTupleTable->vals[0];//Get first record from table. Should only be one record!!!
	
	
	
	//This method retrieves descriptions from SQL TYPE
	//Requires SQL command CREATE TYPE getsqlreturnrow_type AS ("Table Name" text, "Column Name" text, "Contents of Column" text);
	
	resultTupleDescription = RelationNameGetTupleDesc("getsqlreturnrow_type"); //Does all the work of building the description. 
	
	resultTupleTableSlot=TupleDescGetSlot(resultTupleDescription);//Allocate a slot on the heap for this tuple
	
	resultAttributesInMetaData = TupleDescGetAttInMetadata(resultTupleDescription); //Create a Meta attribute used to populate a tuple from C strings.
	
	
	//palloc() will automatically free your pointers when you leave the function even if you forget to.
	//Careless use of malloc() causes memory leaks!
	//Use pfree() to free memory if you are doing recursive things or expect the function access lots of memory.
	
	result=(char **)palloc(3*sizeof(char **)); //Returning 3 columns
	
	result[resultContentsColumn]=palloc(1);result[resultContentsColumn][0]=0x0;//Creating a default of empty string to represent a posible previous setting.
	
	//Convert columnNameArg from (text) to (char *) so can use it in SPI_fnumber
	genSpiFnumberCharPtr=palloc(VARSIZE(columnNameArg)-VARHDRSZ+1);
	memcpy(genSpiFnumberCharPtr,VARDATA(columnNameArg),VARSIZE(columnNameArg)-VARHDRSZ);
	genSpiFnumberCharPtr[VARSIZE(columnNameArg)-VARHDRSZ]=0x0; //NULL terminate new string
	
	//SPI_getvalue() returns a (char *) NULL terminated representation of the requested column. bool={t,f}, int=atoi(genCharPtr), double=atof(genCharPtr), etc...
	//SPI_fnumber() returns the column number based on the column description. If you don't know the column number, then use the column name.
	genCharPtr=SPI_getvalue(selectedTableTuple,selectedTableTupleDescription,SPI_fnumber(selectedTableTupleDescription,genSpiFnumberCharPtr));//Get char * representation of column value. Use SPI_getbinval() for internal binary value.
	if (genCharPtr!=NULL){pfree(result[resultContentsColumn]);result[resultContentsColumn]=genCharPtr;genCharPtr=NULL;} //I used genCharPtr instead of result[resultContentsColumn] because I didn't want to loose the previous value unless this function returned a valid answer. In this example it wasn't needed and I could have set the result to zero len string after getting a NULL returned but in a more complicated program, I may have had a default answer.
	
	//Get Table Name
	result[resultTableName]=palloc(VARSIZE(tableNameArg)-VARHDRSZ+1);
	memcpy(result[resultTableName],VARDATA(tableNameArg),VARSIZE(tableNameArg)-VARHDRSZ);
	result[resultTableName][(VARSIZE(tableNameArg)-VARHDRSZ)]=0x0; //NULL terminate new string
	
	//Get Column Name
	result[resultColumnName]=palloc(VARSIZE(columnNameArg)-VARHDRSZ+1);
	memcpy(result[resultColumnName],VARDATA(columnNameArg),VARSIZE(columnNameArg)-VARHDRSZ);
	result[resultColumnName][VARSIZE(columnNameArg)-VARHDRSZ]=0x0; //NULL terminate new string
	
	resultHeapTuple=BuildTupleFromCStrings(resultAttributesInMetaData,result);//Build the tuple. Note resultAttributesInMetadata contains the column headers from above.
	
	resultDatum=TupleGetDatum(resultTupleTableSlot,resultHeapTuple); //Convert tuple to datum since must return datum
	
	//Don't need to clean up palloc()ed pointers. The routine calling this function will do it for me!
	
	//WARNING:    NOT calling SPI_finish() will cause a backend memory leak!!!!! There are exceptions to this rule.
	SPI_finish();//Close down connection to backend which frees all SPI created heap space since done! WARNING. The calling routine DOES NOT do this because this could be a multi-call routine. NOT calling this will cause a backend memory leak!
	
	elog(INFO,"Done\n");
	
	PG_RETURN_DATUM(resultDatum);
	
}//end getsqlreturnrow()