Ergebnismenge in Variable packen

Hallo,

gibt es einen Datentyp, in dem ich die Ergebnismenge einer Funktion zwischenspeichern kann ?

Hintergrund:

Ich habe eine dreifach geschachtelte FOR-Schleife die jeweils durch die Ergebnismenge einer Funktion(!) iteriert.

Die äußerste FOR-Schleife ist nicht das Problem, aber die inneren FOR-Schleifen müssen bei jedem neuen Durchgang ihre Funktion neu aufrufen, was doch reichlich bremst.

Da die Ergebnismenge nicht einer Tabelle sondern einer Funktion entstammt, scheidet wohl %ROWTYPE als Datentyp aus.

Unter INFORMIX sieht die Lösung so aus:

DEFINE varName  SET( LVARCHAR NOT NULL );

–> hier wird ein sog. SET von LVARCHAR’s übergeben.

Viele Grüße,

Andreas

Was liefert denn besagte Funktion konkret für einen Typ zurück?

Hallo,

die 3 Funktionen liefern jeweils eine Spalte vom Datentyp VARCHAR zurück.

CREATE OR REPLACE FUNCTION postgres.hmgetmatchkeys(...parameter...)
  [B]RETURNS SETOF character varying[/B] AS
     'LibHLAmatch.so', 'hmGetMatchKeys'
  LANGUAGE 'c' VOLATILE;

Und was hindert dich jetzt daran, im DECLARE Bereich eine VARCHAR Variable anzulegen und diese als Zwischenspeicher zu nutzen?

Ich habe folgendes Probiert:

DECLARE
   ...
   cur_a     VARCHAR(4);
   keys_a    VARCHAR NOT NULL;
   ...

BEGIN
   ...
   keys_a    := SELECT hmGetMatchKeys( ... Parameter ... );
   ...

   FOR cur_a IN keys_a LOOP
   ...

Leider funktioniert das aber so nicht, gibt immer einen 'Syntax Error at or near “select” ’ :frowning:

Wenn ich statt der Variablen das Select-Statement in der FOR-Schleife verwende funktioniert alles … aber dann wird halt jedes mal die Funktion abgearbeitet.

Ich weiss zwar immer noch nicht, von welcher FOR Schleife du genau sprichst, aber du solltest dir diesen Punkt der Dokumentation mal genauer durchlesen:

37.6.3 Executing a Query with a Single-Row Result
PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: Basic Statements

Das Problem mit der Variablen scheint gelöst zu sein.

keys_a    := hmGetMatchKeys( ... );

Jetzt kämpfe ich noch mit den besagten FOR-Schleifen, deren ‘Zähler’ vom Typ VarChar(4) durch die Variablen mit den Ergebnismengen ( eine Spalte mit entspr. VarChar-Werten) iterieren soll.

FOR cur_a IN keys_a LOOP

   FOR cur_b IN keys_b LOOP

      FOR cur_dr IN keys_dr LOOP

      ...

      END LOOP;

   END LOOP;

END LOOP;

Hier scheint irgendwas mit der Syntax nicht zu stimmen, aber die Fehlermeldung von PGAdminIII hilft da auch nicht wirklich weiter
(Syntax Error at or near $1, LINE 1) :frowning:

Problem gelöst … war ein syntaktischer Fehler:

FOR cur_a IN [B]SELECT keys_a[/B] LOOP

Es fehlte lediglich ein SELECT for der Variablen, welche die Ergebnismenge enthielt …

Vielen, vielen Dank !!!

akretschmer: Rechnung!

Menno, muss man hier alles selber machen? :wink:

Hallo noch mal,

leider war meine Freude etwas verfrüht.
Keine Fehlermeldungen zu bekommen ist leider nicht immer mit ‘funktioniert auch’ gleich zu setzen.

Die als VARCHAR deklarierte Variable ‘keys_a’ übernimmt zwar die Ergebnismenge der Funktion ‘hmGetMatchKeys( … )’ (Rückgabewert: RETURNS SETOF character varying), scheint sich aber nur den ersten Wert zu “merken” … daher werden meine Schleifen folglich auch nur ein mal durchlaufen.

Ich habe als work-arround erst mal 3 statische Tabellen angelegt, welche die Funktion meiner Variablen übernehmen. Durch diese iterieren jetzt meine Schleifen ordnungsgemäß … aber mit einer Performance, die doch sehr zu wünschen übrig läßt (Die Informix-Version mit Variablen als Zwischenspeicher läuft 25x schneller)

Mach ich vielleicht bei der Wertzuweisung etwas falsch ?!

keys_a    := SELECT hmGetMatchKeys( ... Parameter ... );

Viele Grüße,

Andreas

Klingt so, als ob Du “return next;” vergessen hast.

(Die Informix-Version mit Variablen als Zwischenspeicher läuft 25x schneller)

Niemand zwingt Dich zu iregdnwas, oder?
[/quote]

Andreas

Hallo Andreas (kiaaap),

RETURNS SETOF … liefert 0…n Zeilen einer Abfrage zurück. Die Variable ‘keys_a’ kann in der definierten Form nur 1 Zeile speichern. Mehrere Zeilen kann man nur in Tabellen speichern!



