//return1row.c By Lee Lofgren 5/13/2003: Learn at your own risk. I've no idea
//what I'm doing. Heck, I don't even believe myself most of the time.
//I'm actually one of a group of monkeys working on Shakespear.
//I just make up C-coded giberish on my break.
//
//Return 1 row example. Return 1 row of data.
//This example shows how to return a row of columns from a function.
//
//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 return1row.c    makes return1row.o
//gcc -shared -o return1row.so return1row.o   makes shared library
//
//
//SQL Commands to install function:
//CREATE TYPE return1row_type AS ("column 1" text, "second column" text, "column 3" int4);
//CREATE FUNCTION return1row(text) RETURNS "return1row_type" AS '/workdir/return1row.so','return1row' LANGUAGE 'C';
//
//To use: SELECT * FROM return1row('hithere');
//
//WARNNIG: Common mistake
//SELECT return1row('hithere');   <--This returns ERROR: Cannot display a value of type RECORD
// because the function returns multiple columns!!!
//
//CREATE TYPE is used to define the number of return columns and their respective names
//In CREATE FUNCTION ... RETURNS <name used in CREATE TYPE> AS <directory and name of C program compiled as a shared library>,<name of routine in c program>
//
//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 "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "access/heapam.h"
#include "access/transam.h"
#include "utils/builtins.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(return1row);

Datum
return1row(PG_FUNCTION_ARGS)
{
	text	*nothing=PG_GETARG_TEXT_P(0);
	TupleDesc	tupleDescription; //Needed for column headers
	TupleTableSlot	*tupleTableSlot; //Needed for heap space to store info
	AttInMetadata	*attributesInMetaData; //Needed to convert C strings to Tuple
	HeapTuple	heapTuple;//Location of tuple on heap. Place where tuple gets placed when we fill it with data.
	char **buffer;//General purpuse buffer to show how to use palloc()
	int	numberOfColumns=0;
	Datum	datumAnswer;
	char column1[]="Column 1 info"; //return data for this test
	char column2[]="Column 2 info"; //return data for this test
	int column3=42;//return data for this test 
	int strLen;
	
	//We'll create a return set of 3 columns
	//This method retrieves descriptions from SQL TYPE
	//Requires SQL command CREATE TYPE return1row_type AS (column_1 TEXT, column_2 TEXT, column_3 INT4);
	numberOfColumns=3; //See above line
	
	tupleDescription = RelationNameGetTupleDesc("return1row_type"); //Does all the work of building the description. 
	
	tupleTableSlot=TupleDescGetSlot(tupleDescription);//Allocate a slot on the heap for this tuple
	
	attributesInMetaData = TupleDescGetAttInMetadata(tupleDescription); //Create a Meta attribute used to populate a tuple from C strings.
	
	
	//Going through pointless work to show postgres' palloc command. Use instead of malloc!!!
	//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.
	
	buffer=(char **)palloc(numberOfColumns*sizeof(char *));
	
	strLen=strlen(column1);
	buffer[0]=(char *)palloc(strLen+1);
	sprintf(buffer[0],"%.*s",strLen,column1);//using precision to limit string length. This limits the char * string length not the return length(obviously).
	strLen=strlen(column2);
	buffer[1]=(char *)palloc(strLen+1);
	sprintf(buffer[1],"%.*s",strLen,column2);
	strLen=11;//INT4 FFFFFFFF=4294967295(10 digits unsigned, 11 digits signed)
	buffer[2]=(char *)palloc(strLen+1);
	snprintf(buffer[2],strLen+1,"%11d",column3);//snprintf limits overall string length. Look under port/snprintf.c limits outstring to strLen by putting NULL at position specified by arg2
	
	heapTuple=BuildTupleFromCStrings(attributesInMetaData,buffer);//Build the tuple. Note attributesInMetadata contains the column headers
	
	datumAnswer=TupleGetDatum(tupleTableSlot,heapTuple);
	
	//Clean up some of the palloc()ed stuff
	pfree(buffer[1]);
	pfree(buffer[2]);
	//Didn't clean it all up to show that you don't have to clean it up. Though how will you really know whether it worked?
	
	PG_RETURN_DATUM(datumAnswer);
	
}//end return1row()