Thread: Simple function question
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
On Tue, Oct 26, 2004 at 07:12:36AM -0400, Sean Davis wrote:
> 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'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]);
> 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....
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/
> > 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, 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
On Oct 26, 2004, at 9:49 AM, Sean Davis wrote: >> >> 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, > > 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)? > Sorry. Answered (partially) my own question. max_expr_depth is set to 10000 as default on my MacOS installation (7.4.3). Sean
On Tue, Oct 26, 2004 at 09:49:43 -0400, Sean Davis <sdavis2@mail.nih.gov> wrote: > > 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)? 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.