PostgreSQL: Subselect Return als String

[NeoVanGoth]

Hi,
irgendwie komm ich grad nicht so recht auf den grünen Zweig ;)
Folgendes Szenario:
Ich habe einen Table tbl_usr und einen tbl_group. Es existiert eine 1-zu-n-Relation von usr zu group, d.h. jeder usr kann mit mehren group-Datensätzen verknüpft sein (klar).

Nun hätte ich gern eine View, die mir 1. alle Columns von usr zurückgibt, als auch einen String, in dem kommasepariert alle groups drinstehen.

z.B. (usr_id, usr_login, groups) => "1", "testuser", "1,2,3"

Versucht habe ich es über einen Subselect: SELECT usr_id, usr_login, (SELECT usr_group_group_id FROM tbl_group WHERE usr_group_usr_id = usr_id) WHERE usr_id = 1;

Funktioniert allerdings nicht, weil der Subselect natürlich mehr als eine Row zurückgibt und die nicht in einen String verwandelt wird. Von MySQL hatte ich sowas wie Concat in Erinnerung, die PGSQL-Variante davon haut aber in dem Fall auf keinen Fall hin.

Hat jemand ne Ahnung, wie ich das hinbekommen könnte?

Ich spiele derweil mal mit einer PL/pgSQL-Funktion rum, evlt. geht das so...

Danke für die Hilfe!

[Rouven]

Hi,

das dürfte IMHO ein Problem sein, dass dir bei den allermeisten DBMS bevorsteht. CONCAT fasst rinzipiell verschiedene Informationen einer Zeile zusammen, du suchst aber etwas, das verschiedene Zeilen zusammenfasst.
Laut SQL-Standard (99? 2003?) dürften dich (Multi-)Sets weiterbringen, d.h. die Möglichkeit die Ergebnisse einer Abfrage von Zeilen in eine Menge "umzugruppieren". Ich habe mich aber weder intensiv damit beschäftigt noch weiß ich, ob das derzeit von einem DBMS hinreichend unterstützt wird.

MfG
Rouven

[Ilja]

yo,

Ich habe einen Table tbl_usr und einen tbl_group. Es existiert eine 1-zu-n-Relation von usr zu group, d.h. jeder usr kann mit mehren group-Datensätzen verknüpft sein (klar).

da habe ich meine zweifel, es handelt sich wohl eher um eine n:m beziehung, sprich jeder user kann in mehreren gruppen sein und jede gruppe kann mehrere user besitzen, es sei denn du meinst, dass jeder user nur eine gruppe besitzen kann.

Nun hätte ich gern eine View, die mir 1. alle Columns von usr zurückgibt, als auch einen String, in dem kommasepariert alle groups drinstehen.

das wird ohne zusätzliche programmierung nicht möglich sein, da mann ja nie genau weiß, wieviele gruppen es geben kann. und somit wird es schwierig, die selektion in eine projektion umzuwandeln. Oracle kann so etwas mit sqlplus.

Funktioniert allerdings nicht, weil der Subselect natürlich mehr als eine Row zurückgibt und die nicht in einen String verwandelt wird.

genau das ist oben angesprochene problem von selektion und projektion. man kann soetwas lösen, wenn man genau weiss, wieviele datensätze ausgewählt werden. aber in deinem fall ist es unterschiedlich.

Von MySQL hatte ich sowas wie Concat in Erinnerung, die PGSQL-Variante davon haut aber in dem Fall auf keinen Fall hin.

Concat fügt nur spalten inerhalb eines datensatzes zusammen, du willst aber informationen zusammenführen, die in mehreren datensätze der ergebnismenge stehen.

Ich spiele derweil mal mit einer PL/pgSQL-Funktion rum, evlt. geht das so...

mit PL/SQL geht es auf jeden fall, mit sqlplus reicht dafür sogar ein break befehl. ich kenne mich leider nicht in postrgeSQL aus.....

Ilja

[Tom]

Hello,

Ich habe einen Table tbl_usr und einen tbl_group. Es existiert eine 1-zu-n-Relation von usr zu group, d.h. jeder usr kann mit mehren group-Datensätzen verknüpft sein (klar).

Nur BTW:

Das wäre aber eine n:m Beziehung.
Sowohl User als auch Gruppe sind Entitäten.

Jeder User kann in m Grippen sein und Jede Gruppe kann n User haben.
Dafür würdest Du dann drei Dateien benötigen.

Harzliche Grüße vom Berg
http://www.annerschbarrich.de

Tom

[NeoVanGoth]

Hmm, vielen Dank für die bisherigen Gedanken.
Dabei ist mir aufgefallen, dass ich mich etwas unpräzise ausgedrückt habe, natürlich ist zwischen Usern und Gruppen eine n:m-Beziehung, in dem Fall sind die Gruppen aber extern definiert, d.h. man könnte meine tbl_group als Verknüpfungstabelle ansehen, die folgendermaßen aufgebaut ist:

CREATE TABLE tbl_usr_group (
usr_group_usr_id int, /* referenziert tbl_usr.usr_id */
usr_group_group_id int /* referenziert eine externe ID */
);

[NeoVanGoth]

Ok, habs über eine Funtion gelöst:

CREATE OR REPLACE FUNCTION f_usr_groups (int) RETURNS text AS $$
DECLARE
 groups text;
 r_groups RECORD;
BEGIN
 groups = '';
 FOR r_groups IN SELECT usr_group_group_id FROM tbl_usr_group WHERE usr_group_usr_id = $1 ORDER BY usr_group_group_id LOOP
  groups := groups || ',' || r_groups.usr_group_group_id;
 END LOOP;
 groups := trim (leading ',' from groups);
 RETURN groups;
END;
$$ LANGUAGE 'plpgsql';

Siehe da, liefert genau das, was ich brauche :)

Dane fürs Nachdenken :)

[dedlfix]

echo $begrüßung;

Funktioniert allerdings nicht, weil der Subselect natürlich mehr als eine Row zurückgibt und die nicht in einen String verwandelt wird. Von MySQL hatte ich sowas wie Concat in Erinnerung, die PGSQL-Variante davon haut aber in dem Fall auf keinen Fall hin.

Falls das mal jemand für MySQL sucht, die Funktion nennt sich GROUP_CONCAT() und wird im Kapitel GROUP BY (Aggregate) Functions beschrieben.

echo "$verabschiedung $name";

[Daniel Thoma]

Hallo NeoVanGoth,

Funktioniert allerdings nicht, weil der Subselect natürlich mehr als eine Row zurückgibt und die nicht in einen String verwandelt wird. Von MySQL hatte ich sowas wie Concat in Erinnerung, die PGSQL-Variante davon haut aber in dem Fall auf keinen Fall hin.

Doch, die PGSQL-Variante davon haut hin:

  
SELECT array_to_string(ARRAY(SELECT bla FROM t1), ',');  

Grüße

Daniel