Hello,
this patch allow any expression in return statement and it does simply
casting from non type record into typed records. Allows same casting as
assign statement.
Regards
Pavel Stehule
From: Bruce Momjian <[EMAIL PROTECTED]>
To: Pavel Stehule <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: near future plpgsql?
Date: Wed, 23 Aug 2006 08:25:18 -0400 (EDT)
Pavel Stehule wrote:
> Hello,
>
> I had talk with Neil and I knew so Neil forgot on my patch
> http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php . I
want
> to resend this patch again, but this patch is in conflict with cancelled
> "lookahead" patch. Can I work on it? My patch was sent before feature
> frease. But I prefere apply it after "lookahead" patch.
>
> your recommendation?
Yes, please send an updated version. Sorry we forgot about it. I try
to keep patches around until someone does apply it, but didn't do that
in this case.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2006-08-23 14:29:07.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2006-08-23 14:31:43.000000000 +0200
***************
*** 1586,1599 ****
</para>
<para>
- 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>
If you declared the function with output parameters, write just
<command>RETURN</command> with no expression. The current values
of the output parameter variables will be returned.
--- 1586,1591 ----
*** ./src/pl/plpgsql/src/gram.y.orig 2006-08-23 10:39:35.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2006-08-23 14:37:20.000000000 +0200
***************
*** 2001,2006 ****
--- 2001,2007 ----
make_return_stmt(int lineno)
{
PLpgSQL_stmt_return *new;
+ int tok;
new = palloc0(sizeof(PLpgSQL_stmt_return));
new->cmd_type = PLPGSQL_STMT_RETURN;
***************
*** 2026,2032 ****
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! switch (yylex())
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
--- 2027,2034 ----
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! tok = yylex();
! switch (tok)
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
***************
*** 2040,2051 ****
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
{
--- 2042,2059 ----
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 value in function returning tuple");
break;
}
! if (!new->expr && yylex() != ';')
! yyerror("RETURN must specify a value in function returning tuple");
}
else
{
***************
*** 2065,2070 ****
--- 2073,2079 ----
make_return_next_stmt(int lineno)
{
PLpgSQL_stmt_return_next *new;
+ int tok;
if (!plpgsql_curr_compile->fn_retset)
yyerror("cannot use RETURN NEXT in a non-SETOF function");
***************
*** 2083,2089 ****
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! switch (yylex())
{
case T_ROW:
new->retvarno = yylval.row->rowno;
--- 2092,2099 ----
}
else if (plpgsql_curr_compile->fn_retistuple)
{
! tok = yylex();
! switch (tok)
{
case T_ROW:
new->retvarno = yylval.row->rowno;
***************
*** 2093,2104 ****
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(';', ";");
--- 2103,2120 ----
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 a value in function returning tuple");
break;
}
! if (!new->expr && yylex() != ';')
! yyerror("RETURN NEXT must specify a value in function returning tuple");
}
else
new->expr = plpgsql_read_expression(';', ";");
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2006-08-23 11:30:40.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2006-08-23 13:39:06.000000000 +0200
***************
*** 155,160 ****
--- 155,168 ----
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
static void free_var(PLpgSQL_var *var);
+ static HeapTuple coerce_to_tuple(PLpgSQL_execstate * estate,
+ HeapTuple tuple,
+ TupleDesc tupdesc,
+ TupleDesc reqtupdesc);
+
+ static void
+ exec_coerce_tuple(PLpgSQL_execstate *estate);
+
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 1805,1817 ****
{
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
{
--- 1813,1827 ----
{
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)
! exec_coerce_tuple(estate);
}
else
{
***************
*** 1940,1965 ****
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);
}
--- 1950,1997 ----
bool isNull;
Oid rettype;
! 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")));
!
! tuple = NULL;
!
! if (!estate->retisnull)
! {
! exec_coerce_tuple(estate);
! tuple = (HeapTuple) estate->retval;
! 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);
}
***************
*** 4683,4685 ****
--- 4715,4824 ----
var->freeval = false;
}
}
+
+
+ /*
+ * Cast tuple for desired tuple descriptor
+ *
+ */
+
+ static void
+ exec_coerce_tuple(PLpgSQL_execstate *estate)
+ {
+ 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) coerce_to_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;
+ }
+
+ ReleaseTupleDesc(in_tupdesc);
+ exec_eval_cleanup(estate);
+ }
+
+
+ static HeapTuple
+ coerce_to_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;
+ }
*** ./src/test/regress/sql/plpgsql.sql.orig 2006-08-23 14:20:53.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2006-08-23 14:35:51.000000000 +0200
***************
*** 2440,2442 ****
--- 2440,2626 ----
select footest();
drop function footest();
+
+ -- test return can contain any valid row expression
+
+
+ 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();
+
+ -- wrong, unsupported implicit casting
+ 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_row4();
+ 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, NULL, NULL);
+ return next (1,2,3::text, NULL, NULL);
+ 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 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
