gpcontrib/orafce/dbms_sql.c

#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"

#if PG_VERSION_NUM < 120000

#include "access/htup_details.h"

#endif

#include "access/tupconvert.h"
#include "catalog/pg_type_d.h"
#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "lib/stringinfo.h"
#include "parser/parse_coerce.h"
#include "parser/scansup.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/elog.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
#include "utils/memutils.h"
#include "utils/typcache.h"
#include "executor/spi_priv.h"

#include "orafce.h"
#include "builtins.h"

#define MAX_CURSORS			100

/*
 * bind variable data
 */
typedef struct
{
	char	   *refname;
	int			position;

	Datum		value;

	Oid			typoid;
	bool		typbyval;
	int16		typlen;

	bool		isnull;
	unsigned int varno;		/* number of assigned placeholder of parsed query */
	bool		is_array;	/* true, when a value is assigned via bind_array */
	Oid			typelemid;	/* Oid of element of a array */
	bool		typelembyval;
	int16		typelemlen;
	int			index1;
	int			index2;
} VariableData;

/*
 * Query result column definition
 */
typedef struct
{
	int			position;

	Oid			typoid;
	bool		typbyval;
	int16		typlen;
	int32		typmod;
	bool		typisstr;
	Oid			typarrayoid;		/* oid of requested array output value */
	uint64		rowcount;			/* maximal rows of requested array */
	int			index1;				/* output array should be rewrited from this index */
} ColumnData;

/*
 * It is used for transformation result data to form
 * generated by column_value procedure or column
 * value function.
 */
typedef struct
{
	bool		isvalid;			/* true, when this cast can be used */
	bool		without_cast;		/* true, when cast is not necessary */
	Oid		targettypid;			/* used for domains */
	Oid		array_targettypid;		/* used for array domains */
	int32	targettypmod;			/* used for strings */
	bool	typbyval;				/* used for copy result to outer memory context */
	int16	typlen;					/* used for copy result to outer memory context */
	bool	is_array;

	Oid		funcoid;
	Oid		funcoid_typmod;
	CoercionPathType path;
	CoercionPathType path_typmod;
	FmgrInfo	finfo;
	FmgrInfo	finfo_typmod;
	FmgrInfo	finfo_out;
	FmgrInfo	finfo_in;
	Oid			typIOParam;
} CastCacheData;

/*
 * dbms_sql cursor definition
 */
typedef struct
{
	int16		cid;
	char	   *parsed_query;
	char	   *original_query;
	unsigned int nvariables;
	int			max_colpos;
	List	   *variables;
	List	   *columns;
	char		cursorname[32];
	Portal		portal;				/* one shot (execute) plan */
	SPIPlanPtr	plan;
	MemoryContext cursor_cxt;
	MemoryContext cursor_xact_cxt;
	MemoryContext tuples_cxt;
	MemoryContext result_cxt;		/* short life memory context */
	HeapTuple	tuples[1000];
	TupleDesc	coltupdesc;
	TupleDesc	tupdesc;
	CastCacheData *casts;
	uint64		processed;
	uint64		nread;
	uint64		start_read;
	bool		assigned;
	bool		executed;
	Bitmapset  *array_columns;		/* set of array columns */
	uint64		batch_rows;			/* how much rows should be fetched to fill target arrays */
} CursorData;

typedef enum
{
	TOKEN_SPACES,
	TOKEN_COMMENT,
	TOKEN_NUMBER,
	TOKEN_BIND_VAR,
	TOKEN_STR,
	TOKEN_EXT_STR,
	TOKEN_DOLAR_STR,
	TOKEN_IDENTIF,
	TOKEN_QIDENTIF,
	TOKEN_DOUBLE_COLON,
	TOKEN_OTHER,
	TOKEN_NONE
} orafceTokenType;

static char *next_token(char *str, char **start, size_t *len, orafceTokenType *typ, char **sep, size_t *seplen);

PG_FUNCTION_INFO_V1(dbms_sql_is_open);
PG_FUNCTION_INFO_V1(dbms_sql_open_cursor);
PG_FUNCTION_INFO_V1(dbms_sql_close_cursor);
PG_FUNCTION_INFO_V1(dbms_sql_parse);
PG_FUNCTION_INFO_V1(dbms_sql_bind_variable);
PG_FUNCTION_INFO_V1(dbms_sql_bind_variable_f);
PG_FUNCTION_INFO_V1(dbms_sql_bind_array_3);
PG_FUNCTION_INFO_V1(dbms_sql_bind_array_5);
PG_FUNCTION_INFO_V1(dbms_sql_define_column);
PG_FUNCTION_INFO_V1(dbms_sql_define_array);
PG_FUNCTION_INFO_V1(dbms_sql_execute);
PG_FUNCTION_INFO_V1(dbms_sql_fetch_rows);
PG_FUNCTION_INFO_V1(dbms_sql_execute_and_fetch);
PG_FUNCTION_INFO_V1(dbms_sql_column_value);
PG_FUNCTION_INFO_V1(dbms_sql_column_value_f);
PG_FUNCTION_INFO_V1(dbms_sql_last_row_count);
PG_FUNCTION_INFO_V1(dbms_sql_describe_columns);
PG_FUNCTION_INFO_V1(dbms_sql_describe_columns_f);
PG_FUNCTION_INFO_V1(dbms_sql_debug_cursor);

static uint64 last_row_count = 0;
static MemoryContext	persist_cxt = NULL;
static CursorData		cursors[MAX_CURSORS];

static void
open_cursor(CursorData *c, int cid)
{
	c->cid = cid;

	if (!persist_cxt)
	{
		persist_cxt = AllocSetContextCreate(NULL,
											"dbms_sql persist context",
											ALLOCSET_DEFAULT_SIZES);
		memset(cursors, 0, sizeof(cursors));
	}

	c->cursor_cxt = AllocSetContextCreate(persist_cxt,
														   "dbms_sql cursor context",
														   ALLOCSET_DEFAULT_SIZES);
	c->assigned = true;
}

/*
 * FUNCTION dbms_sql.open_cursor() RETURNS int
 */
Datum
dbms_sql_open_cursor(PG_FUNCTION_ARGS)
{
	int		i;

	(void) fcinfo;

	/* find and initialize first free slot */
	for (i = 0; i < MAX_CURSORS; i++)
	{
		if (!cursors[i].assigned)
		{
			open_cursor(&cursors[i], i);

			PG_RETURN_INT32(i);
		}
	}

	ereport(ERROR,
			(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
			 errmsg("too many opened cursors"),
			 errdetail("There is not free slot for new dbms_sql's cursor."),
			 errhint("You should to close unused cursors")));

	/* be msvc quiet */
	return (Datum) 0;
}

static CursorData *
get_cursor(FunctionCallInfo fcinfo, bool should_be_assigned)
{
	CursorData	   *cursor;
	int				cid;

	if (PG_ARGISNULL(0))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("cursor id is NULL")));

	cid = PG_GETARG_INT32(0);
	if (cid < 0 || cid >= MAX_CURSORS)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("value of cursor id is out of range")));

	cursor = &cursors[cid];
	if (!cursor->assigned && should_be_assigned)
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_CURSOR),
				 errmsg("cursor is not valid")));

	return cursor;
}

/*
 * CREATE FUNCTION dbms_sql.is_open(c int) RETURNS bool;
 */
Datum
dbms_sql_is_open(PG_FUNCTION_ARGS)
{
	CursorData	   *c;

	c = get_cursor(fcinfo, false);

	PG_RETURN_BOOL(c->assigned);
}

/*
 * Release all sources assigned to cursor
 */
