Monday, July 26, 2010

missing %type[] - workaround

We are not able to do some more complex functions in plpgsql when returns is polymorphic array type. Why - we are not able to define variable as array of some variable - so input have to be anyarray (and we can do copy of this type) or we to calculate result in one expression used in RETURN statement. But there is one workaround. We can use a OUT polymorphic parameter - function with one OUT parameter is scalar function still.
-- we cannot do
CREATE OR REPLACE FUNCTION foo(a anyelement)
RETURNS anyarray AS $$
DECLARE b a%type[]; 
BEGIN
  ...

-- we can
CREATE OR REPLACE FUNCTION foo(a anyelement, OUT b anyarray)
AS $$
BEGIN
  b := array_fill(a, ARRAY[1]);
  ...