Postgresql – Loop through slices of 2-dimensional array in plpgsql function

arrayplpgsqlpostgresqlpostgresql-9.6

How to loop through an array of arrays in a function and access its elements?

Below is the the function (this is only the relevant part).

create  or replace function public.whatwhatwhy(
        _fields character varying []
    ) returns void as 
$func$
declare strSQL text := '';
declare _field text := '';
begin 
    foreach _field in array _fields[:][1:2]
        loop 
            strSQL :=  'fieldname:' || _field[1] || ' stats: ' || _field[2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
end
$func$ language plpgsql;

I want to call the function with:

select public.whatwhatwhy(ARRAY[
['total_pop', 'sum'],
['total_male_pop','sum'],
['median_female_age','median']
])

Question
How do I access e.g. total_pop and sum in my _field? I assumed the _field variable holds another array however I get an error:

SQL Error [42804]: ERROR: cannot subscript type text because it is not an array
Where: SQL statement "SELECT 'fieldname:' || _field[1] || ' stats: ' || _field[2]"

Note

I tried this statement, hoping that this solves the problem, but it seems to be identical to the statement above.

select public.whatwhatwhy(ARRAY[
ARRAY['total_pop', 'sum'],
ARRAY['total_male_pop','sum'],
ARRAY['median_female_age','median']
])

Best Answer

A FOR loop over array_upper(_fields,1) like you found is a simple and good option.

Even simpler for your case (for Postgres 9.1 or later): FOREACH with SLICE. The manual:

With a positive SLICE value, FOREACH iterates through slices of the array rather than single elements.

Your function could look like this:

CREATE OR REPLACE FUNCTION public.mj_rubbish(_fields text[])
   RETURNS void AS
$func$
DECLARE
   m text[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY _fields
   LOOP
      RAISE NOTICE '% EXECUTING fieldname: %, stats: %', now()::text, m[1], m[2];
   END LOOP;
END
$func$  LANGUAGE plpgsql;

This also works regardless of actual array indices - which do no have to start with 1 (but do by default).

Postgresql – Passing array from one plpgsql function to another

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass, 
                               inttable  regclass, 
                               verttable regclass)
RETURNS void AS
$func$
BEGIN
   EXECUTE format(
      'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
     , linktable)
   USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that $1, $2, $3 in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING $1, $2, $3. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

WHERE   int.id ANY %L

WHERE   int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                               inttable  regclass,
                               verttable regclass,
                               intersection_ids int[])

    -- ... SNIP

    EXECUTE format('
        INSERT INTO %1$s
               (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
        SELECT  int.id, l1.id      , l2.id
              , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %2$s int
        JOIN    %3$s v1 ON v1.intersection_id = int.id
        JOIN    %3$s v2 ON v2.intersection_id = int.id
        JOIN    %4$s l1 ON l1.target_node = v1.node_id
        JOIN    %4$s l2 ON l2.source_node = v2.node_id
                     AND l2.road_id <> l1.road_id
        WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        ', temptable,
           inttable,
           verttable,
           linktable)
    USING intersection_ids;

    ...

The whole setup might be simplified further.

Postgresql – Conditional WHERE in plpgsql function

@Ypercube's comment basically answers your core question.

But your function is also much slower and more complicated than necessary:

CREATE OR REPLACE FUNCTION find_days_business(_reception_duration int
                                            , _department_id bigint
                                            , _service_id bigint)
  RETURNS TABLE (avail_records numeric
               , busy_records bigint
               , percentage numeric
               , day_date date) AS
$func$
BEGIN
 RETURN QUERY
 SELECT s.sum_diff, n.ct, ((n.ct * 100) / s.sum_diff ), s.date
 FROM  (
    SELECT p.date, sum(p.diff) AS sum_diff
    FROM  (
       SELECT w.date
            , ceil(((max(w.time_to) - min(w.time_from)) * 60) / _reception_duration) AS diff
       FROM   workingtimetable w
       WHERE  w.date >= now()::date
       AND    w.department_id = _department_id
       -- another restriction IF _service_id is not null:
       AND   (_service_id IS NULL OR <another restriction>)
       GROUP  BY w.user_id, w.date
       ) p          -- perday
    GROUP  BY p.date
 ) s                -- sum_of_avail_records
 LEFT   JOIN (
    SELECT d.date, count(*) AS ct
    FROM   appointments_book_records r
    JOIN   appointments_book_days    d ON r.appointment_day_id = d.id
    WHERE  d.date >= now()::date
    GROUP  BY d.date
 ) n USING (date)   -- num_of_busy_records 
 ORDER  BY s.date;
END
$func$  LANGUAGE plpgsql;

Major points

Temporary tables are more expensive and not necessary here. Simple subqueries do the job.
Don't include columns in the SELECT list that you don't need for anything. Dead weight. (You also don't need to include GROUP BY expressions in the SELECT list.)
Don't ORDER BY in subqueries. While Postgres typically keeps the order in the outer SELECT for simple queries, this is completely arbitrary and not reliable. There is no provision in the SQL standard that would require propagation of ORDER BY in subqueries to the outer query.

If your are looking for fully dynamic query predicates or arbitrary dynamic query elements with optimized query plans, you'll have to use dynamic SQL and EXECUTE:

Best Answer

Related Solutions

Postgresql – Passing array from one plpgsql function to another

Postgresql – Conditional WHERE in plpgsql function

Major points

Related Question