Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Tom Lane]

Jim Nasby  writes:
> On 1/6/17 2:45 PM, Tom Lane wrote:
>> While I was checking the patch to verify that it didn't change any
>> behavior, I noticed that it did, and there's a pre-existing bug here:
>> pltcl_build_tuple_result is applying utf_e2u to the Tcl_GetString results,
>> but surely that should be utf_u2e instead.  Fortunately we haven't shipped
>> this code yet.

> Ugh.

> For this patch lets just fix that (see attached).

I already fixed it in HEAD.

> Moving forward, I think it'd be good to improve this...

Yeah, it's pretty ugly, but I'm not sure what would be a better design.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Jim Nasby]

On 1/6/17 2:45 PM, Tom Lane wrote:

The only thing that seems significant is that we'd change the SQLSTATE
for the "odd number of list items" error: pltcl_trigger_handler has

(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
 errmsg("trigger's return list must have even number of elements")));

and that would become

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("column name/value list must have even number of elements")));

But that's probably fine; it's hard to believe anyone is depending on this
particular case --- and I think the argument that this is legitimately
a TRIGGER_PROTOCOL_VIOLATED case is a bit thin anyway.


Yeah, I forgot to mention that, but I did look at both cases and felt 
the same.



While I was checking the patch to verify that it didn't change any
behavior, I noticed that it did, and there's a pre-existing bug here:
pltcl_build_tuple_result is applying utf_e2u to the Tcl_GetString results,
but surely that should be utf_u2e instead.  Fortunately we haven't shipped
this code yet.


Ugh.

For this patch lets just fix that (see attached). Moving forward, I 
think it'd be good to improve this...


There's only 2 cases of Tcl_GetString that don't then call utf_u2e (or, 
amusingly, UTF_U2E); perhaps we should just get rid of all direct use of 
TclGetString and replace it with something like pltcl_get_utf() and 
pltcl_get_ascii()?


Oh, hell, now I see that there's an actual difference between utf_* and 
UTF_*. And AFAICT, those macros don't completely solve things either; if 
you make multiple calls you're still leaking memory but wouldn't know 
it. Granted, existing calls seem to all be properly wrapped, but that 
seems pretty error prone.


Looking at plpython, it seems that it doesn't go through any of this 
trouble: if you're using python 3 it just treats everything as if it 
needs encoding, and the conversion routines handle freeing things. 
AFAICT that's true for identifiers as well.


So I'm thinking that we should just forbid the use of direct Tcl string 
functions and pass everything though our own functions that will handle 
UTF8. There are a bunch of places where we're handing static C strings 
(that are clearly plain ASCII) to TCL, so we should probably continue to 
support that. But in the other direction I don't think it's worth it.


TCL does have a separate string append operation as well, so we'll need 
to either provide that or do all the appending using our string 
functions and then pass that along.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 5cb4ee85e0..0524db6548 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -3035,7 +3035,7 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_Obj 
**kvObjv, int kvObjc,
 