static void
close_cursor(CursorData *c)
{
	if (c->executed && c->portal)
		SPI_cursor_close(c->portal);

	/* release all assigned memory */
	if (c->cursor_cxt)
		MemoryContextDelete(c->cursor_cxt);

	if (c->cursor_xact_cxt)
		MemoryContextDelete(c->cursor_xact_cxt);

	if (c->plan)
		SPI_freeplan(c->plan);

	memset(c, 0, sizeof(CursorData));
}

/*
 * PROCEDURE dbms_sql.close_cursor(c int)
 */
Datum
dbms_sql_close_cursor(PG_FUNCTION_ARGS)
{
	CursorData	   *c;

	c = get_cursor(fcinfo, false);

	close_cursor(c);

	return (Datum) 0;
}

/*
 * Print state of cursor - just for debug purposes
 */
Datum
dbms_sql_debug_cursor(PG_FUNCTION_ARGS)
{
	CursorData	   *c;
	ListCell	   *lc;

	c = get_cursor(fcinfo, false);

	if (c->assigned)
	{
		if (c->original_query)
			elog(NOTICE, "orig query: \"%s\"", c->original_query);

		if (c->parsed_query)
			elog(NOTICE, "parsed query: \"%s\"", c->parsed_query);

	}
	else
		elog(NOTICE, "cursor is not assigned");

	foreach(lc, c->variables)
	{
		VariableData *var = (VariableData *) lfirst(lc);

		if (var->typoid != InvalidOid)
		{
			if (!var->isnull)
			{
				Oid		typOutput;
				bool	isVarlena;
				char   *str;

				getTypeOutputInfo(var->typoid, &typOutput, &isVarlena);
				str = OidOutputFunctionCall(typOutput, var->value);

				elog(NOTICE, "variable \"%s\" is assigned to \"%s\"", var->refname, str);
			}
			else
				elog(NOTICE, "variable \"%s\" is NULL", var->refname);
		}
		else
			elog(NOTICE, "variable \"%s\" is not assigned", var->refname);
	}

	foreach(lc, c->columns)
	{
		ColumnData *col = (ColumnData *) lfirst(lc);

		elog(NOTICE, "column definition for position %d is %s",
					  col->position,
					  format_type_with_typemod(col->typoid, col->typmod));
	}

	return (Datum) 0;
}

/*
 * Search a variable in cursor's variable list
 */
static VariableData *
get_var(CursorData *c, char *refname, int position, bool append)
{
	ListCell	   *lc;

	foreach(lc, c->variables)
	{
		VariableData *var = (VariableData *) lfirst(lc);

		if (strcmp(var->refname, refname) == 0)
			return var;
	}

	if (append)
	{
		VariableData   *nvar;
		MemoryContext	oldcxt;

		oldcxt = MemoryContextSwitchTo(c->cursor_cxt);
		nvar = palloc0(sizeof(VariableData));

		nvar->refname = pstrdup(refname);
		nvar->varno = c->nvariables + 1;
		nvar->position = position;

		c->variables = lappend(c->variables, nvar);
		c->nvariables += 1;

		MemoryContextSwitchTo(oldcxt);

		return nvar;
	}
	else
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_PARAMETER),
				 errmsg("variable \"%s\" doesn't exists", refname)));

	/* be msvc quite */
	return NULL;
}

/*
 * PROCEDURE dbms_sql.parse(c int, stmt varchar)
 */
Datum
dbms_sql_parse(PG_FUNCTION_ARGS)
{
	char	   *query,
			   *ptr;
	char	   *start;
	size_t		len;
	orafceTokenType	typ;
	StringInfoData	sinfo;
	CursorData *c;
	MemoryContext oldcxt;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("parsed query string is NULL")));

	if (c->parsed_query)
	{
		int		cid = c->cid;

		close_cursor(c);
		open_cursor(c, cid);
	}

	query = text_to_cstring(PG_GETARG_TEXT_P(1));
	ptr = query;

	initStringInfo(&sinfo);

	while (ptr)
	{
		char	   *startsep;
		char	   *next_ptr;
		size_t		seplen;

		next_ptr = next_token(ptr, &start, &len, &typ, &startsep, &seplen);
		if (next_ptr)
		{
			if (typ == TOKEN_DOLAR_STR)
			{
				appendStringInfo(&sinfo, "%.*s", (int) seplen, startsep);
				appendStringInfo(&sinfo, "%.*s", (int) len, start);
				appendStringInfo(&sinfo, "%.*s", (int) seplen, startsep);
			}
			else if (typ == TOKEN_BIND_VAR)
			{
				char	   *name = downcase_identifier(start, (int) len, false, true);
				VariableData *var = get_var(c, name, (int) (ptr - query), true);

				appendStringInfo(&sinfo, "$%d", var->varno);

				pfree(name);
			}
			else if (typ == TOKEN_EXT_STR)
			{
				appendStringInfo(&sinfo, "e\'%.*s\'", (int) len, start);
			}
			else if (typ == TOKEN_STR)
			{
				appendStringInfo(&sinfo, "\'%.*s\'", (int) len, start);
			}
			else if (typ == TOKEN_QIDENTIF)
			{
				appendStringInfo(&sinfo, "\"%.*s\"", (int) len, start);
			}
			else if (typ != TOKEN_NONE)
			{
				appendStringInfo(&sinfo, "%.*s", (int) len, start);
			}
		}

		ptr = next_ptr;
	}

	/* save result to persist context */
	oldcxt = MemoryContextSwitchTo(c->cursor_cxt);
	c->original_query = pstrdup(query);
	c->parsed_query = pstrdup(sinfo.data);

	MemoryContextSwitchTo(oldcxt);

	pfree(query);
	pfree(sinfo.data);

	return (Datum) 0;
}

/*
 * Calling procedure can be slow, so there is a function alternative
 */
static Datum
bind_variable(PG_FUNCTION_ARGS)
{
	CursorData *c;
	VariableData *var;
	char *varname, *varname_downcase;
	Oid			valtype;
	bool		is_unknown = false;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("name of bind variable is NULL")));

	varname = text_to_cstring(PG_GETARG_TEXT_P(1));
	if (*varname == ':')
		varname += 1;

	varname_downcase = downcase_identifier(varname, (int) strlen(varname), false, true);
	var = get_var(c, varname_downcase, -1, false);

	valtype = get_fn_expr_argtype(fcinfo->flinfo, 2);
	if (valtype == RECORDOID)
		ereport(ERROR,
			    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
			     errmsg("cannot to bind a value of record type")));

	valtype = getBaseType(valtype);
	if (valtype == UNKNOWNOID)
	{
		is_unknown = true;
		valtype = TEXTOID;
	}

	if (var->typoid != InvalidOid)
	{
		if (!var->typbyval && !var->isnull)
		{
			pfree(DatumGetPointer(var->value));
			var->value = (Datum) 0;
		}

		var->isnull = true;
	}

	var->typoid = valtype;

	if (!PG_ARGISNULL(2))
	{
		MemoryContext	oldcxt;

		get_typlenbyval(var->typoid, &var->typlen, &var->typbyval);

		oldcxt = MemoryContextSwitchTo(c->cursor_cxt);

		if (is_unknown)
			var->value = CStringGetTextDatum(DatumGetPointer(PG_GETARG_DATUM(2)));
		else
			var->value = datumCopy(PG_GETARG_DATUM(2), var->typbyval, var->typlen);

		var->isnull = false;

		MemoryContextSwitchTo(oldcxt);
	}
	else
		var->isnull = true;

	return (Datum) 0;
}

