crosstab erklärung

Nun ja, schnell & schmutzig:

test=# select * from chemie;
 verbindung  | element | atomanzahl
-------------+---------+------------
 Formaldehyd | C       |          1
 Formaldehyd | H       |          2
 Formaldehyd | O       |          1
 Wasser      | H       |          2
 Wasser      | O       |          1
(5 rows)

Nun eine Funktion:

create or replace function chemie() returns int as $$
declare
  anz_spalten int;
  i int;
  j int;
  k int;
  rec record;
  sql text;
  alt_verbindung text;
begin
  select into anz_spalten count(1) as anzahl from chemie group by verbindung order by anzahl desc limit 1;
  sql := 'create table chemie_crosstab (verbindung text';
  for i in 1 .. anz_spalten loop
    sql := sql || ', element' || i || ' text, atomanzahl' || i || ' int';
  end loop;
  sql := sql || ')';
  raise notice '%',sql;
  execute sql;

  alt_verbindung := '';
  k := 0;
  sql := '';
  for rec in select * from chemie order by verbindung, element loop
    k:=k+1;
    if alt_verbindung != rec.verbindung then
      if sql != '' then
        for j in k .. anz_spalten loop
          raise notice 'k: %',k;
          sql := sql || ',NULL,NULL';
        end loop;
        raise notice 'sql version 1 %', sql;
        execute sql;
        k:=0;
        sql:='';
      end if;
      sql := 'insert into chemie_crosstab(verbindung';
      for j in 1 .. anz_spalten loop
        sql := sql || ',element' || j || ', atomanzahl' || j ;
      end loop;
      sql := sql || ') values (' || quote_literal(rec.verbindung) || ',' || quote_literal(rec.element) || ',' || rec.atomanzahl;
    else
      sql := sql || ',' || quote_literal(rec.element) || ',' || rec.atomanzahl;
    end if;
    alt_verbindung := rec.verbindung;
    if k = anz_spalten then
      sql := sql || ')';
      raise notice 'fuehre aus: %',sql;
      execute sql;
      k:=0;
      sql:='';
    end if;
  end loop;
  for j in k+1 .. anz_spalten loop
    sql := sql || ',NULL, NULL';
  end loop;
  sql := sql || ')';
  raise notice 'fuehre zum Schluss aus: %', sql;
  execute sql;
  return 1;
end;
$$ language plpgsql;

Anwendung:

CREATE FUNCTION
test=*# select * from chemie();
NOTICE:  create table chemie_crosstab (verbindung text, element1 text, atomanzahl1 int, element2 text, atomanzahl2 int, element3 text, atomanzahl3 int)
NOTICE:  fuehre aus: insert into chemie_crosstab(verbindung,element1, atomanzahl1,element2, atomanzahl2,element3, atomanzahl3) values ('Formaldehyd','C',1,'H',2,'O',1)
NOTICE:  fuehre zum Schluss aus: insert into chemie_crosstab(verbindung,element1, atomanzahl1,element2, atomanzahl2,element3, atomanzahl3) values ('Wasser','H',2,'O',1,NULL, NULL)
 chemie
--------
      1
(1 row)

test=*# select * from chemie_crosstab;
 verbindung  | element1 | atomanzahl1 | element2 | atomanzahl2 | element3 | atomanzahl3
-------------+----------+-------------+----------+-------------+----------+-------------
 Formaldehyd | C        |           1 | H        |           2 | O        |           1
 Wasser      | H        |           2 | O        |           1 |          |
(2 rows)

Mag sein, daß da noch Fehler drin sind (z.B. beim zweiten Aufruf nörgelt er, daß die Tabelle schon existiert), ich hab das auch nicht weiter getestet. Der Weg sollte aber erkennbar sein.

Daß man, wenn man sowas schreiben will, sich etwas mit der Sprache beschäftigen muß, sollte klar sein.

Andreas

Hallo,

