Variable Wertzuweisung aus record: work-around 1

Hallo,

wir haben gelernt, dass eine Zuweisung

value:=rec.<colname>;

wobei den Inhalt einer Textvariablen für den Spaltennamen bezeichnet rec vom Typ RECORD oder %rowtype ist,
nicht möglich ist.

Eine Möglichkeit, diese Zuweisung trotzdem zu erreichen, wird unten dargestellt. Leider habe ich hier bisher nur Tabellen mit recht einfachen Spaltentypen, so dass ich mir nicht sicher bin, ob das in jedem Fall richtig funktioniert.

Deshalb bitte ich, diese Funktion zu testen (falls jemand die Zeit finden sollte) und Fehler hier zu posten. Kommentare jeder Art sind natürlich auch sehr willkommen.

Vielen Dank im Voraus und beste Grüße

Gerd


Ablauf des Tests:
die drei Funktionen installieren und Testabfragen gemäß

select get_column_value(
  get_table_oid('mytable'),
  get_row_as_text('mytable', ''), 
  'mycolumnname' );

mit entsprechenden Tabellennamen mytable und Spaltennamen für mycolumnname durchführen und Resultat überprüfen.

Hauptfunktion get_column_value:

CREATE OR REPLACE FUNCTION get_column_value(tableoid oid, valrecstr text, colname text)
  RETURNS text AS
$BODY$DECLARE
  i integer; j integer; poscomma integer; posquote integer; inquote bool; 
  help text; helpstr2 text; aChar text;
  attrid integer;
  rec1 record; 
  _sql text;
BEGIN
  RAISE NOTICE 'get_column_value parameters tableoid: %, row: %, colname: %', tableoid, valrecstr, colname;

  -- find out the position of column within the record string
  _sql := 'SELECT attname, attrelid, attnum FROM pg_attribute';
  _sql := _sql || ' WHERE attname=' || quote_literal(colname) || ' AND attrelid=' || tableoid || ';';
  attrid := 0;
  FOR rec1 IN EXECUTE _sql
  LOOP attrid := rec1.attnum; EXIT; END LOOP;
  IF (attrid=0) THEN 
    RAISE EXCEPTION 'get_column_value: No such column % in table with oid %', colname, tableoid;
    RETURN '';
  END IF;

  -- parse the given record string
  help := valrecstr;
  -- delete first and last char '(' and ')'
  help := substr(help, 2, length(help)-2);

  i:=0; 
  WHILE ((help<>'') AND (i<attrid> 0) AND (posquote < poscomma)) THEN
      -- take the string until the next quote
      j := posquote;
      inquote := true; 
      WHILE ( j<length(help) )
      LOOP
        j:=j+1;
        aChar := substr(help, j, 1); 
        IF ((NOT inquote) AND (aChar=',')) THEN exit; END IF;
        IF (aChar='"') THEN inquote:=NOT inquote; END IF;
      END LOOP;
      IF (aChar<>',') THEN j:=j+1; END IF;
      helpstr2 := substr(help, posquote+1, j-posquote-2);
      help := substr(help, j+1, length(help));
      RAISE NOTICE 'LOOP %, result #%#, new help #%#', i, helpstr2, help;
    ELSE
      -- take the string until poscomma
      helpstr2 := substr(help, 1, poscomma-1);
      help := substr(help, poscomma+1, length(help));
      RAISE NOTICE 'LOOP %, result #%#, new help #%#', i, helpstr2, help;
    END IF; 
  END LOOP;

  IF (i<>attrid) THEN
    RAISE EXCEPTION 'get_column_value: bad record string "%", could not find entry #% for column %', valrecstr, attrid, colname;
    RETURN '';
  END IF;
 
  RAISE NOTICE 'get_column_value result: %', helpstr2;  
  RETURN helpstr2;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
COMMENT ON FUNCTION get_column_value(tableoid oid, valrecstr text, colname text) IS 'Returns the value in column colname from the (as text) given row valrecstr. Note: The given tableoid must be the oid of the table from which the given row is extracted. ';

Hilfsfunktion zur Ermittlung der oid einer Tabelle:

CREATE OR REPLACE FUNCTION get_table_oid(tablename text)
  RETURNS oid AS
$BODY$DECLARE
  _sql text;
  scheme text;
  tabelle text;
  i integer;
  id oid;
  id2 oid;
  therow RECORD;
BEGIN
  -- split given tablename into schema and table
  i := strpos(tablename, '.');
  IF (i=0) THEN scheme := 'public'; tabelle := trim(both from tablename); 
  ELSE
    scheme := trim(both from substr(tablename, 1, i-1));
    tabelle := trim(both from substr(tablename, i+1, length(tablename)));
  END IF;
  RAISE NOTICE 'get_table_oid called for schema % and table %', scheme, tabelle;

  -- find the oid of schema
  _sql := 'SELECT nspname, oid FROM pg_namespace WHERE nspname=' || quote_literal(scheme) || ';';
  id := 0; 
  FOR therow IN EXECUTE _sql
  LOOP id := therow.oid; EXIT; END LOOP;
  IF (id = 0) THEN
    RAISE EXCEPTION 'get_table_oid: no such schema %', scheme;
    RETURN 0;
  END IF;

  -- find the oid for table in scheme with oid id
  id2 := 0;
  _sql := 'SELECT relname, relnamespace, oid FROM pg_class WHERE relname=' || quote_literal(tabelle) || ' AND relnamespace=' || id || ';';
  FOR therow IN EXECUTE _sql
  LOOP id2 := therow.oid; EXIT; END LOOP;
  IF (id2 = 0) THEN
    RAISE EXCEPTION 'get_table_oid: no such table % in schema %', tabelle, scheme;
    RETURN 0;
  END IF;
  RETURN id2;  
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
COMMENT ON FUNCTION get_table_oid(tablename text) IS 'Returns the oid of the table given by full name (scheme.table). If no scheme is specified, "public" will be taken.';