/*
 * CREATE PROCEDURE dbms_sql.bind_variable(c int, name varchar2, value "any");
 */
Datum
dbms_sql_bind_variable(PG_FUNCTION_ARGS)
{
	return bind_variable(fcinfo);
}

/*
 * CREATE FUNCTION dbms_sql.bind_variable_f(c int, name varchar2, value "any") RETURNS void;
 */
Datum
dbms_sql_bind_variable_f(PG_FUNCTION_ARGS)
{
	return bind_variable(fcinfo);
}

static void
bind_array(FunctionCallInfo fcinfo, int index1, int index2)
{
	CursorData *c;
	VariableData *var;
	char *varname, *varname_downcase;
	Oid			valtype;
	Oid			elementtype;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("name of bind variable is NULL")));

	varname = text_to_cstring(PG_GETARG_TEXT_P(1));
	if (*varname == ':')
		varname += 1;

	varname_downcase = downcase_identifier(varname, (int) strlen(varname), false, true);
	var = get_var(c, varname_downcase, -1, false);

	valtype = get_fn_expr_argtype(fcinfo->flinfo, 2);
	if (valtype == RECORDOID)
		ereport(ERROR,
			    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
			     errmsg("cannot to bind a value of record type")));

	valtype = getBaseType(valtype);
	elementtype = get_element_type(valtype);

	if (!OidIsValid(elementtype))
		ereport(ERROR,
			    (errcode(ERRCODE_DATATYPE_MISMATCH),
			     errmsg("value is not a array")));

	if (var->typoid != InvalidOid)
	{
		if (!var->typbyval && !var->isnull)
		{
			pfree(DatumGetPointer(var->value));
			var->value = (Datum) 0;
		}

		var->isnull = true;
	}

	var->is_array = true;
	var->typoid = valtype;
	var->typelemid = elementtype;

	get_typlenbyval(elementtype, &var->typelemlen, &var->typelembyval);

	if (!PG_ARGISNULL(2))
	{
		MemoryContext	oldcxt;

		get_typlenbyval(var->typoid, &var->typlen, &var->typbyval);

		oldcxt = MemoryContextSwitchTo(c->cursor_cxt);

		var->value = datumCopy(PG_GETARG_DATUM(2), var->typbyval, var->typlen);

		var->isnull = false;

		MemoryContextSwitchTo(oldcxt);
	}
	else
		var->isnull = true;

	var->index1 = index1;
	var->index2 = index2;
}

/*
 * CREATE PROCEDURE dbms_sql.bind_array(c int, name varchar2, value anyarray);
 */
Datum
dbms_sql_bind_array_3(PG_FUNCTION_ARGS)
{
	bind_array(fcinfo, -1, -1);

	return (Datum) 0;
}

/*
 * CREATE PROCEDURE dbms_sql.bind_array(c int, name varchar2, value anyarray, index1 int, index2 int);
 */
Datum
dbms_sql_bind_array_5(PG_FUNCTION_ARGS)
{
	int		index1, index2;

	if (PG_ARGISNULL(3) || PG_ARGISNULL(4))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("index is NULL")));

	index1 = PG_GETARG_INT32(3);
	index2 = PG_GETARG_INT32(4);

	if (index1 < 0 || index2 < 0)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("index is below zero")));

	if (index1 > index2)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("index1 is greater than index2")));

	bind_array(fcinfo, index1, index2);

	return (Datum) 0;
}

static ColumnData *
get_col(CursorData *c, int position, bool append)
{
	ListCell	   *lc;

	foreach(lc, c->columns)
	{
		ColumnData *col = (ColumnData *) lfirst(lc);

		if (col->position == position)
			return col;
	}

	if (append)
	{
		ColumnData	   *ncol;
		MemoryContext	oldcxt;

		oldcxt = MemoryContextSwitchTo(c->cursor_cxt);
		ncol = palloc0(sizeof(ColumnData));

		ncol->position = position;
		if (c->max_colpos < position)
			c->max_colpos = position;

		c->columns = lappend(c->columns, ncol);

		MemoryContextSwitchTo(oldcxt);

		return ncol;
	}
	else
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_COLUMN),
				 errmsg("column no %d is not defined", position)));

	/* be msvc quite */
	return NULL;
}

/*
 * CREATE PROCEDURE dbms_sql.define_column(c int, col int, value "any", column_size int DEFAULT -1);
 */
Datum
dbms_sql_define_column(PG_FUNCTION_ARGS)
{
	CursorData *c;
	ColumnData *col;
	Oid			valtype;
	Oid			basetype;
	int		position;
	int		colsize;
	TYPCATEGORY category;
	bool	ispreferred;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("column position (number) is NULL")));

	position = PG_GETARG_INT32(1);
	col = get_col(c, position, true);

	valtype = get_fn_expr_argtype(fcinfo->flinfo, 2);
	if (valtype == RECORDOID)
		ereport(ERROR,
			    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
			     errmsg("cannot to define a column of record type")));

	if (valtype == UNKNOWNOID)
		valtype = TEXTOID;

	basetype = getBaseType(valtype);

	if (col->typoid != InvalidOid)
		ereport(ERROR,
			    (errcode(ERRCODE_DUPLICATE_COLUMN),
			     errmsg("column is defined already")));

	col->typoid = valtype;

	if (PG_ARGISNULL(3))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("column_size is NULL")));

	colsize = PG_GETARG_INT32(3);

	get_type_category_preferred(basetype, &category, &ispreferred);
	col->typisstr = category == TYPCATEGORY_STRING;
	col->typmod = (col->typisstr && colsize != -1) ? colsize + 4 : -1;

	get_typlenbyval(basetype, &col->typlen, &col->typbyval);

	col->rowcount = 1;

	return (Datum) 0;
}

/*
 * CREATE PROCEDURE dbms_sql.define_array(c int, col int, value "anyarray", rowcount int, index1 int);
 */
Datum
dbms_sql_define_array(PG_FUNCTION_ARGS)
{
	CursorData *c;
	ColumnData *col;
	Oid			valtype;
	Oid			basetype;
	int		position;
	int		rowcount;
	int		index1;
	Oid		elementtype;
	TYPCATEGORY category;
	bool	ispreferred;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("column position (number) is NULL")));

	position = PG_GETARG_INT32(1);
	col = get_col(c, position, true);

	valtype = get_fn_expr_argtype(fcinfo->flinfo, 2);
	if (valtype == RECORDOID)
		ereport(ERROR,
			    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
			     errmsg("cannot to define a column of record type")));

	get_type_category_preferred(valtype, &category, &ispreferred);
	if (category != TYPCATEGORY_ARRAY)
		elog(ERROR, "defined value is not array");

	col->typarrayoid = valtype;

	basetype = getBaseType(valtype);
	elementtype = get_element_type(basetype);

	if (!OidIsValid(elementtype))
		ereport(ERROR,
			    (errcode(ERRCODE_DATATYPE_MISMATCH),
			     errmsg("column is not a array")));

	if (col->typoid != InvalidOid)
		ereport(ERROR,
			    (errcode(ERRCODE_DUPLICATE_COLUMN),
			     errmsg("column is defined already")));

	col->typoid = elementtype;

	if (PG_ARGISNULL(3))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("cnt is NULL")));

	rowcount = PG_GETARG_INT32(3);
	if (rowcount <= 0)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("cnt is less or equal to zero")));

	col->rowcount = (uint64) rowcount;

	if (PG_ARGISNULL(4))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("lower_bnd is NULL")));

	index1 = PG_GETARG_INT32(4);
	if (index1 < 1)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("lower_bnd is less than one")));

	if (index1 != 1)
		ereport(ERROR,
			    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
			     errmsg("lower_bnd can be only only \"1\"")));

	col->index1 = index1;

	get_typlenbyval(col->typoid, &col->typlen, &col->typbyval);

	return (Datum) 0;
}

