Skip site navigation (1) Skip section navigation (2)

Re: Simple function question

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Cc: Postgres <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Simple function question
Date: 2004-10-26 12:29:13
Message-ID: 20041026122913.GA83905@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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/

In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2004-10-26 13:49:43
Subject: Re: Simple function question
Previous:From: Sean DavisDate: 2004-10-26 11:12:36
Subject: Simple function question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group