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: (view raw, whole thread or download thread mbox)
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

SELECT * FROM product WHERE prodid = ANY($1)

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

In response to


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-2017 The PostgreSQL Global Development Group