Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2RLpgw00858 for ; Tue, 27 Mar 2001 16:51:42 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id QAA10259; Tue, 27 Mar 2001 16:51:36 -0500 (EST) From: Bruce Momjian Message-Id: <200103272151.QAA10259@candle.pha.pa.us> Subject: Re: RE: pl/pgsql and returning rows In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B0949DE@NEZU> "from Jeff Eckermann at Mar 27, 2001 03:43:22 pm" To: Jeff Eckermann Date: Tue, 27 Mar 2001 16:51:36 -0500 (EST) CC: pgsql-sql@postgresql.org, wade X-Mailer: ELM [version 2.4ME+ PL77 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200103/561 X-Sequence-Number: 2231 MY book in chapter 18 has a Pl/PgSQL function called change_statename that does insert/update automatically. http://www.postgresql.org/docs/awbook.html > As a workaround, you can insert your row into an existing table, then > retrieve it from there later. I think you need to enumerate all of the > fields, as in 'INSERT INTO table VALUES (ret.field1, > ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. > Messy, but the best method available right now. > > > -----Original Message----- > > From: Richard Huxton [SMTP:dev@archonet.com] > > Sent: Tuesday, March 27, 2001 2:27 AM > > To: pgsql-sql@postgresql.org; wade > > Subject: Re: pl/pgsql and returning rows > > > > From: "wade" > > > > > create function get_details(int4) returns details as ' > > > declare > > > ret details%ROWTYPE; > > > site_rec record; > > > cntct contacts%ROWTYPE; > > > begin > > > select into site_rec * sites_table where id = $1 limit 1; > > > select into cntct * from contacts where id = site_rec.contact; > > > > > > -- and then i populate rows of ret. > > > ret.name := cntct.name; > > > ret.ip := site_rec.ip; > > > . > > > . > > > . > > > return ret; > > > end; > > > ' language 'plpgsql'; > > > > > > now the problem is when is when I do a: > > > SELECT get_details(55); > > > all i get is a single oid-looking return value: > > > get_details > > > ------------- > > > 136295592 > > > (1 row) > > > > Sorry - you can't return a row from a function at the present time (except > > for trigger functions which are special) although I believe this is on the > > todo list for a later 7.x release. > > > > Just from the top of my head, you might try a view with a select rule, > > although I'm not completely clear what your objectives are. > > > > - Richard Huxton > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026