Sample PL/pgSQL function to walk multidimensional arrays

  • strict warning: Non-static method view::load() should not be called statically in /usr/share/nginx/www/drupal/sites/all/modules/views/views.module on line 1113.
  • strict warning: Declaration of views_handler_field::query() should be compatible with views_handler::query($group_by = false) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_field.inc on line 0.
  • strict warning: Declaration of views_handler_field_user::init() should be compatible with views_handler_field::init(&$view, $options) in /usr/share/nginx/www/drupal/sites/all/modules/views/modules/user/views_handler_field_user.inc on line 0.
  • strict warning: Declaration of views_handler_argument::options_validate() should be compatible with views_handler::options_validate($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_argument.inc on line 0.
  • strict warning: Declaration of views_handler_argument::query() should be compatible with views_handler::query($group_by = false) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_argument.inc on line 0.
  • strict warning: Declaration of views_handler_sort::options_validate() should be compatible with views_handler::options_validate($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_sort.inc on line 0.
  • strict warning: Declaration of views_handler_sort::options_submit() should be compatible with views_handler::options_submit($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_sort.inc on line 0.
  • strict warning: Declaration of views_handler_sort::query() should be compatible with views_handler::query($group_by = false) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_sort.inc on line 0.
  • strict warning: Declaration of views_handler_filter::options_validate() should be compatible with views_handler::options_validate($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_filter.inc on line 0.
  • strict warning: Declaration of views_handler_filter::query() should be compatible with views_handler::query($group_by = false) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_filter.inc on line 0.
  • strict warning: Declaration of views_handler_relationship::query() should be compatible with views_handler::query($group_by = false) in /usr/share/nginx/www/drupal/sites/all/modules/views/handlers/views_handler_relationship.inc on line 0.
  • strict warning: Declaration of views_plugin_query::options_submit() should be compatible with views_plugin::options_submit($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/plugins/views_plugin_query.inc on line 0.
  • strict warning: Declaration of views_plugin_argument_validate::options_submit() should be compatible with views_plugin::options_submit($form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/plugins/views_plugin_argument_validate.inc on line 0.
  • strict warning: Declaration of views_plugin_row::options_validate() should be compatible with views_plugin::options_validate(&$form, &$form_state) in /usr/share/nginx/www/drupal/sites/all/modules/views/plugins/views_plugin_row.inc on line 0.

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;

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;

It can be queried like this:

 
SELECT * FROM return_3darray(ARRAY[ [[1,4],[2,1]], [[2,1],[3,1]], [[3,1],[4,2]] ] );

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;One can then query this for instance like this:SELECT * FROM return_array(ARRAY[0,1,2]);

Trackback URL for this post:

https://blog.voodoo-arts.net/trackback/98
szybkie pozyczki przez telefon

szybkie pozyczki przez telefon

Submitted by ulylojado (not verified) on Fri, 2017-08-11 23:27.
hellllo there

92 umsonnst daten
Gratis kostenlos 23

Live Sex, Cam Chat, XXX

Submitted by Boybc (not verified) on Tue, 2014-03-11 02:51.
bierzemy pozyczki przez internet

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.

Submitted by Adafajuluwutorumedo (not verified) on Thu, 2014-03-06 07:10.
You don't need a own function

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.

CREATE OR REPLACE FUNCTION return_array(anyarray)
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?

Submitted by Pavel Stehule (not verified) on Tue, 2013-08-13 19:50.

Tags for Sample PL/pgSQL function to walk multidimensional arrays