Hello
this patch allow using any row or record expression in return, return next
statement in row, record functions - per request John Berkus
http://archives.postgresql.org/pgsql-hackers/2005-10/msg01350.php
regards
Pavel Stehule
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
diff -c -r pgsql.old/doc/src/sgml/plpgsql.sgml pgsql/doc/src/sgml/plpgsql.sgml
*** pgsql.old/doc/src/sgml/plpgsql.sgml 2005-11-05 00:14:00.000000000 +0100
--- pgsql/doc/src/sgml/plpgsql.sgml 2005-11-07 13:23:53.000000000 +0100
***************
*** 1499,1506 ****
When returning a scalar type, any expression can be used. The
expression's result will be automatically cast into the
function's return type as described for assignments. To return a
! composite (row) value, you must write a record or row variable
! as the <replaceable>expression</replaceable>.
</para>
<para>
--- 1499,1506 ----
When returning a scalar type, any expression can be used. The
expression's result will be automatically cast into the
function's return type as described for assignments. To return a
! composite (row) value, you must to use any row or record variable
! or any row or record function.
</para>
<para>
diff -c -r pgsql.old/src/pl/plpgsql/src/gram.y pgsql/src/pl/plpgsql/src/gram.y
*** pgsql.old/src/pl/plpgsql/src/gram.y 2005-10-13 17:34:19.000000000 +0200
--- pgsql/src/pl/plpgsql/src/gram.y 2005-11-06 20:52:18.000000000 +0100
***************
*** 1079,1084 ****
--- 1079,1085 ----
stmt_return : K_RETURN lno
{
PLpgSQL_stmt_return *new;
+ int tok;
new = palloc0(sizeof(PLpgSQL_stmt_return));
new->cmd_type = PLPGSQL_STMT_RETURN;
***************
*** 1104,1110 ****
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! switch (yylex())
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
--- 1105,1112 ----
}
else if (plpgsql_curr_compile->fn_retistuple)
{
!
! switch (tok = yylex())
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
***************
*** 1118,1129 ****
new->retvarno = yylval.rec->recno;
break;
default:
! yyerror("RETURN must specify a record or row variable in function returning tuple");
break;
}
! if (yylex() != ';')
! yyerror("RETURN must specify a record or row variable in function returning tuple");
}
else
{
--- 1120,1138 ----
new->retvarno = yylval.rec->recno;
break;
+ case T_WORD:
+ case '(':
+ plpgsql_push_back_token(tok);
+ new->expr = plpgsql_read_expression(';',";");
+ break;
+
default:
! yyerror("RETURN must specify a record or row variable or row function in function returning tuple");
break;
}
! if (!new->expr)
! if (yylex() != ';')
! yyerror("RETURN must specify a record or row variable or row function in function returning tuple");
}
else
{
***************
*** 1142,1147 ****
--- 1151,1157 ----
stmt_return_next: K_RETURN_NEXT lno
{
PLpgSQL_stmt_return_next *new;
+ int tok;
if (!plpgsql_curr_compile->fn_retset)
yyerror("cannot use RETURN NEXT in a non-SETOF function");
***************
*** 1160,1166 ****
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! switch (yylex())
{
case T_ROW:
new->retvarno = yylval.row->rowno;
--- 1170,1176 ----
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! switch (tok = yylex())
{
case T_ROW:
new->retvarno = yylval.row->rowno;
***************
*** 1170,1181 ****
new->retvarno = yylval.rec->recno;
break;
default:
yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
break;
}
! if (yylex() != ';')
! yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
}
else
new->expr = plpgsql_read_expression(';', ";");
--- 1180,1198 ----
new->retvarno = yylval.rec->recno;
break;
+ case T_WORD:
+ case '(':
+ plpgsql_push_back_token(tok);
+ new->expr = plpgsql_read_expression(';',";");
+ break;
+
default:
yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
break;
}
! if (!new->expr)
! if (yylex() != ';')
! yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
}
else
new->expr = plpgsql_read_expression(';', ";");
diff -c -r pgsql.old/src/pl/plpgsql/src/pl_exec.c pgsql/src/pl/plpgsql/src/pl_exec.c
*** pgsql.old/src/pl/plpgsql/src/pl_exec.c 2005-10-24 17:10:22.000000000 +0200
--- pgsql/src/pl/plpgsql/src/pl_exec.c 2005-11-07 14:40:49.000000000 +0100
***************
*** 181,186 ****
--- 181,190 ----
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate * estate, bool state);
static void free_var(PLpgSQL_var * var);
+ static HeapTuple make_tuple_from_tuple(PLpgSQL_execstate * estate,
+ HeapTuple tuple,
+ TupleDesc tupdesc,
+ TupleDesc reqtupdesc);
/* ----------
***************
*** 1792,1804 ****
{
if (estate->retistuple)
{
! exec_run_select(estate, stmt->expr, 1, NULL);
! if (estate->eval_processed > 0)
{
! estate->retval = (Datum) estate->eval_tuptable->vals[0];
! estate->rettupdesc = estate->eval_tuptable->tupdesc;
! estate->retisnull = false;
! }
}
else
{
--- 1796,1855 ----
{
if (estate->retistuple)
{
! estate->retval = exec_eval_expr(estate, stmt->expr, &(estate->retisnull), &(estate->rettype));
! if (!(estate->rettype == RECORDOID || get_typtype(estate->rettype) == 'c'))
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong result type supplied in RETURN")));
!
! if (!estate->retisnull)
{
! int tupType;
! int tupTypmod;
! TupleDesc in_tupdesc;
! HeapTupleData td;
!
! tupType = HeapTupleHeaderGetTypeId((HeapTupleHeader) estate->retval);
! tupTypmod = HeapTupleHeaderGetTypMod((HeapTupleHeader) estate->retval);
! in_tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
!
! if (in_tupdesc == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong result type supplied in RETURN")));
!
! td.t_len = HeapTupleHeaderGetDatumLength((HeapTupleHeader) estate->retval);
! ItemPointerSetInvalid(&(td.t_self));
! td.t_tableOid = InvalidOid;
! td.t_data = (HeapTupleHeader) estate->retval;
!
!
! /* coerce type if needed */
!
! if (estate->rsi && IsA(estate->rsi, ReturnSetInfo)
! && estate->rsi->expectedDesc != NULL
! && !compatible_tupdesc(estate->rsi->expectedDesc, in_tupdesc))
! {
! estate->retval = (Datum) make_tuple_from_tuple(estate,
! &td,
! in_tupdesc,
! estate->rsi->expectedDesc);
! if ((HeapTuple) estate->retval == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong record type supplied in RETURN NEXT")));
!
! estate->rettupdesc = estate->rsi->expectedDesc;
! }
! else
! {
! estate->retval = (Datum) heap_copytuple(&td);;
! estate->rettupdesc = in_tupdesc;
! }
!
!
! exec_eval_cleanup(estate);
! }
}
else
{
***************
*** 1927,1954 ****
bool isNull;
Oid rettype;
! if (natts != 1)
! ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("wrong result type supplied in RETURN NEXT")));
! retval = exec_eval_expr(estate,
stmt->expr,
&isNull,
&rettype);
!
! /* coerce type if needed */
! retval = exec_simple_cast_value(retval,
rettype,
tupdesc->attrs[0]->atttypid,
tupdesc->attrs[0]->atttypmod,
isNull);
! tuple = heap_form_tuple(tupdesc, &retval, &isNull);
! free_tuple = true;
! exec_eval_cleanup(estate);
}
else
{
--- 1978,2058 ----
bool isNull;
Oid rettype;
! if (estate->retistuple)
! {
! retval = exec_eval_expr(estate, stmt->expr, &isNull, &rettype);
! if (!(rettype == RECORDOID || get_typtype(rettype) == 'c'))
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong result type supplied in RETURN")));
!
! tuple = NULL; /* keep compiler quiet */
!
! if (!isNull)
! {
! int tupType;
! int tupTypmod;
! TupleDesc in_tupdesc;
! HeapTupleData td;
!
! tupType = HeapTupleHeaderGetTypeId((HeapTupleHeader) retval);
! tupTypmod = HeapTupleHeaderGetTypMod((HeapTupleHeader) retval);
! in_tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
!
! if (in_tupdesc == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong result type supplied in RETURN")));
!
! td.t_len = HeapTupleHeaderGetDatumLength((HeapTupleHeader) retval);
! ItemPointerSetInvalid(&(td.t_self));
! td.t_tableOid = InvalidOid;
! td.t_data = (HeapTupleHeader) retval;
!
! /* is nessery conversion? */
! if (!compatible_tupdesc(tupdesc, in_tupdesc))
! {
!
! tuple = make_tuple_from_tuple(estate,
! &td,
! in_tupdesc,
! tupdesc);
! if (tuple == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("wrong record type supplied in RETURN NEXT")));
! }
! else
! tuple = heap_copytuple(&td);
!
! exec_eval_cleanup(estate);
! free_tuple = true;
! }
! }
! else
! {
! if (natts != 1)
! ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("wrong result type supplied in RETURN NEXT")));
! retval = exec_eval_expr(estate,
stmt->expr,
&isNull,
&rettype);
! /* coerce type if needed */
! retval = exec_simple_cast_value(retval,
rettype,
tupdesc->attrs[0]->atttypid,
tupdesc->attrs[0]->atttypmod,
isNull);
! tuple = heap_form_tuple(tupdesc, &retval, &isNull);
! free_tuple = true;
! exec_eval_cleanup(estate);
! }
}
else
{
***************
*** 3620,3625 ****
--- 3724,3730 ----
* If this is a simple expression, bypass SPI and use the executor
* directly
*/
+
if (expr->expr_simple_expr != NULL)
return exec_eval_simple_expr(estate, expr, isNull, rettype);
***************
*** 4504,4506 ****
--- 4609,4668 ----
var->freeval = false;
}
}
+
+
+ /*
+ * coerce tuple
+ */
+
+ static HeapTuple
+ make_tuple_from_tuple(PLpgSQL_execstate * estate,
+ HeapTuple tuple,
+ TupleDesc tupdesc,
+ TupleDesc reqtupdesc)
+ {
+ int rnatts = reqtupdesc->natts;
+ int natts = tupdesc->natts;
+ Datum *values;
+ Datum *rvalues;
+ bool *nulls;
+ bool *rnulls;
+ int i;
+
+ values = (Datum *) palloc0(natts * sizeof(Datum));
+ rvalues = (Datum *) palloc0(rnatts * sizeof(Datum));
+ nulls = (bool *) palloc(natts * sizeof(bool));
+ rnulls = (bool *) palloc(rnatts * sizeof(bool));
+
+ heap_deform_tuple(tuple, tupdesc, values, nulls);
+
+ for (i = 0; i < rnatts; i++)
+ {
+ /* coerce if needed */
+ if (i < natts)
+ {
+
+ rvalues[i] = exec_simple_cast_value(values[i],
+ tupdesc->attrs[i]->atttypid,
+ reqtupdesc->attrs[i]->atttypid,
+ reqtupdesc->attrs[i]->atttypmod,
+ nulls[i]);
+ rnulls[i] = nulls[i];
+ }
+ else
+ {
+ /* missing values */
+ rnulls[i] = true;
+ }
+ }
+
+ tuple = heap_form_tuple(reqtupdesc, rvalues, rnulls);
+
+ pfree(values);
+ pfree(rvalues);
+ pfree(nulls);
+ pfree(rnulls);
+
+ return tuple;
+ }
+
Pouze v pgsql/src/test/regress/expected: plpgsql.sql
diff -c -r pgsql.old/src/test/regress/sql/plpgsql.sql pgsql/src/test/regress/sql/plpgsql.sql
*** pgsql.old/src/test/regress/sql/plpgsql.sql 2005-09-14 20:35:38.000000000 +0200
--- pgsql/src/test/regress/sql/plpgsql.sql 2005-11-07 14:51:23.000000000 +0100
***************
*** 2280,2282 ****
--- 2280,2462 ----
end loop outer_label;
end;
$$ language plpgsql;
+
+ create type __trt as (x integer, y integer, z text);
+
+ create or replace function return_row1() returns __trt as $$
+ declare r __trt;
+ begin r := row(1,2,3);
+ return r;
+ end;
+ $$ language plpgsql;
+ select return_row1();
+
+ create or replace function return_row2() returns __trt as $$
+ declare r record;
+ begin r := row(1,2,3);
+ return r;
+ end;
+ $$ language plpgsql;
+ select return_row2();
+
+ create or replace function return_row3() returns __trt as $$
+ begin
+ return row(1,2,3);
+ end;
+ $$ language plpgsql;
+ select return_row3();
+
+ create or replace function return_row4() returns __trt as $$
+ begin
+ return (1,2,'3'::text);
+ end;
+ $$ language plpgsql;
+ select return_row4();
+
+ create or replace function return_row5() returns record as $$
+ begin
+ return row(1,2,3);
+ end;
+ $$ language plpgsql;
+ select return_row5();
+
+ create or replace function return_row6() returns setof __trt as $$
+ begin
+ return next row(1,2,3::text);
+ return next row(4,5,6::text);
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row6();
+
+ create or replace function return_row7() returns setof __trt as $$
+ declare r __trt;
+ begin
+ r := row(1,2,3);
+ return next r;
+ r := row(4,5,6);
+ return next r;
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row7();
+
+ create or replace function return_row8() returns setof __trt as $$
+ declare r record;
+ begin
+ r := row(1,2,3::text);
+ return next r;
+ r := row(4,5,6::text);
+ return next r;
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row8();
+
+ create or replace function return_row9() returns setof record as $$
+ declare r record;
+ begin
+ r := row(1,2,3::text);
+ return next r;
+ r := row(4,5,6::text);
+ return next r;
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row9() as (x integer, y integer, z text);
+
+ create or replace function return_row10() returns setof record as $$
+ begin
+ return next row(1,2,3::text);
+ return next row(4,5,6::text);
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row10() as (x integer, y integer, z text);
+
+ create or replace function return_row11() returns setof float as $$
+ declare a float = 1.3;
+ begin
+ return next sin(1.1);
+ return next sin(1.2);
+ return next sin(a);
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row11();
+
+ create or replace function return_row12() returns float as $$
+ declare a float = 1.2;
+ begin
+ return sin(a);
+ end;
+ $$ language plpgsql;
+ select return_row12();
+
+ -- should fail: only row function is allowed
+ create or replace function return_row13() returns setof record as $$
+ begin
+ return next sin(1);
+ return next sin(2);
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row13() as (x integer, y integer, z text);
+
+ create or replace function return_row14() returns setof record as $$
+ begin
+ return next (1,2,3);
+ return next (1,2,3::text);
+ return next row(1,2,3::text);
+ return next return_row5();
+ return next return_row1();
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row14() as (x integer, y integer, z text);
+
+ create or replace function return_row15() returns record as $$
+ begin
+ return return_row4();
+ end;
+ $$ language plpgsql;
+ select return_row15();
+ select * from return_row15() as (a integer, b integer, c text);
+
+ create or replace function return_row16() returns record as $$
+ begin
+ return return_row3();
+ end;
+ $$ language plpgsql;
+ select return_row16();
+ select * from return_row16() as (a integer, b integer, c text);
+
+ create or replace function return_row17() returns setof record as $$
+ begin
+ return next (1,2,3);
+ return next (1,2,3::text);
+ return next row(1,2,3::text,4,5);
+ return;
+ end;
+ $$ language plpgsql;
+ select * from return_row17() as (x integer, y integer, z integer, i integer);
+
+ drop function return_row1();
+ drop function return_row2();
+ drop function return_row3();
+ drop function return_row4();
+ drop function return_row5();
+ drop function return_row6();
+ drop function return_row7();
+ drop function return_row8();
+ drop function return_row9 ();
+ drop function return_row10();
+ drop function return_row11();
+ drop function return_row12();
+ drop function return_row13();
+ drop function return_row14();
+ drop function return_row15();
+ drop function return_row16();
+ drop function return_row17();
+
+ drop type __trt;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
