Sample PL/pgSQL function to walk multidimensional arrays
Submitted by tom on Tue, 2013-07-30 12: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 11:30.Apk Gta 5 Gta 5 Game Free Download For Android Mobile Ppsspp
- Antwort
Submitted by iligix (not verified) on Mon, 2020-03-09 19:14.Apk Gta 5 Gta 5 Game Download For Android In Low Mb
- Antwort
Submitted by usybuva (not verified) on Fri, 2020-03-06 21: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 03:37.Gta 5 Android
- Antwort
Submitted by genny (not verified) on Tue, 2019-12-03 00:53.tinder gold mod apk
- Antwort
Submitted by ALESIA (not verified) on Sun, 2019-12-01 04:45.Gta 5 Mobile Download
- Antwort
Submitted by Genvieve (not verified) on Sat, 2019-11-30 00:09.Gta 5 Mobile Game
- Antwort
Submitted by SHAD (not verified) on Wed, 2019-11-27 07:14.Gta 5 Mobile App
- Antwort
Submitted by Freddie (not verified) on Mon, 2019-11-25 02:50.Gta 5 Android
- Antwort
Submitted by TONISHA (not verified) on Sat, 2019-11-23 21:43.tinder plus free iphone
- Antwort
Submitted by Claribel (not verified) on Thu, 2019-11-21 05:05.fork lift
- Antwort
Submitted by ARNETTA (not verified) on Mon, 2019-11-18 17:59.tinder unlimited swipes
- Antwort
Submitted by berny (not verified) on Thu, 2019-11-14 06:08.szybkie pozyczki przez telefon
- Antwort
Submitted by ulylojado (not verified) on Fri, 2017-08-11 22:27.92 umsonnst daten
Gratis kostenlos 23
Live Sex, Cam Chat, XXX
- Antwort
Submitted by Boybc (not verified) on Tue, 2014-03-11 01: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 06: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 18:50.