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 f2RMLSw06304 for ; Tue, 27 Mar 2001 17:21:28 -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 RAA14603; Tue, 27 Mar 2001 17:21:22 -0500 (EST) From: Bruce Momjian Message-Id: <200103272221.RAA14603@candle.pha.pa.us> Subject: Re: RE: pl/pgsql and returning rows In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B0949DF@NEZU> "from Jeff Eckermann at Mar 27, 2001 04:17:09 pm" To: Jeff Eckermann Date: Tue, 27 Mar 2001 17:21:22 -0500 (EST) CC: PostgreSQL-SQL 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/562 X-Sequence-Number: 2232 FYI, I am always looking for additional examples that I should add in the next edition. > Yes: good example! I keep a printed copy on my desk... :-) > > > -----Original Message----- > > From: Bruce Momjian [SMTP:pgman@candle.pha.pa.us] > > Sent: Tuesday, March 27, 2001 3:52 PM > > To: Jeff Eckermann > > Cc: pgsql-sql@postgresql.org; wade > > Subject: Re: [SQL] RE: pl/pgsql and returning rows > > > > 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 > -- 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