for (i = 0; i < kvObjc; i += 2)
{
-   char   *fieldName = utf_e2u(Tcl_GetString(kvObjv[i]));
+   char   *fieldName = utf_u2e(Tcl_GetString(kvObjv[i]));
int attn = 
SPI_fnumber(call_state->ret_tupdesc, fieldName);
 
/*
@@ -3058,7 +3058,7 @@ pltcl_build_tuple_result(Tcl_Interp *interp, Tcl_Obj 
**kvObjv, int kvObjc,
 errmsg("cannot set system attribute 
\"%s\"",
fieldName)));
 
-   values[attn - 1] = utf_e2u(Tcl_GetString(kvObjv[i + 1]));
+   values[attn - 1] = utf_u2e(Tcl_GetString(kvObjv[i + 1]));
}
 
return BuildTupleFromCStrings(call_state->attinmeta, values);

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Tom Lane]

Jim Nasby  writes:
> On 11/8/16 8:33 AM, Tom Lane wrote:
>> As things stand in HEAD, the behavior is about the same, but the error
>> messages are not --- in one case they mention triggers and of course the
>> other doesn't.  There are a couple of other minor things in the way of
>> unifying the two hunks of code, so I concluded it probably wasn't worth
>> the trouble.  But feel free to take another look if it bugs you.

> I had to add a bit of cruft to pltcl_build_tuple_result but it's not 
> that bad. tg_tupdesc could potentially be eliminated, but I don't know 
> if it's really worth it.

> Note that this does change some of the trigger error messages, but I 
> don't think that's really an issue?

The only thing that seems significant is that we'd change the SQLSTATE
for the "odd number of list items" error: pltcl_trigger_handler has

ereport(ERROR,
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
 errmsg("trigger's return list must have even number of elements")));

and that would become

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("column name/value list must have even number of elements")));

But that's probably fine; it's hard to believe anyone is depending on this
particular case --- and I think the argument that this is legitimately
a TRIGGER_PROTOCOL_VIOLATED case is a bit thin anyway.

While I was checking the patch to verify that it didn't change any
behavior, I noticed that it did, and there's a pre-existing bug here:
pltcl_build_tuple_result is applying utf_e2u to the Tcl_GetString results,
but surely that should be utf_u2e instead.  Fortunately we haven't shipped
this code yet.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Jim Nasby]

On 11/8/16 8:33 AM, Tom Lane wrote:

As things stand in HEAD, the behavior is about the same, but the error
messages are not --- in one case they mention triggers and of course the
other doesn't.  There are a couple of other minor things in the way of
unifying the two hunks of code, so I concluded it probably wasn't worth
the trouble.  But feel free to take another look if it bugs you.


I had to add a bit of cruft to pltcl_build_tuple_result but it's not 
that bad. tg_tupdesc could potentially be eliminated, but I don't know 
if it's really worth it.


Note that this does change some of the trigger error messages, but I 
don't think that's really an issue?

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index b0d9e41..25d959e 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -301,7 +301,8 @@ static void pltcl_set_tuple_values(Tcl_Interp *interp, 
const char *arrayname,
 static Tcl_Obj *pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc);
 static HeapTuple pltcl_build_tuple_result(Tcl_Interp *interp,
 Tcl_Obj **kvObjv, int kvObjc,
-pltcl_call_state *call_state);
+pltcl_call_state *call_state,
+TupleDesc tg_tupdesc);
 static void pltcl_init_tuple_store(pltcl_call_state *call_state);
 
 
@@ -966,7 +967,7 @@ pltcl_func_handler(PG_FUNCTION_ARGS, pltcl_call_state 
*call_state,
throw_tcl_error(interp, prodesc->user_proname);
 
tup = pltcl_build_tuple_result(interp, resultObjv, resultObjc,
-  
call_state);
+  
call_state, NULL);
retval = HeapTupleGetDatum(tup);
}
else
@@ -1000,8 +1001,6 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state 
*call_state,
const char *result;
int result_Objc;
Tcl_Obj   **result_Objv;
-   Datum  *values;
-   bool   *nulls;
 
/* Connect to SPI manager */
if (SPI_connect() != SPI_OK_CONNECT)
@@ -1219,70 +1218,9 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, pltcl_call_state 
*call_state,
 errmsg("could not split return value from 
trigger: %s",

utf_u2e(Tcl_GetStringResult(interp);
 
-   if (result_Objc % 2 != 0)
-   ereport(ERROR,
-   
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
-errmsg("trigger's return list must have even number of 
elements")));
-
-   values = (Datum *) palloc0(tupdesc->natts * sizeof(Datum));
-   nulls = (bool *) palloc(tupdesc->natts * sizeof(bool));
-   memset(nulls, true, tupdesc->natts * sizeof(bool));
-
-   for (i = 0; i < result_Objc; i += 2)
-   {
-   char   *ret_name = utf_u2e(Tcl_GetString(result_Objv[i]));
-   char   *ret_value = utf_u2e(Tcl_GetString(result_Objv[i + 
1]));
-   int attnum;
-   Oid typinput;
-   Oid typioparam;
-   FmgrInfofinfo;
-
-   /
-* Get the attribute number
-*
-* We silently ignore ".tupno", if it's present but doesn't 
match
-* any actual output column.  This allows direct use of a row
-* returned by pltcl_set_tuple_values().
-/
-   attnum = SPI_fnumber(tupdesc, ret_name);
-   if (attnum == SPI_ERROR_NOATTRIBUTE)
-   {
-   if (strcmp(ret_name, ".tupno") == 0)
-   continue;
-   ereport(ERROR,
-   (errcode(ERRCODE_UNDEFINED_COLUMN),
-errmsg("unrecognized attribute \"%s\"",
-   ret_name)));
-   }
-   if (attnum <= 0)
-   ereport(ERROR,
-   (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-errmsg("cannot set system attribute 
\"%s\"",
-   ret_name)));
-
-   /
-* Lookup the attribute type's 

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Tom Lane]

Jim Nasby  writes:
> Hrm, I completely spaced on the fact that composite returns are 
> essentially the same thing as trigger returns. ISTM we should be able to 
> use the same code for both. IIRC those magic elements could end up in 
> any SPI result, so that handling certainly needs to be the same.

> Have you had a chance to look at this or should I?

As things stand in HEAD, the behavior is about the same, but the error
messages are not --- in one case they mention triggers and of course the
other doesn't.  There are a couple of other minor things in the way of
unifying the two hunks of code, so I concluded it probably wasn't worth
the trouble.  But feel free to take another look if it bugs you.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Jim Nasby]

On 11/6/16 12:15 PM, Tom Lane wrote:

I wrote:

I got the code to a state that I liked (attached), and started reviewing
the docs, and then it occurred to me to wonder why you'd chosen to use
Tcl lists to represent composite output values.  The precedent established
by input argument handling is that composites are transformed to Tcl
arrays.  So shouldn't we use an array to represent a composite result,
too?


After further nosing around I see that the return-a-tuple-as-a-list
concept is already embedded in pltcl_trigger_handler.  So the
inconsistency is already there, and it's not necessarily this patch's
job to fix it.  Still seems like we might want to allow using an array
directly rather than insisting on conversion to a list, but that's a
task for a separate patch.


My understanding is that the TCL community is of mixed feelings when it 
comes to arrays vs lists. It does seem worth adding array support though.



We should, however, make some attempt to ensure that the list-to-tuple
conversion semantics are the same in both cases.  In particular I notice
some undocumented behavior around a magic ".tupno" element.  Will see
about cleaning that up.


Hrm, I completely spaced on the fact that composite returns are 
essentially the same thing as trigger returns. ISTM we should be able to 
use the same code for both. IIRC those magic elements could end up in 
any SPI result, so that handling certainly needs to be the same.


Have you had a chance to look at this or should I?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Tom Lane]

I wrote:
> I got the code to a state that I liked (attached), and started reviewing
> the docs, and then it occurred to me to wonder why you'd chosen to use
> Tcl lists to represent composite output values.  The precedent established
> by input argument handling is that composites are transformed to Tcl
> arrays.  So shouldn't we use an array to represent a composite result,
> too?

After further nosing around I see that the return-a-tuple-as-a-list
concept is already embedded in pltcl_trigger_handler.  So the
inconsistency is already there, and it's not necessarily this patch's
job to fix it.  Still seems like we might want to allow using an array
directly rather than insisting on conversion to a list, but that's a
task for a separate patch.

We should, however, make some attempt to ensure that the list-to-tuple
conversion semantics are the same in both cases.  In particular I notice
some undocumented behavior around a magic ".tupno" element.  Will see
about cleaning that up.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Pavel Stehule]

2016-11-06 2:12 GMT+01:00 Tom Lane :

> Jim Nasby  writes:
> > Attached is a patch that adds support for SRFs and returning composites
> > from pl/tcl. This work was sponsored by Flight Aware.
>
> I spent a fair amount of time whacking this around, because I did not
> like the fact that you were using the pltcl_proc_desc structs for
> call-local data.  That would fail nastily in a recursive function.
> I ended up making a new struct to represent per-call data, which
> allowed reducing the number of global pointers.
>
> I got the code to a state that I liked (attached), and started reviewing
> the docs, and then it occurred to me to wonder why you'd chosen to use
> Tcl lists to represent composite output values.  The precedent established
> by input argument handling is that composites are transformed to Tcl
> arrays.  So shouldn't we use an array to represent a composite result,
> too?
>

This can be similar to PLPythonu - one dimensional array is possible to
transform to composite - when composite is expected.

Regards

Pavel



>
> I wouldn't necessarily object to allowing either representation, though
> I'm not sure how we'd distinguish between them.
>
> regards, tom lane
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Tom Lane]

Jim Nasby  writes:
> Attached is a patch that adds support for SRFs and returning composites 
> from pl/tcl. This work was sponsored by Flight Aware.

I spent a fair amount of time whacking this around, because I did not
like the fact that you were using the pltcl_proc_desc structs for
call-local data.  That would fail nastily in a recursive function.
I ended up making a new struct to represent per-call data, which
allowed reducing the number of global pointers.

I got the code to a state that I liked (attached), and started reviewing
the docs, and then it occurred to me to wonder why you'd chosen to use
Tcl lists to represent composite output values.  The precedent established
by input argument handling is that composites are transformed to Tcl
arrays.  So shouldn't we use an array to represent a composite result,
too?

I wouldn't necessarily object to allowing either representation, though
I'm not sure how we'd distinguish between them.

regards, tom lane

diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index 805cc89..1c185cb 100644
*** a/doc/src/sgml/pltcl.sgml
--- b/doc/src/sgml/pltcl.sgml
*** $$ LANGUAGE pltcl;
*** 173,180 
  
  
  
!  There is currently no support for returning a composite-type
!  result value, nor for returning sets.
  
  
  
--- 173,226 
  
  
  
!  PL/Tcl functions can return a record containing multiple output
!  parameters.  The function's Tcl code should return a list of
!  key-value pairs matching the output parameters.
! 
! 
! CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
! return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
! $$ LANGUAGE 'pltcl';
! 
! 
! 
! 
!  Sets can be returned as a table type.  The Tcl code should successively
!  call return_next with an argument consisting of a Tcl
!  list of key-value pairs.
! 
! 
! CREATE OR REPLACE FUNCTION squared_srf(int,int) RETURNS TABLE (x int, y int) AS $$
! for {set i $1} {$i < $2} {incr i} {
! return_next [list x $i y [expr {$i * $i}]]
! }
! $$ LANGUAGE 'pltcl';
! 
! 
! 
! 
!  Any columns that are defined in the composite return type but absent from
!  a list of key-value pairs passed to return_next are implicitly
!  null in the corresponding row. PL/Tcl will generate a Tcl error when a
!  column name in the key-value list is not one of the defined columns.
! 
! 
! 
!  Similarly, functions can be defined as returning SETOF
!  with a user-defined data type.
! 
! 
! 
!  PL/Tcl functions can also use return_next to return a set of
!  a scalar data type.
! 
! 
! CREATE OR REPLACE FUNCTION sequence(int,int) RETURNS SETOF int AS $$
! for {set i $1} {$i < $2} {incr i} {
! return_next $i
! }
! $$ language 'pltcl';
! 
  
  
  
*** $$ LANGUAGE pltcl;
*** 197,204 
   displayed by a SELECT statement).  Conversely, the
   return
   command will accept any string that is acceptable input format for
