The following code snippet creates three arrays, which are passed into a PL/R function.
FOR s_id, s_latitude, s_longitude IN
SELECT
s.id,
s.latitude_decimal,
s.longitude_decimal
FROM
climate.station s
WHERE
s.applicable AND
s.latitude_decimal BETWEEN box.latitude_min AND box.latitude_max AND
s.longitude_decimal BETWEEN box.longitude_min AND box.longitude_max
LOOP
SELECT array_append( v_id, s_id ) INTO v_id;
SELECT array_append( v_latitude, s_latitude ) INTO v_latitude;
SELECT array_append( v_longitude, s_longitude ) INTO v_longitude;
END LOOP;
The arrays are declared as:
v_id integer[];
v_latitude double precision[];
v_longitude double precision[];
I would rather use CREATE TYPE
and pass one array with each element containing three values.
How would you code this so that it does not use a FOR ... LOOP
?
The first line of the PL/R function is:
stations <- cbind( v_id, v_longitude, v_latitude )
I would like to eliminate that line of code and simplify the SELECT
query.
Thank you.