mfg

fristo

Natürlich zwingt mich keiner zu irgend was, der Faktor 25 sollte auch nur den Performanceverlust durch die Tabellen statt Variablen verdeutlichen.

Allerdings würde es mich als Freund von Open Source - Produkten doch schon freuen, wenn diese im Vergleich zu kommerziellen Produkten gut da stehen :wink:

Hintergrund: Ich mache gerade ein studentisches Praktikum und meine Aufgabe ist ein Performance-Vergleich Informix vs. Postgres bzgl. der hier benötigten Datenbankanforderungen. Bisher steht Postgres v.a. in Bezug auf die Performance ausgesprochen gut da … daher verwundert bzw. stört mich dieses Problem doch ein wenig.

Zurück zum Problem:
Also die Funktion selbst ist ja in C geschrieben und liefert ordnungsgemäß die gewünschte Tabelle zurück. Wenn ich die Tabellen (hier t_temp_keys_a u.s.w.) zum zwischenspeichern verwende funktioniert ja auch alles.
Nur wenn ich die Variablen (keys_a, u.s.w.) verwende bekommt die FOR-Schleife nur einen Wert. Daher meine Vermutung das ich bei der Zuweisung der Funktionsrückgabe in die Variable bzw. in der FOR-Schleife einen Fehler habe … auch wenn PG nichts anzeigt.

...
/*
INSERT INTO t_temp_keys_a  VALUES (hmGetMatchKeys(  'A',  a1,  a2,    'A*', dna_a1, dna_a2, pop_id, threshold ));
INSERT INTO t_temp_keys_b  VALUES (hmGetMatchKeys(  'B',  b1,  b2,    'B*', dna_b1, dna_b2, pop_id, threshold ));
INSERT INTO t_temp_keys_dr VALUES (hmGetMatchKeys( 'DR', dr1, dr2, 'DRB1*',  drb11,  drb12, pop_id, threshold ));
*/

SELECT hmGetMatchKeys(  'A',  a1,  a2,    'A*', dna_a1, dna_a2, pop_id, threshold ) INTO keys_a;
SELECT hmGetMatchKeys(  'B',  b1,  b2,    'B*', dna_b1, dna_b2, pop_id, threshold ) INTO keys_b;
SELECT hmGetMatchKeys( 'DR', dr1, dr2, 'DRB1*',  drb11,  drb12, pop_id, threshold ) INTO keys_dr;

FOR cur_a IN SELECT keys_a LOOP
   FOR cur_b IN SELECT keys_b LOOP
      cnt := 0;
      FOR cur_dr IN SELECT keys_dr LOOP
         ...

Nein. Schau:

test=# select * from foo;
 id |   word
----+-----------
  1 | car
  1 | fruit
  2 | car
  2 | fruit
  3 | car
  3 | vegetable
  2 | vegetable
  1 | car
(8 rows)

Time: 0.779 ms
test=*# create function get_all() returns setof text as $$declare t record;begin for t in select word from foo loop return next t.word; end loop; end;$$language plpgsql;
CREATE FUNCTION
Time: 1.883 ms
test=*# select * from get_all();
  get_all
-----------
 car
 fruit
 car
 fruit
 car
 vegetable
 vegetable
 car
(8 rows)

t ist eine RECORD-Variable, sie speichert keine Tabelle, sondern ‘nur’ eine Zeile.

Andreas

Ich fürchte ich drücke mich falsch oder undeutlich aus … oder ich blicke es einfach nicht ( was wohl eher zutrifft :astonished: ).

Mit SELECT * FROM hmGetMatchKeys( … Parameter …); bekomme ich auch meine gewünschte Tabelle zurück, wie in Deinem Beispiel.

Genau diesen Inhalt möchte ich aber in meiner zweiten Funktion für die verschachtelten FOR-Schleifen nutzen und zwar ohne jedesmal wieder die Funktion hmGetMatchKeys( … Parameter …) aufrufen zu müssen.

Daher Suche ich einen Weg die mit der Funktion ermittelte Grundgesamtheit durch die meine Schleifen iterieren sollen,so zwischen zu speichern, das die geschachtelten Schleifen öfter darauf zugreifen können.

Ich brauche also irgend was im Sinne von:

FOR cur_a IN [B]variable_mit_tabelle[/B] LOOP

Sorry für die Mühe … und vielen Dank für die Geduld …

keine Ahnung…


Daher Suche ich einen Weg die mit der Funktion ermittelte Grundgesamtheit durch die meine Schleifen iterieren sollen,so zwischen zu speichern, das die geschachtelten Schleifen öfter darauf zugreifen können.

Eine SRF (Set Returning Function) liefert Dir quasi eine Tabelle. Mit der kannst Du wieder wie mit einer ‘normalen’ Tabelle arbeiten. Ausgehend von meinem Beispiel:

test=*# select upper(foo) from get_all() foo;
   upper
-----------
 CAR
 FRUIT
 CAR
 FRUIT
 CAR
 VEGETABLE
 VEGETABLE
 CAR