!  the function's declared return type.  So, within the PL/Tcl function,
!  all values are just text strings.
  
  
 
--- 243,252 
   displayed by a SELECT statement).  Conversely, the
   return
   command will accept any string that is acceptable input format for
!  the function's declared return type(s).  Likewise when producing a
!  set using return_next, values are converted to their
!  native database data types.  (A Tcl error is generated whenever this
!  conversion fails.)
  
  
 
diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out
index 6cb1fdb..05382e5 100644
*** a/src/pl/tcl/expected/pltcl_queries.out
--- b/src/pl/tcl/expected/pltcl_queries.out
*** select tcl_lastoid('t2') > 0;
*** 303,305 
--- 303,366 
   t
  (1 row)
  
+ -- test some error cases
+ CREATE FUNCTION tcl_error(OUT a int, OUT b int) AS $$return {$$ LANGUAGE pltcl;
+ SELECT tcl_error();
+ ERROR:  missing close-brace
+ CREATE FUNCTION bad_record(OUT a text, OUT b text) AS $$return [list a]$$ LANGUAGE pltcl;
+ SELECT bad_record();
+ ERROR:  column name/value list must have an even number of elements
+ CREATE FUNCTION bad_field(OUT a text, OUT b text) AS $$return [list a 1 b 2 cow 3]$$ LANGUAGE pltcl;
+ SELECT bad_field();
+ ERROR:  column name/value list contains nonexistent column name "cow"
+ -- test compound return
+ select * from tcl_test_cube_squared(5);
+  squared | cubed 
+ -+---
+   25 |   125
+ (1 row)
+ 
+ -- test SRF
+ select * from tcl_test_squared_rows(0,5);
+  x | y  
+ ---+
+  0 |  0
+  1 |  1
+  2 |  4
+  3 |  9
+  4 | 16
+ (5 rows)
+ 
+ select * from tcl_test_sequence(0,5) as a;
+  a 
+ ---
+  0
+  1
+  2
+  3
+  4
+ (5 rows)
+ 
+ select 1, tcl_test_sequence(0,5);
+  ?column? | tcl_test_sequence 
+ 

Re: [HACKERS] Add support for SRF and returning composites to pl/tcl

[Pavel Stehule]

Hi

I checked this code, and it looks well

0.  there are not any reason why we would not to implement this feature -
more, the implementation is simple.

1. there was not problem with patching, compilation
2. the original patch is missing new expected result for regress tests,
fixed in attached patch
3. all regress tests passed
4. the tests and docs is enough for this purpose

I'll mark this patch as ready for commit

Regards

Pavel



2016-10-13 0:06 GMT+02:00 Jim Nasby :

> Attached is a patch that adds support for SRFs and returning composites
> from pl/tcl. This work was sponsored by Flight Aware.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)   mobile: 512-569-9461
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index 805cc89..1c185cb 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -173,8 +173,54 @@ $$ LANGUAGE pltcl;
 
 
 