static void
cursor_xact_cxt_deletion_callback(void *arg)
{
	CursorData *cur = (CursorData *) arg;

	cur->cursor_xact_cxt = NULL;
	cur->result_cxt = NULL;
	cur->tuples_cxt = NULL;

	cur->processed = 0;
	cur->nread = 0;
	cur->executed = false;
	cur->tupdesc = NULL;
	cur->coltupdesc = NULL;
	cur->casts = NULL;
	cur->array_columns = NULL;
}

static uint64
execute(CursorData *c)
{
	last_row_count = 0;

	/* clean space with saved result */
	if (!c->cursor_xact_cxt)
	{
		MemoryContextCallback *mcb;
		MemoryContext oldcxt;

		c->cursor_xact_cxt = AllocSetContextCreate(TopTransactionContext,
												   "dbms_sql transaction context",
												   ALLOCSET_DEFAULT_SIZES);

		oldcxt = MemoryContextSwitchTo(c->cursor_xact_cxt);
		mcb = palloc0(sizeof(MemoryContextCallback));

		mcb->func = cursor_xact_cxt_deletion_callback;
		mcb->arg = c;

		MemoryContextRegisterResetCallback(c->cursor_xact_cxt, mcb);

		MemoryContextSwitchTo(oldcxt);
	}
	else
	{
		MemoryContext	save_cxt = c->cursor_xact_cxt;

		MemoryContextReset(c->cursor_xact_cxt);
		c->cursor_xact_cxt = save_cxt;

		c->casts = NULL;
		c->tupdesc = NULL;
		c->tuples_cxt = NULL;
	}

	c->result_cxt = AllocSetContextCreate(c->cursor_xact_cxt,
										  "dbms_sql short life context",
										  ALLOCSET_DEFAULT_SIZES);

	/*
	 * When column definitions are available, build final query
	 * and open cursor for fetching. When column definitions are
	 * missing, then the statement can be called with high frequency
	 * etc INSERT, UPDATE, so use cached plan.
	 */
	if (c->columns)
	{
		Datum	   *values;
		Oid		   *types;
		char	   *nulls;
		ListCell   *lc;
		int			i;
		MemoryContext oldcxt;
		uint64		batch_rows = 0;

		oldcxt = MemoryContextSwitchTo(c->cursor_xact_cxt);

		/* prepare query arguments */
		values = palloc(sizeof(Datum) * c->nvariables);
		types = palloc(sizeof(Oid) * c->nvariables);
		nulls = palloc(sizeof(char) * c->nvariables);

		i = 0;
		foreach(lc, c->variables)
		{
			VariableData *var = (VariableData *) lfirst(lc);

			if (var->is_array)
				ereport(ERROR,
					    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					     errmsg("a array (bulk) variable can be used only when no column is defined")));

			if (!var->isnull)
			{
				/* copy a value to xact memory context, to be independent on a outside */
				values[i] = datumCopy(var->value, var->typbyval, var->typlen);
				nulls[i] = ' ';
			}
			else
				nulls[i] = 'n';

			if (var->typoid == InvalidOid)
				ereport(ERROR,
					    (errcode(ERRCODE_UNDEFINED_PARAMETER),
					     errmsg("variable \"%s\" has not a value", var->refname)));

			types[i] = var->typoid;
			i += 1;
		}

		/* prepare or refresh target tuple descriptor, used for final tupconversion */
		if (c->tupdesc)
			FreeTupleDesc(c->tupdesc);

#if PG_VERSION_NUM >= 120000

		c->coltupdesc = CreateTemplateTupleDesc(c->max_colpos);

#else

		c->coltupdesc = CreateTemplateTupleDesc(c->max_colpos, false);

#endif

		/* prepare current result column tupdesc */
		for (i = 1; i <= c->max_colpos; i++)
		{
			ColumnData *col = get_col(c, i, false);
			char genname[32];

			snprintf(genname, 32, "col%d", i);

			Assert(col->rowcount > 0);

			if (col->typarrayoid)
			{

				if (batch_rows != 0)
					batch_rows = col->rowcount < batch_rows ? col->rowcount : batch_rows;
				else
					batch_rows = col->rowcount;

				c->array_columns = bms_add_member(c->array_columns, i);
			}
			else
			{
				/* in this case we cannot do batch of rows */
				batch_rows = 1;
			}

			TupleDescInitEntry(c->coltupdesc, (AttrNumber) i, genname, col->typoid, col->typmod, 0);
		}

		c->batch_rows = batch_rows;
		Assert(c->coltupdesc->natts >= 0);
		c->casts = palloc0(sizeof(CastCacheData) * ((unsigned int) c->coltupdesc->natts));

		MemoryContextSwitchTo(oldcxt);

		snprintf(c->cursorname, sizeof(c->cursorname), "__orafce_dbms_sql_cursor_%d", c->cid);

		if (SPI_connect() != SPI_OK_CONNECT)
			elog(ERROR, "SPI_connact failed");

		c->portal = SPI_cursor_open_with_args(c->cursorname,
											  c->parsed_query,
											  (int) c->nvariables,
											  types,
											  values,
											  nulls,
											  false,
											  0);

		/* internal error */
		if (c->portal == NULL)
			elog(ERROR,
				 "could not open cursor for query \"%s\": %s",
				 c->parsed_query,
				 SPI_result_code_string(SPI_result));

		SPI_finish();

		/* Describe portal and prepare cast cache */
		if (c->portal->tupDesc)
		{
			int		natts = 0;
			TupleDesc tupdesc = c->portal->tupDesc;

			for (i = 0; i < tupdesc->natts; i++)
			{
				Form_pg_attribute att = TupleDescAttr(tupdesc, i);

				if (att->attisdropped)
					continue;

				natts += 1;
			}

			if (natts != c->coltupdesc->natts)
				ereport(ERROR,
					    (errcode(ERRCODE_DATA_EXCEPTION),
					     errmsg("number of defined columns is different than number of query's columns")));
		}

		c->executed = true;
	}
	else
	{
		MemoryContext oldcxt;
		Datum	   *values;
		char	   *nulls;
		ArrayIterator *iterators;
		bool		has_iterator = false;
		bool		has_value = true;
		int			max_index1 = -1;
		int			min_index2 = -1;
		uint64		result = 0;
		ListCell   *lc;
		int			i;

		if (SPI_connect() != SPI_OK_CONNECT)
			elog(ERROR, "SPI_connact failed");

		/* prepare, or reuse cached plan */
		if (!c->plan)
		{
			Oid			   *types = NULL;
			SPIPlanPtr		plan;

			types = palloc(sizeof(Oid) * c->nvariables);

			i = 0;
			foreach(lc, c->variables)
			{
				VariableData *var = (VariableData *) lfirst(lc);

				if (var->typoid == InvalidOid)
					ereport(ERROR,
						    (errcode(ERRCODE_UNDEFINED_PARAMETER),
						     errmsg("variable \"%s\" has not a value", var->refname)));

				types[i++] = var->is_array ? var->typelemid : var->typoid;
			}

			plan = SPI_prepare(c->parsed_query, (int) c->nvariables, types);

			if (!plan)
				/* internal error */
				elog(ERROR, "cannot to prepare plan");

			if (types)
				pfree(types);

			SPI_keepplan(plan);

			c->plan = plan;
		}

		oldcxt = MemoryContextSwitchTo(c->result_cxt);

		/* prepare query arguments */
		values = palloc(sizeof(Datum) * c->nvariables);
		nulls = palloc(sizeof(char) * c->nvariables);
		iterators = palloc(sizeof(ArrayIterator *) * c->nvariables);

		has_value = true;

		i = 0;
		foreach(lc, c->variables)
		{
			VariableData *var = (VariableData *) lfirst(lc);

			if (var->is_array)
			{
				if (!var->isnull)
				{
					iterators[i] = array_create_iterator(DatumGetArrayTypeP(var->value),
														 0,
														 NULL);

					/* search do lowest common denominator */
					if (var->index1 != -1)
					{
						if (max_index1 != -1)
						{
							max_index1 = max_index1 < var->index1 ? var->index1 : max_index1;
							min_index2 = min_index2 > var->index2 ? var->index2 : min_index2;
						}
						else
						{
							max_index1 = var->index1;
							min_index2 = var->index2;
						}
					}

					has_iterator = true;

				}
				else
				{
					/* cannot to read data from NULL array */
					has_value = false;
					break;
				}
			}
			else
			{
				values[i] = var->value;
				nulls[i] = var->isnull ? 'n' : ' ';
			}

			i += 1;
		}

		if (has_iterator)
		{
			int			max_rows = -1;

			if (has_value)
			{
				if (max_index1 != -1)
				{
					max_rows = min_index2 - max_index1 + 1;
					has_value = max_rows > 0;

					if (has_value && max_index1 > 1)
					{
						i = 0;
						foreach(lc, c->variables)
						{
							VariableData *var = (VariableData *) lfirst(lc);

							if (var->is_array)
							{
								int		j;

								Assert(iterators[i]);

								for (j = 1; j < max_index1; j++)
								{
									Datum		value;
									bool		isnull;

									has_value = array_iterate(iterators[i], &value, &isnull);
									if (!has_value)
										break;
								}

								if (!has_value)
									break;
							}

							i += 1;
						}
					}
				}
			}

			while (has_value && (max_rows == -1 || max_rows > 0))
			{
				int			rc;

				i = 0;
				foreach(lc, c->variables)
				{
					VariableData *var = (VariableData *) lfirst(lc);

					if (var->is_array)
					{
						Datum		value;
						bool		isnull;

						has_value = array_iterate(iterators[i], &value, &isnull);
						if (!has_value)
							break;

						values[i] = value;
						nulls[i] = isnull ? 'n' : ' ';
					}

					i += 1;
				}
				if (!has_value)
					break;

				rc = SPI_execute_plan(c->plan, values, nulls, false, 0);
				if (rc < 0)
					/* internal error */
					elog(ERROR, "cannot to execute a query");

				result += SPI_processed;

				if (max_rows > 0)
					max_rows -= 1;
			}

			MemoryContextReset(c->result_cxt);
		}
		else if (has_value)
		{
			int			rc;

			rc = SPI_execute_plan(c->plan, values, nulls, false, 0);
			if (rc < 0)
				/* internal error */
				elog(ERROR, "cannot to execute a query");

			result = SPI_processed;
		}

		SPI_finish();

		MemoryContextSwitchTo(oldcxt);

		return result;
	}

	return 0L;
}