(8 rows)

Hilft das?

Andreas

Hallo Andreas,

super Idee … so wie ich das verstehe löst das aber leider nicht mein Problem.

Ich führe den gravierenden Performance-Unterschied auf das entstehende Skalar in Bezug auf die Funktionsaufrufe zurück.

Nehmen wir an das jede Function eine Tabelle mit nur 10 Relationen zurückgibt:

FOR a IN SELECT * FROM function_a() LOOP           <--- 1x
                                                                                  |
   FOR b in SELECT * FROM function_b() LOOP         <--- 10x
                                                                                  |
      FOR c IN SELECT * FROM function_c() LOOP     <--- 100x

         ...

      END LOOP;

   END LOOP;

END LOOP;

In diesem Beispiel wären zum Abarbeiten aller schleifen also insgesamt 111 Funktionsaufrufe notwendig. Wenn ich das Ergebnis der drei Funktionen in Variablen halten könnte (so wie es bei Informix implementiert wurde) sind es halt nur 3 Funktionsaufrufe.

Ich werde als nächstes mal den CREATE FUNCTION - Statements der drei Funktionen mal ein ‘IMMUTABLE’ spendieren … in der Hoffnung, da noch etwas Zeit einzusparen. (Hätte ich eigentlich auch schon früher machen können…)

Interessehalber lasse ich die ganze Sache mal mit den Attributen IMMUTABLE | STABLE und VOLATILE laufen, um den Performance-Unterschied zu messen.

Und wenn alles nichts hilft, werde ich mal versuchen die Funktion mit den Schleifen in Perl zu implementieren … da kann man ja Tabellen in Variablen packen.

Viele Grüße,

Andreas

[quote=“kiaaap”]Hallo Andreas,

super Idee … so wie ich das verstehe löst das aber leider nicht mein Problem.

Ich führe den gravierenden Performance-Unterschied auf das entstehende Skalar in Bezug auf die Funktionsaufrufe zurück.


In diesem Beispiel wären zum Abarbeiten aller schleifen also insgesamt 111 Funktionsaufrufe notwendig. Wenn ich das Ergebnis der drei Funktionen in Variablen halten könnte (so wie es bei Informix implementiert wurde) sind es halt nur 3 Funktionsaufrufe.

Dann erzeuge Arrays aus dem Resultat, also sowas wie:
[code]
test=# CREATE AGGREGATE array_accum (
test(#     sfunc = array_append,
test(#     basetype = anyelement,
test(#     stype = anyarray,
test(#     initcond = '{}'
test(# );
CREATE AGGREGATE
test=*# commit;
COMMIT
test=# select * from foo;
 id |  t
----+-----
  5 | 1,2
  6 | 2,2
  7 | 3,2
  1 | 1
  1 | 1
(5 rows)

test=*# select array_accum(id), array_accum(t) from foo;
 array_accum |       array_accum
-------------+-------------------------
 {5,6,7,1,1} | {"1,2","2,2","3,2",1,1}
(1 row)

In Deiner Funktion rufst Du dann halt nur einmalig die SRF auf und kippst das in Arrays, die Du dann mit den Arrayfunktionen durchlaufen kannst.

Andreas

Hallo Andreas,

ich habe inzwischen meine Funktion mit den FOR-Schleifen nochmal in Perl geschrieben, denn eine externe Bibliothek auf die ich in jedem Durchgang zugreifen muss basiert auch auf Perl … da bot es sich einfach an diesen Weg zu gehen. Ich habe allerdings Deinen letzten Vorschlag mit in unsere Doku aufgenommen, um diese Vorgehensweise bei anderen Matching-Schleifen zu nutzen.

Perl-Version:

   my $keys_a  = hmGetMatchKeys( "A", $a1,  $a2,
                                 "A*", $dna_a1, $dna_a2, $pop_id, $threshold );

   my $keys_b  = hmGetMatchKeys( "B", $b1,  $b2,
                                 "B*", $dna_b1, $dna_b2, $pop_id, $threshold );

   my $keys_dr = hmGetMatchKeys( "DR", $dr1, $dr2,
                                 "DRB1*", $drb11, $drb12, $pop_id, $threshold );

   foreach my $key_a ( @$keys_a ) {
      foreach my $key_b ( @$keys_b ) {
         foreach my $key_dr ( @$keys_dr ) {

Die Performance ist nun auch schon viel erfreulicher. Aus Faktor 25 wurde Faktor 4 . :slight_smile:

Vermutlich hat Informix noch die Nase vorn, weil es besagte Perl-Bibliothek nach dem ersten Aufruf permanent im Speicher hält, während Postgres vermutlich bei jedem Funktionsaufruf die Bibliothek neu lädt.

Das ist bei Informix natürlich ganz toll für die Performance … allerdings muss bei jedem Update der Bibliothek der Server neu gestartet werden (nicht so toll).

Gibts da bei Postgres vielleicht einen eleganteren Weg, eine geladene Bibliothek für längere Zeit (sprich über eine Session hinaus) im Speicher zu halten ?