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....

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.

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

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:

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:


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/ind...createrule.html
http://www.ca.postgresql.org/docs/a...ok/node124.html

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