/*
 * CREATE FUNCTION dbms_sql.execute(c int) RETURNS bigint;
 */
Datum
dbms_sql_execute(PG_FUNCTION_ARGS)
{
	CursorData *c;

	c = get_cursor(fcinfo, true);

	PG_RETURN_INT64((int64) execute(c));
}

static uint64
fetch_rows(CursorData *c, bool exact)
{
	uint64		can_read_rows;

	if (!c->executed)
		ereport(ERROR,
			    (errcode(ERRCODE_INVALID_CURSOR_STATE),
			     errmsg("cursor is not executed")));

	if (!c->portal)
		ereport(ERROR,
			    (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
			     errmsg("cursor has not portal")));

	if (c->nread == c->processed)
	{
		MemoryContext	oldcxt;
		uint64		i;
		int			batch_rows;

		if (!exact)
		{
			if (c->array_columns)
				batch_rows = (1000 / c->batch_rows) * c->batch_rows;
			else
				batch_rows = 1000;
		}
		else
			batch_rows = 2;

		/* create or reset context for tuples */
		if (!c->tuples_cxt)
			c->tuples_cxt = AllocSetContextCreate(c->cursor_xact_cxt,
												  "dbms_sql tuples context",
												  ALLOCSET_DEFAULT_SIZES);
		else
			MemoryContextReset(c->tuples_cxt);

		if (SPI_connect() != SPI_OK_CONNECT)
			elog(ERROR, "SPI_connact failed");

		/* try to fetch data from cursor */
		SPI_cursor_fetch(c->portal, true, batch_rows);

		if (SPI_tuptable == NULL)
			elog(ERROR, "cannot fetch data");

		if (exact && SPI_processed > 1)
			ereport(ERROR,
				    (errcode(ERRCODE_TOO_MANY_ROWS),
				     errmsg("too many rows"),
				     errdetail("In exact mode only one row is expected")));

		if (exact && SPI_processed == 0)
			ereport(ERROR,
				    (errcode(ERRCODE_NO_DATA_FOUND),
				     errmsg("no data found"),
				     errdetail("In exact mode only one row is expected")));

		oldcxt = MemoryContextSwitchTo(c->tuples_cxt);

		c->tupdesc = CreateTupleDescCopy(SPI_tuptable->tupdesc);

		for (i = 0; i < SPI_processed; i++)
			c->tuples[i] = heap_copytuple(SPI_tuptable->vals[i]);

		MemoryContextSwitchTo(oldcxt);

		c->processed = SPI_processed;
		c->nread = 0;

		SPI_finish();
	}

	if (c->processed - c->nread >= c->batch_rows)
		can_read_rows = c->batch_rows;
	else
		can_read_rows = c->processed - c->nread;

	c->start_read = c->nread;
	c->nread += can_read_rows;

	last_row_count = can_read_rows;

	return can_read_rows;
}

/*
 * CREATE FUNCTION dbms_sql.fetch_rows(c int) RETURNS int;
 */
Datum
dbms_sql_fetch_rows(PG_FUNCTION_ARGS)
{
	CursorData *c;

	c = get_cursor(fcinfo, true);

	PG_RETURN_INT32(fetch_rows(c, false));
}

/*
 * CREATE FUNCTION dbms_sql.execute_and_fetch(c int, exact bool DEFAULT false) RETURNS int;
 */
Datum
dbms_sql_execute_and_fetch(PG_FUNCTION_ARGS)
{
	CursorData *c;
	bool		exact;

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("exact option is NULL")));

	exact = PG_GETARG_BOOL(1);

	execute(c);

	PG_RETURN_INT32(fetch_rows(c, exact));
}

/*
 * CREATE FUNCTION dbms_sql.last_row_count() RETURNS int;
 */
Datum
dbms_sql_last_row_count(PG_FUNCTION_ARGS)
{
	(void) fcinfo;

	PG_RETURN_INT32(last_row_count);
}

/*
 * Initialize cast case entry.
 */
