I would like to create a function that accepts an array of IDs
(integers) and loops over them, returning the the rows (setof
rowtype...?) that match them. I have tried to no avail to form such a
function. As a final extension, I would like to be able to return rows
formed by a join across a few tables (return a setof RECORD type?). I
suppose I can just create the view I like then use a similar function
to the single-table version....

Thanks,
Sean



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Tue, Oct 26, 2004 at 07:12:36AM -0400, Sean Davis wrote:
I'm not certain what you're asking for, but perhaps this example
will be useful:

CREATE TABLE product (
prodid SERIAL PRIMARY KEY,
prodname VARCHAR(64) NOT NULL
);

CREATE FUNCTION prodlist(INTEGER[]) RETURNS SETOF product AS '
SELECT * FROM product WHERE prodid = ANY($1)
' LANGUAGE SQL;

INSERT INTO PRODUCT (prodname) VALUES ('Widget');
INSERT INTO PRODUCT (prodname) VALUES ('Gizmo');
INSERT INTO PRODUCT (prodname) VALUES ('Gadget');
INSERT INTO PRODUCT (prodname) VALUES ('Dohickey');
INSERT INTO PRODUCT (prodname) VALUES ('Thingamajig');
INSERT INTO PRODUCT (prodname) VALUES ('Whatsit');

SELECT * FROM prodlist(ARRAY[2,4,6]);

You could return SETOF RECORD but then your queries will need to
provide a column definition list. Another way would be to create
a custom type that describes a result record and return SETOF that
type. But before you do any of this, perhaps you should think about
whether you really need a function at all, or whether you can use
views and WHERE clauses.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Michael,

Thanks for the reply. The reason all of this comes up is that I have
an application in which the user can create "sets" of IDs. I then want
to be able to do logical operations on the sets of IDs and then return
the database objects based on the ID's in those sets. I tried the
function version using "ANY" and the simple query using "IN" and found
an order of magnitude difference in speed (IN is faster). So, it
appears that using views and where clauses is the way to go here. One
last question--is there a limit to the length of a SQL query (in terms
of characters), as some of these sets could be very large (up to 40000
integers)?

Thanks again.

Sean


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Oct 26, 2004, at 9:49 AM, Sean Davis wrote:

Sorry. Answered (partially) my own question. max_expr_depth is set to
10000 as default on my MacOS installation (7.4.3).

Sean


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Tue, Oct 26, 2004 at 09:49:43 -0400,
Sean Davis <sdavis2 (AT) mail (DOT) nih.gov> wrote:
For this kind of task you probably don't want to use arrays. There should
be a table matching sets to the ids that are in them. Then you would be able
to get the information you want using normal queries.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)