Results 1 to 5 of 5

Thread: Using arrays

  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Arrow Unanswered: Using arrays

    HI ,

    I am writing a sorting program in pgplsql . I am using single dimensional array .when ever i am assigning characters to A[1] := 'ab'
    then it is not taking...HOw to assign the value to the single array and later i am checking like ...

    for i in 1..10
    loop
    for j in 1..10
    loop
    A[i]>B[j] then....
    end loop
    end loop

    HOw to do that?

  2. #2
    Join Date
    Jan 2004
    Posts
    19

    Re: Using arrays

    PL/pgSQL is not PL/SQL or Ada.

    http://www.postgresql.org/docs/7.4/i...ive/index.html



    Originally posted by srikanthdba
    HI ,

    I am writing a sorting program in pgplsql . I am using single dimensional array .when ever i am assigning characters to A[1] := 'ab'
    then it is not taking...HOw to assign the value to the single array and later i am checking like ...

    for i in 1..10
    loop
    for j in 1..10
    loop
    A[i]>B[j] then....
    end loop
    end loop

    HOw to do that?

  3. #3
    Join Date
    Jan 2004
    Posts
    19

    Re: Using arrays

    Postgresql 7.4.1 fixes this so you can do this (you have to initialize the array first):

    DECLARE

    my_array[] INTEGER := \'{}\';

    BEGIN

    FOR i IN 1..10 LOOP
    my_array[i] := 10;
    END LOOP;
    my_array[11] := 12;

    END;

    But I don't think it works right for multi-dimensional arrays yet.

    Originally posted by srikanthdba
    HI ,

    I am writing a sorting program in pgplsql . I am using single dimensional array .when ever i am assigning characters to A[1] := 'ab'
    then it is not taking...HOw to assign the value to the single array and later i am checking like ...

    for i in 1..10
    loop
    for j in 1..10
    loop
    A[i]>B[j] then....
    end loop
    end loop

    HOw to do that?

  4. #4
    Join Date
    Sep 2004
    Posts
    1

    PL/pgSQL multidimension (matrix) array in function

    I found how to use, inside a PL/pgSQL function, a two-dimensions array (matrix).
    There is a limitation: the number of the 'columns' of the matrix is fixed at declaration time (in DECLARE section) and you need to manually initialize all the elements in the first 'row' of the matrix.

    The number of rows is unlimited and can be sized at runtime.

    Here is the code that can help you.

    If someone know how to manage an NxN array without limitations, please replay to this thread.

    I'm using PostgreSQL 7.4.1 on RH7.3

    Cheers
    Sergio

    CREATE OR REPLACE FUNCTION "testarray" (integer) RETURNS SETOF integer AS'
    DECLARE
    n alias for $1; -- number of rows is passed as argument
    i INTEGER;
    j integer;
    k INTEGER := 3; -- matrix columns number
    b integer[] := array[0,0,0]; -- need it to initialize the matrix!!
    a integer[][] := array[[0,0,0]]; -- need it to initialize the matrix!!
    begin

    for i in 1..n loop -- the i loop can start obviously from 2 (the first row is already present...) but for our purpose here we use 1
    a := array_cat(a,b);
    for j in 1..k loop
    a[i][j] := i*j;
    end loop;
    end loop;
    for i in 1..n loop

    return next null;
    return next i; -- need it to format in some way the output
    return next null;

    for j in 1..k loop
    return next a[i][j];
    end loop;

    end loop;
    return;
    end
    'LANGUAGE 'plpgsql';


    here is the output:

    => select * from testarray(8);
    testarray
    -----------

    1

    1
    2
    3

    2

    2
    4
    6

    3

    3
    6
    9

    4

    4
    8
    12

    5

    5
    10
    15

    6

    6
    12
    18

    7

    7
    14
    21

    8

    8
    16
    24
    (48 rows)

  5. #5
    Join Date
    May 2010
    Posts
    1

    Thumbs up Multidimensional arrays or Matrix MxN solution

    Recently I needed work with matrix MxN (M rows and N columns), searching solutions in a WEP a don't found nothing usefull for me, and decided investigate by myself.

    It is my proposal and hope it be usefull for many people (this functions were created in POSTGRES 8.1 over Windows Vista, but it works in any S.O.):

    (PD: Sorry for my english)

    Code:
    
    /* ------------------------------------------------------------- */
    
    -- This Function create MxN  (Rows x Cols) INTEGER Matrix 
    -- Autor   : Edgard Medina Q.
    CREATE OR REPLACE FUNCTION New_Matrix (
           IN    pn_Rows         INTEGER         -- Rows in the matriz.
          ,IN    pn_Cols         INTEGER         -- Columns in the matrix.
          ,IN    pn_Init_Value   INTEGER = 0 )   -- Initialize the matrix with this value.
    RETURNS INTEGER[][] AS $$
    DECLARE
       -- The query initialize the columns
       va_Array    INTEGER[]   := ARRAY( SELECT pn_Init_Value FROM generate_series(1, pn_Cols) ) ; 
       vm_Matrix   INTEGER[][] := ARRAY[ va_Array ];   -- Initialize the first row
    BEGIN
       FOR vn_Idx IN 2..pn_Rows LOOP    
          vm_Matrix := array_cat( vm_Matrix, va_Array ) ;  -- Create and initialize the matrix
       END LOOP ;
       RETURN  vm_Matrix ;
    END ; $$ LANGUAGE plpgsql;
    
    /* ------------------------------------------------------------- */
    
    --  This Function make a test in a Matriz from Mx3, M rows and 3 columns.
    --  and his result is showed in a query.
    CREATE OR REPLACE FUNCTION test_matrix1(pn_Rows INTEGER) 
    RETURNS TABLE(col_1 INTEGER, col_2 INTEGER, col_3 INTEGER) AS $$
    DECLARE
       vn_Cols    INTEGER     := 3 ;                             -- matrix columns number
       matrix     INTEGER[][] := New_Matrix(pn_Rows, vn_Cols) ;  -- Create and initialize the matrix
    BEGIN
       FOR i IN 1..pn_Rows LOOP -- now we can use the matrix like another language
          FOR j IN 1..vn_Cols LOOP
             matrix[i][j] := i * j ;
          END LOOP;
       END LOOP;
       
       RETURN  QUERY SELECT matrix[r][1], matrix[r][2], matrix[r][3] FROM generate_series(1, pn_Rows) g(r);
    END; $$ LANGUAGE plpgsql ;
    
    SELECT * FROM test_matrix1(5) ;   -- Test 1, using 5 rows.
    
    Query result is:
    
    col_1     col_2     col_3
    -------   -------   -------   
    1         2         3
    2         4         6
    3         6         9
    4         8         12
    5         10        15
    
    (5 rows proccess)
    
    /* ------------------------------------------------------------- */
    
    -- This Function make a test in a Matriz from Mx3 (M rows and 3 columns).
    -- This sample function I take from Sergio Faintel and I made a ligth change.
    CREATE OR REPLACE FUNCTION test_matrix2(n_Rows INTEGER) 
    RETURNS SETOF integer AS $$
    DECLARE
       n_Cols    INTEGER     := 3;                             -- matrix columns number
       matrix    INTEGER[][] := New_Matrix(n_Rows, n_Cols);    -- Initialize  the matrix
    BEGIN
       FOR i IN 1..n_Rows LOOP    -- now we can use the matrix like another language
          FOR j IN 1..n_Cols LOOP
             matrix[i][j] := i * j ;
          END LOOP;
       END LOOP;
       FOR i IN 1..n_Rows LOOP
          RETURN NEXT null;
          RETURN NEXT i; 
          RETURN NEXT null;
    
          FOR j IN 1..n_Cols LOOP
             RETURN NEXT matrix[i][j];
          END LOOP;
       END LOOP;
       RETURN ;
    END; $$ LANGUAGE plpgsql ;
    
    SELECT test_matrix2(3);     -- test 2, using 3 rows.
    
    Query result is:
    
    test_matrix2
    ------------
    
    1                        <-- Row
    
    1                        <-- Columns
    2
    3
    
    2                        <-- Row
    
    2                        <-- Columns
    4
    6
    
    3                        <-- Row
    
    3                        <-- Columns
    6
    9
    
    (18 rows proccess)
    /* ------------------------------------------------------------- */
    
    Last edited by edgard.medina.q; 05-17-10 at 13:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •