arrayspostgresqlpolymorphismplpgsql

How to generate an array of given size, with one field populated?


I would like to create a function that given a value of any type, a dimension of the one-dimensional array and the column to set, will return a one-dimensional array filled with null values except for one given value at a given position.

The code bellow is what I created tentatively to do it but it does not work:

create function arraycol(v anyelement,n int,i int)
  returns anyarray language plpgsql as $$ 
declare r anyarray; 
begin
    r=array_fill(null,array[n]);
    r[i]=v;
    return r;
end $$;

Is it possible to have the function return a polymorphic array type?

is it possible to create a variable of polymorphic element type?


Solution

  • You were close.

    r anyarray; isn't valid syntax to declare a variable of the polymorphic input type anyarray. To declare a variable of polymorphic input type use the %TYPE construct:

    DECLARE
       array_element v%TYPE;
    

    Unfortunately in this case, we only have a parameter of type anyelement to work with, which makes it tricky. See:

    I use an OUT parameter or type anyarray instead, to simplify.

    Also, you need to pass the actual element type to the function array_fill(). An un-typed null won't do. To skip variable declaration completely I produce the typed null value ad-hoc with NULLIF(v, v):

    CREATE OR REPLACE FUNCTION arraycol (v anyelement, n int, i int, OUT r anyarray)
       LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
    $func$
    BEGIN
       r := array_fill(NULLIF(v, v), ARRAY[n]);
       -- if i > n then this assignment auto-fills positions in between with null
       r[i] := v;
    END
    $func$;
    

    Equivalent variant without array_fill(). In the assignments, an un-typed null works:

    CREATE OR REPLACE FUNCTION arraycol (v anyelement, n int, i int, OUT r anyarray)
       LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
    $func$
    BEGIN
       r[1] := null;  -- assuming 1-based indexes!
       r[i] := v;     -- auto-fills positions in between with null
       IF n > i THEN
          r[n] := null;
       END IF;
    END
    $func$;
    

    fiddle

    Same call, same result. Example:

    SELECT arraycol (1, 3, 2);
    
    arraycol
    {NULL,1,NULL}

    Of course, I'd put in checks to enforce sane input. Like, enforce dimension input > 1.