#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    philippines
    Posts
    4
    Rep Power
    0

    array parameter in Function


    Hi,
    Please forgive me if would post this very novice question.... How to use an array parameter in function and how to call them? If you could give me an example I would appreciate it so much or any references....
    Thanks a million in advance for your help and time...

    Regards,
    Dax

    To rycamor:
    First of all thank you for your advice in the array parameter
    instead of increasing the number of parameter to be used..
    I've created a table with this code :
    -- Table: sample
    CREATE TABLE sample (
    code numeric(9, 0) NOT NULL,
    name varchar(60),
    age numeric(3, 0),
    CONSTRAINT sample_pkey PRIMARY KEY (code)
    ) WITH OIDS;

    ...and created an insert stored function:
    -- Function: ins_sample(_varchar)
    CREATE FUNCTION ins_sample(_varchar) RETURNS int4 AS 'Declare
    code ALIAS FOR $1[1];
    name ALIAS FOR $1[2];
    age ALIAS FOR $1[3];

    BEGIN
    insert into sample values
    (to_number(code,"999999999"),name,to_number(age,"999"));
    RETURN 0;
    end;' LANGUAGE 'plpgsql';

    But I cannot make this work.... Am I doing something wrong here?
    How to execute the stored function? I've been trying to execute it by
    using this statement but it gives me a parser error :
    select ins_sample('{''1'', ''daxdtest'', ''12''}');

    Thanks a lot in advance....
    Last edited by dax; November 18th, 2002 at 02:07 AM.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Actually, this is not a 'novice' question here . I had to think about it a bit myself, and experiment with your code. It's going to take some time to explain it, though.

    Before we get started, exactly what is the 'parse error' message you receive? What version of PostgreSQL are you running?

    It is always best to be specific when asking these questions. Showing me the actual error message is much better than just telling me you have a parse error.
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    philippines
    Posts
    4
    Rep Power
    0
    Hi,
    The error message occurs when I would try to run my insert function: select ins_sample('{''10'', ''daxdtest'', ''12''}') ... this would give me then: parse error at or near "[". Btw, my PostgreSQL version is 7.2.3.
    Thanks for your help in advance....

    Regards,
    Dax
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Here is the best I can deduce after testing (the documentation isn't too helpful here):

    1. Apparently you cannot create aliases for array elements; only for the whole array:
    Code:
    CREATE FUNCTION ins_sample(_varchar) RETURNS int4 AS 'Declare
    userinfo ALIAS FOR $1;
     BEGIN
    insert into sample values (to_number(userinfo[1],"999999999"),userinfo[2],to_number(userinfo[3],"999"));
    RETURN 0;
    end;' LANGUAGE 'plpgsql';
    Basically, there is little point in using aliases at all for array parameters, unless you have multiple arrays as incoming parameters. (Which you will probably have to do, after you read the next part)

    2. PostgreSQL is not very happy with any attempts to cast array elements (to_number(), etc...). Basically, whatever was your array type on creation, you are stuck with that. So, if you want parameters of different types, you will have to use different arrays:
    Code:
    CREATE FUNCTION ins_sample(_varchar, _int4) RETURNS int4 AS 'Declare
    textinput ALIAS FOR $1;
    numberinput ALIAS FOR $2;
     BEGIN
    insert into sample values (numberinput[1],textinput[1],numberinput[2]);
    RETURN 0;
    end;' LANGUAGE 'plpgsql';
    3. This all seems like too much work, to me. If you really need a stored procedure just to insert data into a table, why not just create a function inside your programming environment to do that?

    4. Or, for another work-around, try installing the Perl procedural language handler. Then you can just send one large string as a parameter, with delimiters (commas, tabs, whatever), and use Perl's string-handling functions to break it up into different elements to insert.

    5. Finally, I don't understand what you are trying to accomplish anyway. Why is "SELECT ins_table(value1,value2,value3)" better than just doing "INSERT INTO table VALUES(value1,value2,value3)"? If you are trying to get one function to insert into multiple tables, you should just create a view which joins those tables, and create a query rewrite rule that allows for inserts into that view (the rule actually 'takes apart' your query and puts each value in its appropriate table). See the following for more info on query rewrite rules:

    http://www.postgresql.org/idocs/inde...reaterule.html
    http://www.ca.postgresql.org/docs/aw...k/node124.html
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    philippines
    Posts
    4
    Rep Power
    0
    Hi,
    Thanks for your advice and thanks a million to you. Just for a background of what I'm try to achieve: I have an application in which the user's can choose what back-end they want either SQLServer 2000, Oracle 9i or PostgreSQL (the application by the way is in VB.Net). So I created a DAL (Data Access Layer) to which would interact to the database (in which the user choose) so that my BL (Business Layer) would not mind which database the application is using. But the probelm is these databases has a specific SQL stataments or process in doing things so I opted that all of my insert and other database interaction would be done in the Databases, so that I would have a uniform of calling things and would not mind what my back-end is....
    Anyway, THANKS A MILLION FOR THE ADVICE AND TIPS. And MORE POWER TO YOU!!!!!

    Regards,
    Dax

IMN logo majestic logo threadwatch logo seochat tools logo