ok, wenn es denn sein muss, also doch die “stored procedures”. Da aber der Projektleiter dagegen ist (“Alles so einfach wie möglich”), werde ich das wohl erst mal lassen müssen. Vielen Danke für den Code, auf der Basis sollten sich, falls er seine Ansicht ändert, die notwendigen Kenntnisse verschaffen lassen, um die Details zu klären und den Code zum Laufen zu bringen, falls er es noch nicht tut. Ausserdem soll ich noch später Trigger programmieren und da könnte mir das vielleicht auch helfen.

Was SQL angeht, ob nun umständlich oder kompliziert, ist mir egal, wenn es funktioniert, ebenfalls die Dauer der Abfrage. Weiss jemand wie man mit “exists” arbeitet ?

Ich habe schon mehrere Versuche mit Crosstab gemacht, kriege aber immer eine Fehlermeldung (“unknown argument”). Könnte mal jemand bitte eine möglichst funktionierende SQL Anweisung mit Crosstab für die genannte Tabelle (im statischen Fall) formulieren ?

Vielen Dank

Thomas

Hallo nochmal,
ich hatte übersehen, dass in dem PGPL-SQL Code ein Crosstab Befehl war.
Muss das für standard SQL genauso mit "create table " aufgerufen werden ? In dem Code auf Seite 1 von Noel schien das nicht so zu sein.

Danke auf jeden Fall schon mal, unten nochmal meine Fehlermeldung:
Heisst das dass unsere Version von PostgresSQL (8.1.5) keine CROSSTAB -Funktion besitzt ?

Thomas




SQL Fehler:
FEHLER: Funktion crosstab(“unknown”) existiert nichtHINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.

In der Anweisung:

 
SELECT *FROM crosstab( 'SELECT pcon, element, numberofelement FROM pconcomposition ORDER BY pcon')
AS chemie_daten("pcon" text, "el1" text , "ne1" text, "el2" text , "ne2" text )where pcon = "CO2(g)"
[\code]

crosstab ist exakt was?


Was SQL angeht, ob nun umständlich oder kompliziert, ist mir egal, wenn es funktioniert, ebenfalls die Dauer der Abfrage. Weiss jemand wie man mit “exists” arbeitet ?

die Doku.
PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Subquery Expressions


Ich habe schon mehrere Versuche mit Crosstab gemacht, kriege aber immer eine Fehlermeldung (“unknown argument”). Könnte mal jemand bitte eine möglichst funktionierende SQL Anweisung mit Crosstab für die genannte Tabelle (im statischen Fall) formulieren ?

Vielen Dank

Thomas


test=*# select * from chemie ;
 verbindung  | element | atomanzahl
-------------+---------+------------
 Formaldehyd | C       |          1
 Formaldehyd | H       |          2
 Formaldehyd | O       |          1
 Wasser      | H       |          2
 Wasser      | O       |          1
(5 rows)

test=*# select * from crosstab('select verbindung, element, sum(atomanzahl)::text from chemie group by verbindung, element order by 1') as ct(element text, "O" text, "H" text, "C" text);
   element   | O | H | C
-------------+---+---+---
 Formaldehyd | 1 | 2 | 1
 Wasser      | 1 | 2 |
(2 rows)

Andreas

Nein. Das war ein Tabellenname. Ich hätte sie auch Pittiplatsch nennen können.

Muss das für standard SQL genauso mit "create table " aufgerufen werden ? In dem Code auf Seite 1 von Noel schien das nicht so zu sein.

Danke auf jeden Fall schon mal.

Thomas

Nein. Standard-SQL kennt IIRC keine Crosstabs.

Andreas

Hallo Leute,
hatte lange nichtmehr hier reingeschaut. Mein Problem hat sich zeitlich etwas nach hinten verschoben. Bin da noch nicht weiter und hab auch keine Zeit dafür jetzt, aber die letzten Posts von kretschmer sehen sehr vielversprechend für meine Lösung aus. Danke dafür!

Ja, du musst die Funktion erst noch installieren. Das ist ein SQL-Script, das in irgendeinem Postgres Verzeichnis liegt. Habs jetzt grad nicht im Kopf, muss aber einfach nur aufgerufen werden und gut ist.

Gruß
Noël