- There is currently no support for returning a composite-type
- result value, nor for returning sets.
+ PL/Tcl functions can return a record containing multiple output
+ parameters.  The function's Tcl code should return a list of
+ key-value pairs matching the output parameters.
+
+
+CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
+return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
+$$ LANGUAGE 'pltcl';
+
+
+
+
+ Sets can be returned as a table type.  The Tcl code should successively
+ call return_next with an argument consisting of a Tcl
+ list of key-value pairs.
+
+
+CREATE OR REPLACE FUNCTION squared_srf(int,int) RETURNS TABLE (x int, y int) 
AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next [list x $i y [expr {$i * $i}]]
+}
+$$ LANGUAGE 'pltcl';
+
+
+
+
+ Any columns that are defined in the composite return type but absent from
+ a list of key-value pairs passed to return_next are implicitly
+ null in the corresponding row. PL/Tcl will generate a Tcl error when a
+ column name in the key-value list is not one of the defined columns.
+
+
+
+ Similarly, functions can be defined as returning SETOF
+ with a user-defined data type.
+
+
+
+ PL/Tcl functions can also use return_next to return a set of
+ a scalar data type.
+
+
+CREATE OR REPLACE FUNCTION sequence(int,int) RETURNS SETOF int AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next $i
+}
+$$ language 'pltcl';
+
 
 
 
