Обсуждение: merge result sets
hi all
i'm using postgres 7.3
my problem is i want to build a helper table:
UPDATE studienmodul_summary
SET kategorie = (SELECT kategorie_bezeichnung
if the SELECT gives back just one result row, everthing is easy. my
problem is, there is sometimes more than one result row.
is there a way to concatenate all result rows and insert them in one
field?
any hint is very much appreciated.
thanks
simon
--
Simon Litwan simon.litwan@wyona.com
Wyona Inc. - Open Source Content Management - Apache Lenya
http://www.wyona.com http://lenya.apache.org
On Tue, Jun 20, 2006 at 12:06:24 +0200, simon <simon.litwan@wyona.com> wrote: > hi all > > i'm using postgres 7.3 > > my problem is i want to build a helper table: > > UPDATE studienmodul_summary > SET kategorie = (SELECT kategorie_bezeichnung > > if the SELECT gives back just one result row, everthing is easy. my > problem is, there is sometimes more than one result row. > > is there a way to concatenate all result rows and insert them in one > field? Yes. You can have the subselect call a custom aggregate function that does this for you. Sample code for doing this has been posted on the list multiple times and you should be able to find it in the archives.
On Die, 2006-06-20 at 15:34 -0500, Bruno Wolff III wrote: > On Tue, Jun 20, 2006 at 12:06:24 +0200, > simon <simon.litwan@wyona.com> wrote: > > hi all > > > > i'm using postgres 7.3 > > > > my problem is i want to build a helper table: > > > > UPDATE studienmodul_summary > > SET kategorie = (SELECT kategorie_bezeichnung > > > > if the SELECT gives back just one result row, everthing is easy. my > > problem is, there is sometimes more than one result row. > > > > is there a way to concatenate all result rows and insert them in one > > field? > > Yes. You can have the subselect call a custom aggregate function that does > this for you. Sample code for doing this has been posted on the list > multiple times and you should be able to find it in the archives. thanks for this hint i didn't konw about the custom aggregate function. i found comma_aggregate(text,text) amd similar examples. unfortunatly i didn't found something like comma_aggregate(SELECT...). is it possible to write an aggregate function that takes the result rows of any number and makes a long string out of it? it would be great if someone would have done something before and is willing to share. but hints where to find docu and/or howtos about writting customaggregate functions are also very welcom. simon > -- Simon Litwan simon.litwan@wyona.com Wyona Inc. - Open Source Content Management - Apache Lenya http://www.wyona.com http://lenya.apache.org
On Mit, 2006-06-21 at 00:09 +0200, simon wrote: > On Die, 2006-06-20 at 15:34 -0500, Bruno Wolff III wrote: > > On Tue, Jun 20, 2006 at 12:06:24 +0200, > > simon <simon.litwan@wyona.com> wrote: > > > hi all > > > > > > i'm using postgres 7.3 > > > > > > my problem is i want to build a helper table: > > > > > > UPDATE studienmodul_summary > > > SET kategorie = (SELECT kategorie_bezeichnung > > > > > > if the SELECT gives back just one result row, everthing is easy. my > > > problem is, there is sometimes more than one result row. > > > > > > is there a way to concatenate all result rows and insert them in one > > > field? > > > > Yes. You can have the subselect call a custom aggregate function that does > > this for you. Sample code for doing this has been posted on the list > > multiple times and you should be able to find it in the archives. > > thanks for this hint i didn't konw about the custom aggregate function. > i found comma_aggregate(text,text) amd similar examples. > unfortunatly i didn't found something like comma_aggregate(SELECT...). > > is it possible to write an aggregate function that takes the result rows > of any number and makes a long string out of it? > > it would be great if someone would have done something before and is > willing to share. > but hints where to find docu and/or howtos about writting > customaggregate functions are also very welcom. > > simon > > or in other words, i just would like to know how to rewrite SET kategorie = array_to_string ((SELECT ARRAY (SELECT kategorie_bezeichnung so it works in psql7.3 as well. simon -- Simon Litwan simon.litwan@wyona.com Wyona Inc. - Open Source Content Management - Apache Lenya http://www.wyona.com http://lenya.apache.org
On Wed, Jun 21, 2006 at 12:23:44PM +0200, simon wrote: > or in other words, i just would like to know how to rewrite > > SET kategorie = array_to_string ((SELECT ARRAY (SELECT > kategorie_bezeichnung > > so it works in psql7.3 as well. The aggregate stuff should work. something like: SET kategorie = (SELECT comma_aggregate(kategorie_bezeichnung) FROM ...) should do the trick. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
On Mit, 2006-06-21 at 12:34 +0200, Martijn van Oosterhout wrote: > On Wed, Jun 21, 2006 at 12:23:44PM +0200, simon wrote: > > or in other words, i just would like to know how to rewrite > > > > SET kategorie = array_to_string ((SELECT ARRAY (SELECT > > kategorie_bezeichnung > > > > so it works in psql7.3 as well. > > The aggregate stuff should work. something like: > > SET kategorie = (SELECT comma_aggregate(kategorie_bezeichnung) FROM ...) > > should do the trick. i just found CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text, sfunc=comma_aggregate, stype=text, initcond='' ); and this didn't work with the above mentioned querry. i actually never found any docu about how tor write custom function which takes a whole result set no matter how many rows. thanks anyway simon > > Have a nice day, -- Simon Litwan simon.litwan@wyona.com Wyona Inc. - Open Source Content Management - Apache Lenya http://www.wyona.com http://lenya.apache.org
On Wed, Jun 21, 2006 at 02:08:29PM +0200, simon wrote: > > The aggregate stuff should work. something like: > > > > SET kategorie = (SELECT comma_aggregate(kategorie_bezeichnung) FROM ...) > > > > should do the trick. > i just found > > CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE > WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql > IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text, > sfunc=comma_aggregate, stype=text, initcond='' ); > > and this didn't work with the above mentioned querry. > > i actually never found any docu about how tor write custom function > which takes a whole result set no matter how many rows. Then you havn't looked very hard: http://www.postgresql.org/docs/current/interactive/sql-createaggregate.html You created an aggregate called "comma" so that's how you should call it. This is really no different from the SQL standard min(), max() and sum() functions. SET kategorie = (SELECT comma(kategorie_bezeichnung) FROM ...) Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
On Mit, 2006-06-21 at 14:16 +0200, Martijn van Oosterhout wrote: > On Wed, Jun 21, 2006 at 02:08:29PM +0200, simon wrote: > > > The aggregate stuff should work. something like: > > > > > > SET kategorie = (SELECT comma_aggregate(kategorie_bezeichnung) FROM ...) > > > > > > should do the trick. > > i just found > > > > CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE > > WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql > > IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text, > > sfunc=comma_aggregate, stype=text, initcond='' ); > > > > and this didn't work with the above mentioned querry. > > > > i actually never found any docu about how tor write custom function > > which takes a whole result set no matter how many rows. > > Then you havn't looked very hard: > > http://www.postgresql.org/docs/current/interactive/sql-createaggregate.html > > You created an aggregate called "comma" so that's how you should call > it. This is really no different from the SQL standard min(), max() and > sum() functions. > > SET kategorie = (SELECT comma(kategorie_bezeichnung) FROM ...) > > Have a nice day, thanks very much. you're right i should have read the docu harder. but now everthing works fine. you made my day. simon -- Simon Litwan simon.litwan@wyona.com Wyona Inc. - Open Source Content Management - Apache Lenya http://www.wyona.com http://lenya.apache.org