static void
init_cast_cache_entry(CastCacheData *ccast,
					  Oid targettypid,
					  int32 targettypmod,
					  Oid sourcetypid)
{
	Oid		funcoid;
	Oid		basetypid;

	basetypid = getBaseType(targettypid);

	if (targettypid != basetypid)
		ccast->targettypid = targettypid;
	else
		ccast->targettypid = InvalidOid;

	ccast->targettypmod = targettypmod;

	if (sourcetypid == targettypid)
		ccast->without_cast = targettypmod == -1;
	else
		ccast->without_cast = false;

	if (!ccast->without_cast)
	{
		ccast->path = find_coercion_pathway(basetypid,
											sourcetypid,
											COERCION_ASSIGNMENT,
											&funcoid);

		if (ccast->path == COERCION_PATH_NONE)
			ereport(ERROR,
			    (errcode(ERRCODE_CANNOT_COERCE),
			     errmsg("cannot to find cast from source type \"%s\" to target type \"%s\"",
						 format_type_be(sourcetypid),
						 format_type_be(basetypid))));

		if (ccast->path == COERCION_PATH_FUNC)
		{
			fmgr_info(funcoid, &ccast->finfo);
		}
		else if (ccast->path == COERCION_PATH_COERCEVIAIO)
		{
			bool	typisvarlena;

			getTypeOutputInfo(sourcetypid, &funcoid, &typisvarlena);
			fmgr_info(funcoid, &ccast->finfo_out);

			getTypeInputInfo(targettypid, &funcoid, &ccast->typIOParam);
			fmgr_info(funcoid, &ccast->finfo_in);
		}

		if (targettypmod != -1)
		{
			ccast->path_typmod = find_typmod_coercion_function(targettypid,
															   &funcoid);
			if (ccast->path_typmod == COERCION_PATH_FUNC)
				fmgr_info(funcoid, &ccast->finfo_typmod);
		}
	}

	ccast->isvalid = true;
}

/*
 * Apply cast rules to a value
 */
static Datum
cast_value(CastCacheData *ccast, Datum value, bool isnull)
{
	if (!isnull && !ccast->without_cast)
	{
		if (ccast->path == COERCION_PATH_FUNC)
			value = FunctionCall1(&ccast->finfo, value);
		else if (ccast->path == COERCION_PATH_RELABELTYPE)
		{
			/* do nothing */
		}
		else if (ccast->path == COERCION_PATH_COERCEVIAIO)
		{
			char *str;

			str = OutputFunctionCall(&ccast->finfo_out, value);
			value = InputFunctionCall(&ccast->finfo_in,
									  str,
									  ccast->typIOParam,
									  ccast->targettypmod);
		}
		else
			ereport(ERROR,
				    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
				     errmsg("unsupported cast path yet %d", ccast->path)));

		if (ccast->targettypmod != -1 && ccast->path_typmod == COERCION_PATH_FUNC)
			value = FunctionCall3(&ccast->finfo_typmod,
								  value,
								  Int32GetDatum(ccast->targettypmod),
								  BoolGetDatum(true));
	}

	if (ccast->targettypid != InvalidOid)
		domain_check(value, isnull, ccast->targettypid, NULL, NULL);

	return value;
}

/*
 * CALL statement is relatily slow in PLpgSQL - due repated parsing, planning.
 * So I wrote two variant of this routine. When spi_transfer is true, then
 * the value is copyied to SPI outer memory context.
 */
static Datum
column_value(CursorData *c, int pos, Oid targetTypeId, bool *isnull, bool spi_transfer)
{
	Datum		value;
	int32		columnTypeMode;
	Oid			columnTypeId;
	CastCacheData *ccast;

	Assert(c->executed);

	if (last_row_count == 0)
		ereport(ERROR,
			    (errcode(ERRCODE_NO_DATA_FOUND),
			     errmsg("no data found")));

	if (!c->tupdesc)
		ereport(ERROR,
			    (errcode(ERRCODE_INVALID_CURSOR_STATE),
			     errmsg("cursor is not fetched")));

	if (!c->coltupdesc)
		ereport(ERROR,
			    (errcode(ERRCODE_UNDEFINED_COLUMN),
			     errmsg("no column is defined")));

	if (pos < 1 && pos > c->coltupdesc->natts)
		ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				 errmsg("column position is of of range [1, %d]",
						c->coltupdesc->natts)));

	columnTypeId = (TupleDescAttr(c->coltupdesc, pos - 1))->atttypid;
	columnTypeMode = (TupleDescAttr(c->coltupdesc, pos - 1))->atttypmod;

	Assert(c->casts);
	ccast = &c->casts[pos - 1];

	if (!ccast->isvalid)
	{
		Oid		basetype = getBaseType(targetTypeId);
	
		init_cast_cache_entry(ccast,
							  columnTypeId,
							  columnTypeMode,
							  SPI_gettypeid(c->tupdesc, pos));

		ccast->is_array = bms_is_member(pos, c->array_columns);

		if (ccast->is_array)
		{
			ccast->array_targettypid = basetype != targetTypeId ? targetTypeId : InvalidOid;

			if (get_array_type(getBaseType(columnTypeId)) != basetype)
				ereport(ERROR,
					    (errcode(ERRCODE_DATATYPE_MISMATCH),
					     errmsg("unexpected target type \"%s\" (expected type \"%s\")",
								format_type_be(basetype),
								format_type_be(get_array_type(getBaseType(columnTypeId))))));
		}
		else
			ccast->array_targettypid = InvalidOid;

		get_typlenbyval(basetype, &ccast->typlen, &ccast->typbyval);
	}

	if (ccast->is_array)
	{
		ArrayBuildState *abs;
		uint64		idx;
		uint64		i;

		abs = initArrayResult(columnTypeId, CurrentMemoryContext, false);

		idx = c->start_read;

		for (i = 0; i < c->batch_rows; i++)
		{
			if (idx < c->processed)
			{
				value = SPI_getbinval(c->tuples[idx], c->tupdesc, pos, isnull);
				value = cast_value(ccast, value, *isnull);

				abs = accumArrayResult(abs,
									   value,
									   *isnull,
									   columnTypeId,
									   CurrentMemoryContext);

				idx += 1;
			}
		}

		value = makeArrayResult(abs, CurrentMemoryContext);

		if (ccast->array_targettypid != InvalidOid)
			domain_check(value, isnull, ccast->array_targettypid, NULL, NULL);
	}
	else
	{
		/* Maybe it can be solved by uncached slower cast */
		if (targetTypeId != columnTypeId)
				ereport(ERROR,
					    (errcode(ERRCODE_DATATYPE_MISMATCH),
					     errmsg("unexpected target type \"%s\" (expected type \"%s\")",
								format_type_be(targetTypeId),
								format_type_be(columnTypeId))));

		value = SPI_getbinval(c->tuples[c->start_read], c->tupdesc, pos, isnull);

		value = cast_value(ccast, value, *isnull);
	}

	if (1)//spi_transfer)
		value = SPI_datumTransfer(value, ccast->typbyval, ccast->typlen);

	return value;
}

/*
 * CREATE PROCEDURE dbms_sql.column_value(c int, pos int, INOUT value "any");
 * Note - CALL statement is slow from PLpgSQL block (against function execution).
 * This is reason why this routine is in function form too.
 */
