Sample PL/pgSQL function to walk multidimensional arrays
Submitted by tom on Tue, 2013-07-30 13:48
This function expects a one-dimensional bigint array and returns it as rows of a result table.
CREATE OR REPLACE FUNCTION return_array(anyarray) RETURNS TABLE (id bigint) AS
$$
DECLARE
myarray bigint[];
BEGIN
input := $1;
FOR i IN 1..array_length(myarray, 1)
LOOP
RETURN QUERY SELECT myarray[i];
END LOOP;
RETURN;
END
$$
LANGUAGE plpgsql VOLATILE STRICT;
$$
DECLARE
myarray bigint[];
BEGIN
input := $1;
FOR i IN 1..array_length(myarray, 1)
LOOP
RETURN QUERY SELECT myarray[i];
END LOOP;
RETURN;
END
$$
LANGUAGE plpgsql VOLATILE STRICT;
One can then query this for instance like this:
SELECT * FROM return_array(ARRAY[0,1,2]);
The following function expects a three-dimensional bigint array and returns each element as a result table:
CREATE OR REPLACE FUNCTION return_3darray(anyarray) RETURNS TABLE (id bigint) AS
$$
DECLARE
myarray bigint[][][];
BEGIN
myarray := $1;
FOR i IN 1..array_length(myarray, 1)
LOOP
FOR j IN 1..array_length(myarray, 2)
LOOP
FOR k IN 1..array_length(myarray, 3)
LOOP
RETURN QUERY SELECT myarray[i][j][k];
END LOOP;
END LOOP;
END LOOP;
RETURN;
END
$$
LANGUAGE plpgsql VOLATILE STRICT;
$$
DECLARE
myarray bigint[][][];
BEGIN
myarray := $1;
FOR i IN 1..array_length(myarray, 1)
LOOP
FOR j IN 1..array_length(myarray, 2)
LOOP
FOR k IN 1..array_length(myarray, 3)
LOOP
RETURN QUERY SELECT myarray[i][j][k];
END LOOP;
END LOOP;
END LOOP;
RETURN;
END
$$
LANGUAGE plpgsql VOLATILE STRICT;
It can be queried like this:
SELECT * FROM return_3darray(ARRAY[ [[1,4],[2,1]], [[2,1],[3,1]], [[3,1],[4,2]] ] );
Trackback URL for this post:
https://blog.voodoo-arts.net/trackback/98
Gta 5 For Android Download Gta 5 For Android Apk Dwgamez
- Antwort
Submitted by ebafis (not verified) on Tue, 2020-03-10 12:30.Apk Gta 5 Gta 5 Game Free Download For Android Mobile Ppsspp
- Antwort
Submitted by iligix (not verified) on Mon, 2020-03-09 20:14.Apk Gta 5 Gta 5 Game Download For Android In Low Mb
- Antwort
Submitted by usybuva (not verified) on Fri, 2020-03-06 22:40.Gta 5 Mobile Apk Free Download How To Get Gta 5 For Free In Android
- Antwort
Submitted by idunys (not verified) on Wed, 2020-03-04 04:37.Gta 5 Android
- Antwort
Submitted by genny (not verified) on Tue, 2019-12-03 01:53.tinder gold mod apk
- Antwort
Submitted by ALESIA (not verified) on Sun, 2019-12-01 05:45.Gta 5 Mobile Download
- Antwort
Submitted by Genvieve (not verified) on Sat, 2019-11-30 01:09.Gta 5 Mobile Game
- Antwort
Submitted by SHAD (not verified) on Wed, 2019-11-27 08:14.Gta 5 Mobile App
- Antwort
Submitted by Freddie (not verified) on Mon, 2019-11-25 03:50.Gta 5 Android
- Antwort
Submitted by TONISHA (not verified) on Sat, 2019-11-23 22:43.tinder plus free iphone
- Antwort
Submitted by Claribel (not verified) on Thu, 2019-11-21 06:05.fork lift
- Antwort
Submitted by ARNETTA (not verified) on Mon, 2019-11-18 18:59.tinder unlimited swipes
- Antwort
Submitted by berny (not verified) on Thu, 2019-11-14 07:08.szybkie pozyczki przez telefon
- Antwort
Submitted by ulylojado (not verified) on Fri, 2017-08-11 23:27.92 umsonnst daten
Gratis kostenlos 23
Live Sex, Cam Chat, XXX
- Antwort
Submitted by Boybc (not verified) on Tue, 2014-03-11 02:51.Się na szybkie pożyczki bez bik geneza podarek. Trwaniem pożyczki własnej komuś pożądałoby się wobec tego pozyczkipozabankowe.webgarden.com do tys.
- Antwort
Submitted by Adafajuluwutorumedo (not verified) on Thu, 2014-03-06 07:10.You don't need a own function - PostgreSQL offers a "unnest" function. More, your code is terrible ugly: a) you have to use RETURN NEXT in this use case, b) arrays in pg should not start from 1, so your loop cycle is wrong.
RETURNS SETOF anyelement AS $$
BEGIN
FOR i IN array_lower($1,1) .. array_upper($1,1)
LOOP
RETURN NEXT $1[i];
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgSQL STRICT IMMUTABLE;
</code?
- Antwort
Submitted by Pavel Stehule (not verified) on Tue, 2013-08-13 19:50.