Re: Simple function question

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/