Datum
dbms_sql_column_value(PG_FUNCTION_ARGS)
{
	CursorData *c;
	Datum		value;
	Datum		result;
	int			pos;
	bool		isnull;
	Oid			targetTypeId;
	Oid			resultTypeId;
	TupleDesc	resulttupdesc;
	HeapTuple	resulttuple;
	MemoryContext	oldcxt;

	if (SPI_connect() != SPI_OK_CONNECT)
		elog(ERROR, "SPI_connact failed");

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("column position (number) is NULL")));

	pos = PG_GETARG_INT32(1);

	if (!c->executed)
		ereport(ERROR,
			    (errcode(ERRCODE_INVALID_CURSOR_STATE),
			     errmsg("cursor is not executed")));

	oldcxt = MemoryContextSwitchTo(c->result_cxt);


	/*
	 * Setting of OUT field is little bit more complex, because although
	 * there is only one output field, the result should be compisite type.
	 */
	if (get_call_result_type(fcinfo, &resultTypeId, &resulttupdesc) == TYPEFUNC_COMPOSITE)
	{
		/* check target types */
		if (resulttupdesc->natts != 1)
			/* internal error, should not to be */
			elog(ERROR, "unexpected number of result composite fields");

		targetTypeId = get_fn_expr_argtype(fcinfo->flinfo, 2);
		Assert((TupleDescAttr(resulttupdesc, 0))->atttypid == targetTypeId);
	}
	else
		/* internal error, should not to be */
		elog(ERROR, "unexpected function result type");

	value = column_value(c, pos, targetTypeId, &isnull, false);

	resulttuple = heap_form_tuple(resulttupdesc, &value, &isnull);
	result = PointerGetDatum(SPI_returntuple(resulttuple, CreateTupleDescCopy(resulttupdesc)));

	SPI_finish();

	MemoryContextSwitchTo(oldcxt);
	MemoryContextReset(c->result_cxt);

	PG_RETURN_DATUM(result);
}

/*
 * CREATE FUNCTION dbms_sql.column_value(c int, pos int, value anyelement) RETURNS anyelement;
 * Note - CALL statement is slow from PLpgSQL block (against function execution).
 * This is reason why this routine is in function form too.
 */
Datum
dbms_sql_column_value_f(PG_FUNCTION_ARGS)
{
	CursorData *c;
	Datum		value;
	int			pos;
	bool		isnull;
	Oid			targetTypeId;
	MemoryContext	oldcxt;

	if (SPI_connect() != SPI_OK_CONNECT)
		elog(ERROR, "SPI_connact failed");

	c = get_cursor(fcinfo, true);

	if (PG_ARGISNULL(1))
		ereport(ERROR,
			    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			     errmsg("column position (number) is NULL")));

	pos = PG_GETARG_INT32(1);

	if (!c->executed)
		ereport(ERROR,
			    (errcode(ERRCODE_INVALID_CURSOR_STATE),
			     errmsg("cursor is not executed")));

	oldcxt = MemoryContextSwitchTo(c->result_cxt);

	targetTypeId = get_fn_expr_argtype(fcinfo->flinfo, 2);

	value = column_value(c, pos, targetTypeId, &isnull, true);

	SPI_finish();

	MemoryContextSwitchTo(oldcxt);

	PG_RETURN_DATUM(value);
}

/******************************************************************
 * Simple parser - just for replacement of bind variables by
 * PostgreSQL $ param placeholders.
 *
 ******************************************************************
 */

/*
 * It doesn't work for multibyte encodings, but same implementation
 * is in Postgres too.
 */
static bool
is_identif(unsigned char c)
{
	if (c >= 'a' && c <= 'z')
		return true;
	else if (c >= 'A' && c <= 'Z')
		return true;
	else if (c >= 0200)
		return true;
	else
		return false;
}

/*
 * simple parser to detect :identif symbols in query
 */
