OK, so maybe it is a "hold for 7.5" item, but I think it could be argued that it is a (relatively simple and safe) fix for a relatively big deficiency with the recently committed plpgsql polymorphism.

By way of explanation, the new plpgsql polymorphism capability means that a function can be declared like this:

CREATE OR REPLACE FUNCTION foo(anyelement) returns anyarray AS '
    function body
' language 'plpgsql';

The argument and return type are determined at function call time, and
in this example the return type is constrained to be an array of the
argument type.

Inside the the function body, you can access the actual function call
time *argument* types to create local variables by using the %type
construct, e.g.:

v_myvar $1%type;

However, there is currently is *no way* to create a variable based on the return type, severely limiting the utility of polymorphic plpgsql functions. Because the polymorphic plpgsql patch was submitted just before the freeze (a few hours), I didn't have enough experience actually trying to write useful polymorphic plpgsql functions to know this would be a deficiency.

So what does the attached patch do? It adds an additional variable to any function with a polymorphic return type (only), called $0, which represents the actual function call time return type. With $0 defined, now you can declare a local variable inside the function body using $0%type (it isn't useful for anything else, and is set to NULL for good measure). For example:

create or replace function greatest(anyarray)
returns anyelement as '
declare
   v_arr alias for $1;
   v_lb int;
   v_ub int;
   v_greatest $0%type;
begin
   v_lb := array_lower(v_arr,1);
   v_ub := array_upper(v_arr,1);
   v_greatest := v_arr[v_lb];
   for i in v_lb + 1 .. v_ub loop
     if v_arr[i] > v_greatest then
       v_greatest := v_arr[i];
     end if;
   end loop;
   return v_greatest;
end;
' language 'plpgsql';

create table g(f1 text, f2 text, f3 text, f4 text, f5 text, f6 text);
insert into g values ('a','b','c','d','e','f');
insert into g values ('z','x','c','v','b','n');
create table h(f1 int, f2 int, f3 int);
insert into h values (42,6,39);
insert into h values (2,3,4);

regression=# select greatest(array[f1,f2]) from g;
   greatest
----------
   b
   z
(2 rows)

regression=# select greatest(array[f1,f2,f3,f4,f5,f6]) from g;
   greatest
----------
   f
   z
(2 rows)

regression=# select greatest(array[f1,f2]) from h;
   greatest
----------
         42
          3
(2 rows)

regression=# select greatest(array[f1,f2,f3]) from h;
   greatest
----------
         42
          4
(2 rows)

The entire patch is less than 60 lines, and it is completely localized to the polymorphic return value case. If accepted, I will of course follow with the needed documentation (none of the polymorphic stuff is documented yet anyway).

The $0 variable could be extended later to the same functionality for a plpgsql function returning record, but I wanted to keep the patch focused so that it might be allowed into 7.4 as a "fix".

If there are no objections, please apply. Otherwise please hold for 7.5.

Thanks,

Joe

Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.59
diff -c -r1.59 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c        1 Jul 2003 21:47:09 -0000       1.59
--- src/pl/plpgsql/src/pl_comp.c        3 Jul 2003 02:13:59 -0000
***************
*** 354,359 ****
--- 354,395 ----
                                function->fn_rettyplen = typeStruct->typlen;
                                function->fn_rettypelem = typeStruct->typelem;
                                perm_fmgr_info(typeStruct->typinput, 
&(function->fn_retinput));
+ 
+                               /*
+                                * install $0 reference, but only for polymorphic
+                                * return types
+                                */
+                               if (procStruct->prorettype == ANYARRAYOID ||
+                                       procStruct->prorettype == ANYELEMENTOID)
+                               {
+                                       char            buf[32];
+ 
+                                       /* name for variable */
+                                       snprintf(buf, sizeof(buf), "$%d", 0);
+ 
+                                       /*
+                                        * Normal return values get a var node
+                                        */
+                                       var = malloc(sizeof(PLpgSQL_var));
+                                       memset(var, 0, sizeof(PLpgSQL_var));
+ 
+                                       var->dtype = PLPGSQL_DTYPE_VAR;
+                                       var->refname = strdup(buf);
+                                       var->lineno = 0;
+                                       var->datatype = build_datatype(typeTup, -1);
+                                       var->isconst = true;
+                                       var->notnull = false;
+                                       var->default_val = NULL;
+ 
+                                       /* preset to NULL */
+                                       var->value = 0;
+                                       var->isnull = true;
+                                       var->freeval = false;
+ 
+                                       plpgsql_adddatum((PLpgSQL_datum *) var);
+                                       plpgsql_ns_additem(PLPGSQL_NSTYPE_VAR, 
var->varno,
+                                                                          
var->refname);
+                               }
                        }
                        ReleaseSysCache(typeTup);
  
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to