@@ -197,8 +243,10 @@ $$ LANGUAGE pltcl;
  displayed by a SELECT statement).  Conversely, the
  return
  command will accept any string that is acceptable input format for
- the function's declared return type.  So, within the PL/Tcl function,
- all values are just text strings.
+ the function's declared return type(s).  Likewise when producing a
+ set using return_next, values are converted to their
+ native database data types.  (A Tcl error is generated whenever this
+ conversion fails.)
 
 

diff --git a/src/pl/tcl/expected/pltcl_queries.out 
b/src/pl/tcl/expected/pltcl_queries.out
index 6cb1fdb..1d4cbb3 100644
--- a/src/pl/tcl/expected/pltcl_queries.out
+++ b/src/pl/tcl/expected/pltcl_queries.out
@@ -303,3 +303,63 @@ select tcl_lastoid('t2') > 0;
  t
 (1 row)
 
+-- test compound return
+select * from tcl_test_cube_squared(5);
+ squared | cubed 
+-+---
+  25 |   125
+(1 row)
+
+CREATE FUNCTION bad_record(OUT a text , OUT b text) AS $$return [list a]$$ 
LANGUAGE pltcl;
+SELECT bad_record();
+ERROR:  list must have even number of elements
+CREATE FUNCTION bad_field(OUT a text , OUT b text) AS $$return [list cow 1 a 2 
b 3]$$ LANGUAGE pltcl;
+SELECT bad_field();
+ERROR:  Tcl list contains nonexistent column "cow"
+CREATE OR REPLACE FUNCTION tcl_error(OUT a int, OUT b int) AS $$return {$$ 
LANGUAGE pltcl;
+SELECT tcl_error();
+ERROR:  missing close-brace
+-- test SRF
+select * from tcl_test_squared_rows(0,5);
+ x | y  
+---+
+ 0 |  0
+ 1 |  1
+ 2 |  4
+ 3 |  9
+ 4 | 16
+(5 rows)
+
+select * from tcl_test_sequence(0,5) as a;
+ a 
+---
+ 0
+ 1
+ 2
+ 3
+ 4
+(5 rows)
+
+select 1, tcl_test_sequence(0,5);
+ ?column? | tcl_test_sequence 
+--+---
+1 | 0
+1 | 1
+1 | 2
+1 | 3
+1 | 4
+(5 rows)
+
+CREATE OR REPLACE FUNCTION non_srf() RETURNS int AS $$return_next 1$$ LANGUAGE 
pltcl;
+select non_srf();
+ERROR:  cannot use return_next in a non-set-returning function
+CREATE FUNCTION bad_record_srf(OUT a text , OUT b text) 

[HACKERS] Add support for SRF and returning composites to pl/tcl

[Jim Nasby]

Attached is a patch that adds support for SRFs and returning composites 
from pl/tcl. This work was sponsored by Flight Aware.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461
diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index 805cc89..1c185cb 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -173,8 +173,54 @@ $$ LANGUAGE pltcl;
 
 
 
- There is currently no support for returning a composite-type
- result value, nor for returning sets.
+ PL/Tcl functions can return a record containing multiple output
+ parameters.  The function's Tcl code should return a list of
+ key-value pairs matching the output parameters.
+
+
+CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
+return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
+$$ LANGUAGE 'pltcl';
+
+
+
+
+ Sets can be returned as a table type.  The Tcl code should successively
+ call return_next with an argument consisting of a Tcl
+ list of key-value pairs.
+
+
+CREATE OR REPLACE FUNCTION squared_srf(int,int) RETURNS TABLE (x int, y int) 
AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next [list x $i y [expr {$i * $i}]]
+}
+$$ LANGUAGE 'pltcl';
+
+
+
+
+ Any columns that are defined in the composite return type but absent from
+ a list of key-value pairs passed to return_next are implicitly
+ null in the corresponding row. PL/Tcl will generate a Tcl error when a
+ column name in the key-value list is not one of the defined columns.
+
+
+
+ Similarly, functions can be defined as returning SETOF
+ with a user-defined data type.
+
+
+
+ PL/Tcl functions can also use return_next to return a set of
+ a scalar data type.
+
+
+CREATE OR REPLACE FUNCTION sequence(int,int) RETURNS SETOF int AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next $i
+}
+$$ language 'pltcl';
+
 
 
 
