Hello
This patch add iteration over array to plpgsql
now is supported only iteration over scalar array
Regards
Pavel Stehule
*** ./gram.y.orig 2010-09-29 10:53:44.663270537 +0200
--- ./gram.y 2010-09-30 09:04:04.809900052 +0200
***************
*** 239,244 ****
--- 239,245 ----
%token <keyword> K_ABSOLUTE
%token <keyword> K_ALIAS
%token <keyword> K_ALL
+ %token <keyword> K_ARRAY
%token <keyword> K_BACKWARD
%token <keyword> K_BEGIN
%token <keyword> K_BY
***************
*** 1057,1063 ****
Assert($3->cmd_type == PLPGSQL_STMT_FORS ||
$3->cmd_type == PLPGSQL_STMT_FORC ||
! $3->cmd_type == PLPGSQL_STMT_DYNFORS);
/* forq is the common supertype of all three */
new = (PLpgSQL_stmt_forq *) $3;
new->lineno = plpgsql_location_to_lineno(@2);
--- 1058,1065 ----
Assert($3->cmd_type == PLPGSQL_STMT_FORS ||
$3->cmd_type == PLPGSQL_STMT_FORC ||
! $3->cmd_type == PLPGSQL_STMT_DYNFORS ||
! $3->cmd_type == PLPGSQL_STMT_FORA);
/* forq is the common supertype of all three */
new = (PLpgSQL_stmt_forq *) $3;
new->lineno = plpgsql_location_to_lineno(@2);
***************
*** 1077,1083 ****
int tok = yylex();
int tokloc = yylloc;
! if (tok == K_EXECUTE)
{
/* EXECUTE means it's a dynamic FOR loop */
PLpgSQL_stmt_dynfors *new;
--- 1079,1122 ----
int tok = yylex();
int tokloc = yylloc;
! if (tok == K_ARRAY)
! {
! PLpgSQL_stmt_fora *new;
! PLpgSQL_expr *expr;
!
! new = palloc0(sizeof(PLpgSQL_stmt_fora));
! new->cmd_type = PLPGSQL_STMT_FORA;
!
! expr = read_sql_expression(K_LOOP, "LOOP");
!
! if ($1.rec)
! {
! new->rec = $1.rec;
! check_assignable((PLpgSQL_datum *) new->rec, @1);
! }
! else if ($1.row)
! {
! new->row = $1.row;
! check_assignable((PLpgSQL_datum *) new->row, @1);
! }
! else if ($1.scalar)
! {
! /* convert single scalar to list */
! new->var = (PLpgSQL_var *) $1.scalar;
! check_assignable((PLpgSQL_datum *) new->var, @1);
! }
! else
! {
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("loop variable of loop over rows must be a record or row variable or list of scalar variables"),
! parser_errposition(@1)));
! }
!
! new->expr = expr;
! $$ = (PLpgSQL_stmt *) new;
! }
! else if (tok == K_EXECUTE)
{
/* EXECUTE means it's a dynamic FOR loop */
PLpgSQL_stmt_dynfors *new;
*** ./pl_exec.c.orig 2010-09-29 11:22:32.435395512 +0200
--- ./pl_exec.c 2010-09-30 09:53:53.310900849 +0200
***************
*** 8,14 ****
*
*
* IDENTIFICATION
! * src/pl/plpgsql/src/pl_exec.c
*
*-------------------------------------------------------------------------
*/
--- 8,14 ----
*
*
* IDENTIFICATION
! * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.266 2010/08/19 18:10:48 tgl Exp $
*
*-------------------------------------------------------------------------
*/
***************
*** 107,112 ****
--- 107,114 ----
PLpgSQL_stmt_fors *stmt);
static int exec_stmt_forc(PLpgSQL_execstate *estate,
PLpgSQL_stmt_forc *stmt);
+ static int exec_stmt_fora(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_fora *stmt);
static int exec_stmt_open(PLpgSQL_execstate *estate,
PLpgSQL_stmt_open *stmt);
static int exec_stmt_fetch(PLpgSQL_execstate *estate,
***************
*** 1309,1314 ****
--- 1311,1320 ----
rc = exec_stmt_forc(estate, (PLpgSQL_stmt_forc *) stmt);
break;
+ case PLPGSQL_STMT_FORA:
+ rc = exec_stmt_fora(estate, (PLpgSQL_stmt_fora *) stmt);
+ break;
+
case PLPGSQL_STMT_EXIT:
rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 1860,1893 ****
return rc;
}
/* ----------
! * exec_stmt_fors Execute a query, assign each
! * tuple to a record or row and
! * execute a group of statements
! * for it.
* ----------
*/
static int
! exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
{
! Portal portal;
! int rc;
! /*
! * Open the implicit cursor for the statement using exec_run_select
! */
! exec_run_select(estate, stmt->query, 0, &portal);
/*
! * Execute the loop
*/
! rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, true);
/*
! * Close the implicit cursor
*/
! SPI_cursor_close(portal);
return rc;
}
--- 1866,2072 ----
return rc;
}
+ /*
+ * Check whether a specific array element is NULL
+ *
+ * nullbitmap: pointer to array's null bitmap (NULL if none)
+ * offset: 0-based linear element number of array element
+ */
+ static bool
+ array_get_isnull(const bits8 *nullbitmap, int offset)
+ {
+ if (nullbitmap == NULL)
+ return false; /* assume not null */
+ if (nullbitmap[offset / 8] & (1 << (offset % 8)))
+ return false; /* not null */
+ return true;
+ }
/* ----------
! * exec_stmt_fora Execute a loop for each field of array
* ----------
*/
static int
! exec_stmt_fora(PLpgSQL_execstate *estate, PLpgSQL_stmt_fora *stmt)
{
! Datum value;
! bool isnull;
! Oid valtype;
! bool found = false;
! int rc = PLPGSQL_RC_OK;
! int numelems = 0;
! Oid array_typelem;
! int idx;
! ArrayType *arr;
! char *ptr;
! bits8 *arraynullsptr;
! int16 elmlen;
! bool elmbyval;
! char elmalign;
!
! PLpgSQL_rec *rec = NULL;
! PLpgSQL_row *row = NULL;
! PLpgSQL_var *var = NULL;
!
! HeapTuple tuple;
! Datum dvalues[1];
! bool nulls[1];
! TupleDesc tupdesc = NULL;
!
! /* get a result of array_expr */
! value = exec_eval_expr(estate, stmt->expr, &isnull, &valtype);
! if (isnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("NULL value isn't allowed as parameter of FOR-IN-ARRAY")));
!
! /* check a result of expression - must be a array */
! array_typelem = get_element_type(valtype);
! if (!OidIsValid(array_typelem))
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("result of expression isn't array"),
! errdetail("result of expression is %s",
! format_type_be(valtype))));
!
! /* get a control variables */
! if (stmt->rec != NULL)
! var = (PLpgSQL_var *) (estate->datums[stmt->var->dno]);
! else if (stmt->rec != NULL)
! rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->dno]);
! else if (stmt->row != NULL)
! row = (PLpgSQL_row *) (estate->datums[stmt->row->dno]);
! else
! elog(ERROR, "unsupported targer");
!
! /* copy a result and takes some infos */
! arr = DatumGetArrayTypePCopy(value);
! numelems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
! ptr = ARR_DATA_PTR(arr);
! arraynullsptr = ARR_NULLBITMAP(arr);
! get_typlenbyvalalign(ARR_ELEMTYPE(arr),
! &elmlen,
! &elmbyval,
! &elmalign);
!
! /* clean a stack */
! exec_eval_cleanup(estate);
!
! /* Prepare tupledesc when we need it */
! if (rec != NULL || row != NULL)
! {
! /* have to create a TupleDesc and HeapTuple */
! tupdesc = CreateTemplateTupleDesc(1, false);
! TupleDescInitEntry(tupdesc,1, "fieldvalue", array_typelem, -1, 0);
! BlessTupleDesc(tupdesc);
! }
/*
! * Now do the loop
*/
! for (idx = 0; idx < numelems; idx++)
! {
! found = true; /* looped at least once */
!
! if (array_get_isnull(arraynullsptr, idx))
! {
! dvalues[0] = (Datum) 0;
! nulls[0] = true;
! }
! else
! {
! value = fetch_att(ptr, elmbyval, elmlen);
!
! nulls[0] = false;
! dvalues[0] = value;
!
! ptr = att_addlength_pointer(ptr, elmlen, ptr);
! ptr = (char *) att_align_nominal(ptr, elmalign);
! }
!
! /*
! * store a item to variable - we expecting so almost all
! * iteration will be over scalar values, so it is reason
! * why we don't create a fake tuple over scalar and we
! * don't use a exec_move_row for scalars. This is about
! * four times faster.
! */
! if (var != NULL)
! {
! exec_assign_value(estate, (PLpgSQL_datum *) var,
! dvalues[0], array_typelem, &nulls[0]);
! }
! else
! {
! Assert(row != NULL || rec != NULL);
! tuple = heap_form_tuple(tupdesc, dvalues, nulls);
! exec_move_row(estate, rec, row, tuple, tupdesc);
! heap_freetuple(tuple);
! }
!
! /*
! * Execute the statements
! */
! rc = exec_stmts(estate, stmt->body);
!
! if (rc == PLPGSQL_RC_RETURN)
! break; /* break out of the loop */
! else if (rc == PLPGSQL_RC_EXIT)
! {
! if (estate->exitlabel == NULL)
! /* unlabelled exit, finish the current loop */
! rc = PLPGSQL_RC_OK;
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* labelled exit, matches the current stmt's label */
! estate->exitlabel = NULL;
! rc = PLPGSQL_RC_OK;
! }
!
! /*
! * otherwise, this is a labelled exit that does not match the
! * current statement's label, if any: return RC_EXIT so that the
! * EXIT continues to propagate up the stack.
! */
! break;
! }
! else if (rc == PLPGSQL_RC_CONTINUE)
! {
! if (estate->exitlabel == NULL)
! /* unlabelled continue, so re-run the current loop */
! rc = PLPGSQL_RC_OK;
! else if (stmt->label != NULL &&
! strcmp(stmt->label, estate->exitlabel) == 0)
! {
! /* label matches named continue, so re-run loop */
! estate->exitlabel = NULL;
! rc = PLPGSQL_RC_OK;
! }
! else
! {
! /*
! * otherwise, this is a named continue that does not match the
! * current statement's label, if any: return RC_CONTINUE so
! * that the CONTINUE will propagate up the stack.
! */
! break;
! }
! }
!
! }
!
! pfree(arr);
! ReleaseTupleDesc(tupdesc);
/*
! * Set the FOUND variable to indicate the result of executing the loop
! * (namely, whether we looped one or more times). This must be set here so
! * that it does not interfere with the value of the FOUND variable inside
! * the loop processing itself.
*/
! exec_set_found(estate, found);
return rc;
}
***************
*** 2023,2028 ****
--- 2202,2239 ----
return rc;
}
+ /* ----------
+ * exec_stmt_fors Execute a query, assign each
+ * tuple to a record or row and
+ * execute a group of statements
+ * for it.
+ * ----------
+ */
+ static int
+ exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
+ {
+ Portal portal;
+ int rc;
+
+ /*
+ * Open the implicit cursor for the statement using exec_run_select
+ */
+ exec_run_select(estate, stmt->query, 0, &portal);
+
+ /*
+ * Execute the loop
+ */
+ rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, true);
+
+ /*
+ * Close the implicit cursor
+ */
+ SPI_cursor_close(portal);
+
+ return rc;
+ }
+
+
/* ----------
* exec_stmt_exit Implements EXIT and CONTINUE
***************
*** 2064,2070 ****
exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
{
/*
! * If processing a set-returning PL/pgSQL function, the final RETURN
* indicates that the function is finished producing tuples. The rest of
* the work will be done at the top level.
*/
--- 2275,2281 ----
exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
{
/*
! * If processing a set-returning PL/PgSQL function, the final RETURN
* indicates that the function is finished producing tuples. The rest of
* the work will be done at the top level.
*/
*** ./pl_funcs.c.orig 2010-09-29 11:29:27.956270569 +0200
--- ./pl_funcs.c 2010-09-29 11:36:49.521272006 +0200
***************
*** 230,235 ****
--- 230,237 ----
return _("FOR over SELECT rows");
case PLPGSQL_STMT_FORC:
return _("FOR over cursor");
+ case PLPGSQL_STMT_FORA:
+ return _("FOR over array");
case PLPGSQL_STMT_EXIT:
return "EXIT";
case PLPGSQL_STMT_RETURN:
***************
*** 277,282 ****
--- 279,285 ----
static void dump_while(PLpgSQL_stmt_while *stmt);
static void dump_fori(PLpgSQL_stmt_fori *stmt);
static void dump_fors(PLpgSQL_stmt_fors *stmt);
+ static void dump_fora(PLpgSQL_stmt_fora *stmt);
static void dump_forc(PLpgSQL_stmt_forc *stmt);
static void dump_exit(PLpgSQL_stmt_exit *stmt);
static void dump_return(PLpgSQL_stmt_return *stmt);
***************
*** 337,342 ****
--- 340,348 ----
case PLPGSQL_STMT_FORC:
dump_forc((PLpgSQL_stmt_forc *) stmt);
break;
+ case PLPGSQL_STMT_FORA:
+ dump_fora((PLpgSQL_stmt_fora *) stmt);
+ break;
case PLPGSQL_STMT_EXIT:
dump_exit((PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 596,601 ****
--- 602,619 ----
}
static void
+ dump_fora(PLpgSQL_stmt_fora *stmt)
+ {
+ dump_ind();
+ printf("FORA %s ", (stmt->rec != NULL) ? stmt->rec->refname : stmt->row->refname);
+ dump_expr(stmt->expr);
+ printf("\n");
+ dump_stmts(stmt->body);
+ dump_ind();
+ printf(" ENDFORA\n");
+ }
+
+ static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
*** ./plpgsql.h.orig 2010-09-29 11:10:35.745270541 +0200
--- ./plpgsql.h 2010-09-30 09:00:27.512903020 +0200
***************
*** 87,92 ****
--- 87,93 ----
PLPGSQL_STMT_CASE,
PLPGSQL_STMT_LOOP,
PLPGSQL_STMT_WHILE,
+ PLPGSQL_STMT_FORA,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
PLPGSQL_STMT_FORC,
***************
*** 440,447 ****
/*
* PLpgSQL_stmt_forq represents a FOR statement running over a SQL query.
! * It is the common supertype of PLpgSQL_stmt_fors, PLpgSQL_stmt_forc
! * and PLpgSQL_dynfors.
*/
typedef struct
{
--- 441,448 ----
/*
* PLpgSQL_stmt_forq represents a FOR statement running over a SQL query.
! * It is the common supertype of PLpgSQL_stmt_fors, PLpgSQL_stmt_forc,
! * PLpgSQL_stmt_fora and PLpgSQL_dynfors.
*/
typedef struct
{
***************
*** 454,459 ****
--- 455,472 ----
} PLpgSQL_stmt_forq;
typedef struct
+ {
+ int cmd_type;
+ int lineno;
+ char *label;
+ PLpgSQL_var *var;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
+ List *body; /* List of statements */
+ PLpgSQL_expr *expr;
+ } PLpgSQL_stmt_fora;
+
+ typedef struct
{ /* FOR statement running over SELECT */
int cmd_type;
int lineno;
*** ./pl_scanner.c.orig 2010-09-30 08:47:40.631901895 +0200
--- ./pl_scanner.c 2010-09-29 11:02:01.005270250 +0200
***************
*** 60,65 ****
--- 60,66 ----
static const ScanKeyword reserved_keywords[] = {
PG_KEYWORD("all", K_ALL, RESERVED_KEYWORD)
+ PG_KEYWORD("array", K_ARRAY, RESERVED_KEYWORD)
PG_KEYWORD("begin", K_BEGIN, RESERVED_KEYWORD)
PG_KEYWORD("by", K_BY, RESERVED_KEYWORD)
PG_KEYWORD("case", K_CASE, RESERVED_KEYWORD)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