Hilfsfunktion, die eine Abfrage SELECT * FROM… durchführt und das Ergebnis als Zeichenkette liefert:

CREATE OR REPLACE FUNCTION get_row_as_text(tablename text, whereclause text)
  RETURNS text AS
$BODY$DECLARE
  _sql text;
  result text;
  rec record;
BEGIN
  _sql := 'SELECT * FROM ' || tablename;
  IF (whereclause<>'') THEN
    _sql := _sql || ' WHERE ' || whereclause;
  END IF;
  _sql := _sql || ';';
  result := '';
  FOR rec IN EXECUTE _sql
  LOOP result := rec; EXIT; END LOOP;
  RETURN result;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
COMMENT ON FUNCTION get_row_as_text(tablename text, whereclause text) IS 'Helper function for testing only. Returns the (internal) text for the first row within the given table satisfying the whereclause.';

Vielleicht teste ich mal, wenn ich später Zeit finde.

Etwas anderes interessiert mich: wenn du schon so umfangreichen Code schreibst … wie schnell bzw. wie langsam wird das ganze?
Hast du mit deinen einfachen Tabellen ein paar Messwerte zur Verfügung?

Ich könnte mir vorstellen, das die Sache sehr langsam wird …

Hallo,

netter ungetesteter Code mit netten Syntaxfehlern. Man findet sie schon beim lesen.

Die hier beschrieben Funktion hat auch keine Ähnlichkeit mit der in dem thread http://www.pg-forum.de/viewtopic.php?t=350&highlight= beschriebene Funktion, worauf Du dich offensichtlich bei deinem “gelernten” bezieht.

Der Aufwand für dieses Funktion ist auch unverständlich. Da reicht ja fast ein 4-Zeiler, ohne zu denken.

CREATE OR REPLACE FUNCTION get_column_value (name_table TEXT, name_column TEXT, where_condition TEXT)
  RETURNS TEXT

AS $BODY$

  DECLARE

    result              RECORD;
    result_text         TEXT;

  BEGIN

    FOR result IN EXECUTE 'SELECT ' || name_column || ' FROM ' || name_table || ' WHERE ' || where_condition || ' LIMIT 1' LOOP
    END LOOP;

    result_text := result;

    RETURN (substr(result_text, 2, char_length (result_text) - 2));

  END;

$BODY$

LANGUAGE plpgsql;

mfg

fristo

Hallo fristo,

Offensichtlich ist beim Kopieren der Funktion get_column_value() irgendetwas schiefgegangen. Danke für Deine Aufmerksamkeit, die korrigierte Funktion findet sich weiter unten.

Hallo ads,

Würde mich sehr freuen. Ich kann mir durchaus vorstellen, dass bei koplexeren Spaltentypen (Punkt, Vektor, …) die Funktion so nicht richtig funktioniert.

Das glaube ich auch: zuerst die interne Umwandlung der ganzen Zeile in eine Zeichenkette, dann das Parsen der Zeichenkette…

Ich würde mir auch was Schnelleres wünschen. Aber es ist ja nicht einmal möglich, einen RECORD als Funktionsparameter zu übergeben (und damit das Finden des Wertes vollständig in C abzuhandeln).
Am schönsten wäre ja, wenn eine direkte Zuweisung von record zu array möglich wäre … Zum Glück brauche ich das Ding eher selten, aber dann eben für jede beliebige Tabelle.
Und ich glaube nicht, dass irgendwelche Optimierungsversuche innerhalb der Funktion wesentliche Verbesserungen bewirken könnten. Wenns dann akute Zeitprobleme gibt, muss ein anderer Ansatz her (und da fällt mir jetzt nur ein: die eigentlichen Triggerfunktionen für jede Tabelle extra schreiben oder Deinen Vorschlag nehmen und das ganze in C ausführen).

Beste Grüße

Gerd

Und hier die jetzt hoffentlich richtige Funktion get_column_value (offensichtlich muss man zum Posten von CODE hier HTML deaktivieren)

CREATE OR REPLACE FUNCTION get_column_value&#40]

Hallo ads,

Ich hab’s gerade mal ausprobiert. Für so langsam hätte ich die Funktion nun doch nicht gehalten: bei einer Länge der Textdarstellung des record von etwa 100 Zeichen braucht die Funktion, um den Wert der letzten Spalte zu ermitteln, mindestens 1000 (Eintausend) mal solange wie die direkte Zuweisung. - Also nur als Notbehelf zu gebrauchen.
==> Ich werde nicht umhin kommen, meine Triggerfunktion(en) in C zu programmieren.

Beste Dank nochmal für eure Unterstützung und schöne Grüße

Gerd