@@ -197,8 +243,10 @@ $$ LANGUAGE pltcl;
  displayed by a SELECT statement).  Conversely, the
  return
  command will accept any string that is acceptable input format for
- the function's declared return type.  So, within the PL/Tcl function,
- all values are just text strings.
+ the function's declared return type(s).  Likewise when producing a
+ set using return_next, values are converted to their
+ native database data types.  (A Tcl error is generated whenever this
+ conversion fails.)
 
 

diff --git a/src/pl/tcl/expected/pltcl_queries.out 
b/src/pl/tcl/expected/pltcl_queries.out
index 6cb1fdb..7a7b029 100644
--- a/src/pl/tcl/expected/pltcl_queries.out
+++ b/src/pl/tcl/expected/pltcl_queries.out
@@ -303,3 +303,44 @@ select tcl_lastoid('t2') > 0;
  t
 (1 row)
 
+-- test compound return
+select * from tcl_test_cube_squared(5);
+ squared | cubed 
+-+---
+  25 |   125
+(1 row)
+
+CREATE FUNCTION bad_record(OUT a text , OUT b text) AS $$return [list cow]$$ 
LANGUAGE pltcl;
+SELECT bad_record();
+ERROR:  list must have even number of elements
+CREATE FUNCTION bad_field(OUT a text , OUT b text) AS $$return [list cow 1 a 2 
b 3]$$ LANGUAGE pltcl;
+SELECT bad_field();
+ERROR:  Tcl list contains nonexistent column "cow"
+CREATE OR REPLACE FUNCTION tcl_error(OUT a int, OUT b int) AS $$return {$$ 
LANGUAGE pltcl;
+SELECT tcl_error();
+ERROR:  missing close-brace
+-- test SRF
+select * from tcl_test_squared_rows(0,5);
+ x | y  
+---+
+ 0 |  0
+ 1 |  1
+ 2 |  4
+ 3 |  9
+ 4 | 16
+(5 rows)
+
+CREATE OR REPLACE FUNCTION non_srf() RETURNS int AS $$return_next 1$$ LANGUAGE 
pltcl;
+select non_srf();
+ERROR:  cannot use return_next in a non-set-returning function
+-- test setof returns
+select * from tcl_test_sequence(0,5) as a;
+ a 
+---
+ 0
+ 1
+ 2
+ 3
+ 4
+(5 rows)
+
diff --git a/src/pl/tcl/expected/pltcl_setup.out 
b/src/pl/tcl/expected/pltcl_setup.out
index e65e9e3..5332187 100644
--- a/src/pl/tcl/expected/pltcl_setup.out
+++ b/src/pl/tcl/expected/pltcl_setup.out
@@ -569,6 +569,19 @@ create function tcl_error_handling_test() returns text as 
$$
 return "no error"
 }
 $$ language pltcl;
+CREATE OR REPLACE FUNCTION tcl_test_cube_squared(in int, out squared int, out 
cubed int) AS $$
+return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
+$$ LANGUAGE 'pltcl';
+CREATE OR REPLACE FUNCTION tcl_test_squared_rows(int,int) RETURNS TABLE (x 
int, y int) AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next [list y [expr {$i * $i}] x $i]
+}
+$$ LANGUAGE 'pltcl';
+CREATE OR REPLACE FUNCTION tcl_test_sequence(int,int) RETURNS SETOF int AS $$
+for {set i $1} {$i < $2} {incr i} {
+return_next $i
+}
+$$ language 'pltcl';
 select tcl_error_handling_test();
 tcl_error_handling_test