static char *
next_token(char *str, char **start, size_t *len, orafceTokenType *typ, char **sep, size_t *seplen)
{
	if (*str == '\0')
	{
		*typ = TOKEN_NONE;
		return NULL;
	}

	/* reduce spaces */
	if (*str == ' ')
	{
		*start = str++;
		while (*str == ' ')
			str++;

		*typ = TOKEN_SPACES;
		*len = 1;
		return str;
	}

	/* Postgres's dolar strings */
	if (*str == '$' && (str[1] == '$' ||
		is_identif((unsigned char) str[1]) || str[1] == '_'))
	{
		char	   *aux = str + 1;
		char	   *endstr;
		bool		is_valid = false;
		char	   *buffer;

		/* try to find end of separator */
		while (*aux)
		{
			if (*aux == '$')
			{
				is_valid = true;
				aux++;
				break;
			}
			else if (is_identif((unsigned char) *aux) ||
					 isdigit(*aux) ||
					 *aux == '_')
			{
				aux++;
			}
			else
				break;
		}

		if (!is_valid)
		{
			*typ = TOKEN_OTHER;
			*len = 1;
			*start = str;
			return str + 1;
		}

		/* now it looks like correct $ separator */
		*start = aux; *sep = str;
		Assert(aux >= str);
		*seplen = (size_t) (aux - str);
		*typ = TOKEN_DOLAR_STR;

		/* try to find second instance */
		buffer = palloc(*seplen + 1);
		memcpy(buffer, *sep, *seplen);
		buffer[*seplen] = '\0';

		endstr = strstr(aux, buffer);
		if (endstr)
		{
			Assert(endstr >= *start);
			*len = (size_t) (endstr - *start);
			return endstr + *seplen;
		}
		else
		{
			while (*aux)
				aux++;

			Assert(aux >= *start);
			*len = (size_t) (aux - *start);
			return aux;
		}

		return aux;
	}

	/* Pair comments */
	if (*str == '/' && str[1] == '*')
	{
		*start = str; str += 2;
		while (*str)
		{
			if (*str == '*' && str[1] == '/')
			{
				str += 2;
				break;
			}
			str++;
		}
		*typ = TOKEN_COMMENT;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Number */
	if (isdigit(*str) || (*str == '.' && isdigit(str[1])))
	{
		bool	point = *str == '.';

		*start = str++;
		while (*str)
		{
			if (isdigit(*str))
				str++;
			else if (*str == '.' && !point)
			{
				str++; point = true;
			}
			else
				break;
		}
		*typ = TOKEN_NUMBER;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Double colon :: */
	if (*str == ':' && str[1] == ':')
	{
		*start = str;
		*typ = TOKEN_DOUBLE_COLON;
		*len = 2;
		return str + 2;
	}

	/* Bind variable placeholder */
	if (*str == ':' &&
		(is_identif((unsigned char) str[1]) || str[1] == '_'))
	{
		*start = &str[1]; str += 2;
		while (*str)
		{
			if (is_identif((unsigned char) *str) ||
				isdigit(*str) ||
				*str == '_')
				str++;
			else
				break;
		}
		*typ = TOKEN_BIND_VAR;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Extended string literal */
	if ((*str == 'e' || *str == 'E') && str[1] == '\'')
	{
		*start = &str[2]; str += 2;
		while (*str)
		{
			if (*str == '\'')
			{
				*typ = TOKEN_EXT_STR;
				Assert(str >= *start);
				*len = (size_t) (str - *start);
				return str + 1;
			}
			if (*str == '\\' && str[1] == '\'')
				str += 2;
			else if (*str == '\\' && str[1] == '\\')
				str += 2;
			else
				str += 1;
		}

		*typ = TOKEN_EXT_STR;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* String literal */
	if (*str == '\'')
	{
		*start = &str[1]; str += 1;
		while (*str)
		{
			if (*str == '\'')
			{
				if (str[1] != '\'')
				{
					*typ = TOKEN_STR;
					Assert(str >= *start);
					*len = (size_t) (str - *start);
					return str + 1;
				}
				str += 2;
			}
			else
				str += 1;
		}
		*typ = TOKEN_STR;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Quoted identifier */
	if (*str == '"')
	{
		*start = &str[1]; str += 1;
		while (*str)
		{
			if (*str == '"')
			{
				if (str[1] != '"')
				{
					*typ = TOKEN_QIDENTIF;
					Assert(str >= *start);
					*len = (size_t) (str - *start);
					return str + 1;
				}
				str += 2;
			}
			else
				str += 1;
		}
		*typ = TOKEN_QIDENTIF;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Identifiers */
	if (is_identif((unsigned char) *str) || *str == '_')
	{
		*start = str++;
		while (*str)
		{
			if (is_identif((unsigned char) *str) ||
				isdigit(*str) ||
				*str == '_')
				str++;
			else
				break;
		}
		*typ = TOKEN_IDENTIF;
		Assert(str >= *start);
		*len = (size_t) (str - *start);
		return str;
	}

	/* Others */
	*typ = TOKEN_OTHER;
	*start = str;
	*len = 1;
	return str + 1;
}

/*
 * CREATE PROCEDURE dbms_sql.describe_columns(c int, OUT col_cnt int, OUT desc_t dbms_sql.desc_rec[])
 *
 * Returns an array of column's descriptions. Attention, the typid are related to PostgreSQL type
 * system.
 */
Datum
dbms_sql_describe_columns(PG_FUNCTION_ARGS)
{
	CursorData *c;
	Datum		values[13];
	bool		nulls[13];
	TupleDesc	tupdesc;
	TupleDesc	desc_rec_tupdesc;
	TupleDesc	cursor_tupdesc;
	HeapTuple	tuple;
	Oid			arraytypid;
	Oid			desc_rec_typid;
	Oid		   *types = NULL;
	ArrayBuildState *abuilder;
	SPIPlanPtr		plan;
	CachedPlanSource *plansource = NULL;
	int			ncolumns = 0;
	int			rc;
	int			i = 0;
	bool		nonatomic;
	MemoryContext callercxt = CurrentMemoryContext;

	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
		elog(ERROR, "return type must be a row type");

	arraytypid = TupleDescAttr(tupdesc, 1)->atttypid;
	desc_rec_typid = get_element_type(arraytypid);

	if (!OidIsValid(desc_rec_typid))
		elog(ERROR, "second output field must be an array");

	desc_rec_tupdesc = lookup_rowtype_tupdesc_copy(desc_rec_typid, -1);

	abuilder = initArrayResult(desc_rec_typid, callercxt, true);

	c = get_cursor(fcinfo, true);

	if (c->variables)
	{
		ListCell *lc;

		types = palloc(sizeof(Oid) * c->nvariables);
		i = 0;

		foreach(lc, c->variables)
		{
			VariableData *var = (VariableData *) lfirst(lc);

			if (var->typoid == InvalidOid)
				ereport(ERROR,
					    (errcode(ERRCODE_UNDEFINED_PARAMETER),
					     errmsg("variable \"%s\" has not a value", var->refname)));

			types[i++] = var->is_array ? var->typelemid : var->typoid;
		}
	}

	/*
	 * Connect to SPI manager
	 */
	nonatomic = fcinfo->context &&
		IsA(fcinfo->context, CallContext) &&
		!castNode(CallContext, fcinfo->context)->atomic;

	if ((rc = SPI_connect_ext(nonatomic ? SPI_OPT_NONATOMIC : 0)) != SPI_OK_CONNECT)
		elog(ERROR, "SPI_connect failed: %s", SPI_result_code_string(rc));

	plan = SPI_prepare(c->parsed_query, (int) c->nvariables, types);
	if (!plan || plan->magic != _SPI_PLAN_MAGIC)
		elog(ERROR, "plan is not valid");

	if (list_length(plan->plancache_list) != 1)
		elog(ERROR, "plan is not single execution plany");

	plansource = (CachedPlanSource *) linitial(plan->plancache_list);
	cursor_tupdesc = plansource->resultDesc;

	ncolumns = cursor_tupdesc->natts;

	for (i = 0; i < ncolumns; i++)
	{
		HeapTuple	tp;
		Form_pg_type typtup;
		text	*txt;

		Form_pg_attribute attr = TupleDescAttr(cursor_tupdesc, i);

		/*
		 * 0. col_type            BINARY_INTEGER := 0,
		 * 1. col_max_len         BINARY_INTEGER := 0,
		 * 2. col_name            VARCHAR2(32)   := '',
		 * 3. col_name_len        BINARY_INTEGER := 0,
		 * 4. col_schema_name     VARCHAR2(32)   := '',
		 * 5. col_schema_name_len BINARY_INTEGER := 0,
		 * 6. col_precision       BINARY_INTEGER := 0,
		 * 7. col_scale           BINARY_INTEGER := 0,
		 * 8. col_charsetid       BINARY_INTEGER := 0,
		 * 9. col_charsetform     BINARY_INTEGER := 0,
		 * 10. col_null_ok        BOOLEAN        := TRUE
		 * 11. col_type_name      varchar2       := '',
		 * 12. col_type_name_len  BINARY_INTEGER := 0 );
		 */

		values[0] = Int32GetDatum(attr->atttypid);

		tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(attr->atttypid));
		if (!HeapTupleIsValid(tp))
			elog(ERROR, "cache lookup failed for type %u", attr->atttypid);

		typtup = (Form_pg_type) GETSTRUCT(tp);

		values[1] = Int32GetDatum(0);
		values[6] = Int32GetDatum(0);
		values[7] = Int32GetDatum(0);

		if (attr->attlen != -1)
			values[1] = Int32GetDatum(attr->attlen);
		else if (typtup->typcategory == 'S' && attr->atttypmod > VARHDRSZ)
			values[1] = Int32GetDatum(attr->atttypmod - VARHDRSZ);
		else if (attr->atttypid == NUMERICOID && attr->atttypmod > VARHDRSZ)
		{
			values[6] = Int32GetDatum(((attr->atttypmod - VARHDRSZ) >> 16) & 0xffff);
			values[7] = Int32GetDatum((((attr->atttypmod - VARHDRSZ) & 0x7ff) ^ 1024) - 1024);
		}

		txt = cstring_to_text(NameStr(attr->attname));
		values[2] = PointerGetDatum(txt);
		values[3] = DirectFunctionCall1(textlen, PointerGetDatum(txt));

		txt = cstring_to_text(get_namespace_name(typtup->typnamespace));
		values[4] = PointerGetDatum(txt);
		values[5] = DirectFunctionCall1(textlen, PointerGetDatum(txt));

		values[8] = Int32GetDatum(0);
		values[9] = Int32GetDatum(0);

		values[10] = BoolGetDatum(true);

		if (attr->attnotnull)
			values[10] = BoolGetDatum(false);
		else if (typtup->typnotnull)
			values[10] = BoolGetDatum(false);

		txt = cstring_to_text(NameStr(typtup->typname));
		values[11] = PointerGetDatum(txt);
		values[12] = DirectFunctionCall1(textlen, PointerGetDatum(txt));

		memset(nulls, 0, sizeof(nulls));

		tuple = heap_form_tuple(desc_rec_tupdesc, values, nulls);

		abuilder = accumArrayResult(abuilder,
									HeapTupleGetDatum(tuple),
									false,
									desc_rec_typid,
									CurrentMemoryContext);

		ReleaseSysCache(tp);
	}

	SPI_freeplan(plan);

	if ((rc = SPI_finish()) != SPI_OK_FINISH)
		elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc));

	MemoryContextSwitchTo(callercxt);

	memset(values, 0, sizeof(values));
	memset(nulls, 0, sizeof(nulls));

	values[0] = Int32GetDatum(ncolumns);
	nulls[0] = false;

	values[1] = makeArrayResult(abuilder, callercxt);
	nulls[1] = false;

	tuple = heap_form_tuple(tupdesc, values, nulls);

	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

Datum
dbms_sql_describe_columns_f(PG_FUNCTION_ARGS)
{
	return dbms_sql_describe_columns(fcinfo);
}