Thread: pl/pgsql and returning rows
Here is the senario... I have a table defined as create table details ( field1 <type> field2 <type> . . . ); and a function: 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) How do i get at the actual information in the row? Is this type of function even possible? If not, is there a different manner in which i should approach this task? Thanx in advance.-Wade
From: "wade" <wade@wavefire.com> > 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
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" <wade@wavefire.com> > > > 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
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" <wade@wavefire.com> > > > > > 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, Pennsylvania19026
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" <wade@wavefire.com> > > > > > > > > > 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, Pennsylvania19026
From: "Bruce Momjian" <pgman@candle.pha.pa.us> > 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 The functions called get_details though, so I assumed it's supposed to be shorthand for a join. - Richard > > 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" <wade@wavefire.com> > > > > > > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >