PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Postgres won’t let you declare r anyarray; or declare r v%type[]. What it won’t mind at all is if you already have it declared by that point:

    create function arraycol(v anycompatiblenonarray,n int,i int, 
  r anyarray default array[null])--here, just don't tell anyone
  returns anycompatiblearray language plpgsql as $f$ 
begin
    r=array_fill(nullif(v,v),array[n]);
    r[i]=v;
    return r;
end $f$;

    It’s not strictly the same as returning via an OUT parameter – r‘s argmode is the default IN but effectively, it’s achieving pretty much the same goal, pretty much the same way as that.
    Demo at db<>fiddle:

    select arraycol(1,3,2);
select arraycol('some_text',3,2);
select arraycol(true,3,2);
select arraycol('{"a":true}'::jsonb,3,2);
select arraycol(tstzrange('now','tomorrow','[]'),3,2);
    arraycol
    {NULL,1,NULL}
    arraycol
    {NULL,some_text,NULL}
    arraycol
    {NULL,t,NULL}
    arraycol
    {NULL,"{"a": true}",NULL}
    arraycol
    {NULL,"["2024-05-10 14:05:47.265587+01","2024-05-11 00:00:00+01"]",NULL}

    One thing it won’t do is give you arrays of arrays.

    select arraycol(array[1,2,3],3,2);

    ERROR:  function arraycol(integer[], integer, integer) does not exist
LINE 17: select arraycol(array[1,2,3],3,2);
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

    Even if you swap out anycompatiblenonarray for a regular anycompatible

    ERROR:  could not find array type for data type integer[]

    Which might be for the better, because all this feels very wrong.

    Here’s how you can also do that in plain SQL:

    create function arraycol_arr(
  v anycompatiblenonarray,n int,i int, 
  r anyarray default array[null])
  returns anycompatiblearray immutable strict parallel safe as $f$ 
select arr[:i-1]||array[v]||arr[i+1:]
from(select array_fill(nullif(v,v),array[n]) arr)_;
$f$ language sql;

create function arraycol_sql_gens_agg(
  v anycompatiblenonarray,n int,i int, 
  r anyarray default array[null])
  returns anycompatiblearray immutable strict parallel safe as $f$ 
select array_agg(case x when i then v end)
from(select generate_series(1,n)x)_;
$f$ language sql;

    Which according to the tests at the end of the demo is a nice way to butcher your performance.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search