#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0

    Exclamation Array in plpgsql -- HELP


    Hello everyone,

    i have mandrake 9.0 linux with postgresql 7.3.4 & i want to create one simple function to define & assign arrays in the function & return manipulate array in the function...

    Here is the code what i am trying to create...

    *******************************************
    create or replace function test() returns integer[] as '
    declare
    v_ret integer[]:=''{}'';
    begin
    v_ret[1] := 1;
    v_ret[2] := 2;
    return v_ret;
    end ;
    'language 'plpgsql';
    ********************************************

    I am getting error
    ********************************************
    ERROR: syntax error at or near "["
    WARNING: plpgsql: ERROR during compile of test near line 4
    WARNING: plpgsql: ERROR during compile of test near line 4
    ERROR: syntax error at or near "["
    ********************************************

    Can Anyone Help???
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    13
    The syntax is fine for a plpgsql function - did you add plpgsql support via createlang plpgsql <database>?
    Casuistry Agnostic
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    The syntax is ... almost fine . The one thing you forgot is that you can't declare an integer array element to have a value of {}, which is nothing. Try it this way:

    Code:
    create or replace function test() returns integer[] as '
    declare
    v_ret integer[]:=''{0}'';
    begin
    v_ret[1] := 1;
    v_ret[2] := 2;
    return v_ret;
    end ;
    'language 'plpgsql';
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0

    Angry Still Not Working!!


    Hello,

    Thanks for the help..

    But we checked about create lang and the initialization by ''{0}''; and found that it is still giving the same error !!

    We think it might be something related to Mandrake.. Can It Be??

    Will look forward to replies..

    Majolee
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    13
    The problem is specifying the value of array element - that seems to not yet be possible in plpgsql, or at least not until 7.4 is released (Developers' TODO list). It's in beta now.
    Casuistry Agnostic
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Aha, yes... I was using 7.4 beta
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo