1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

CREATE FUNCTION, PL/pgSQL vs. SQL

Dieses Thema im Forum "PostgreSQL" wurde erstellt von ukulele, 29 Mai 2019.

  1. ukulele

    ukulele Datenbank-Guru

    Nabend,

    Ich übersetze grade eine Funktion von MS nach PG. Dabei ist mir die zunächst etwas merkwürdig erscheinende Unterscheidung zwischen LANGUAGE plpgsql und LANGUAGE sql aufgefallen. Was ist hier wann sinnvoll und warum wird das so merkwürdig über AS $$ am Anfang definiert und am Ende spezifiziert? Ist mir irgendwie nicht klar.

    In der Funktion meckert er mir SET vCounter = vCounter + 1 an, was ist für plpgsql falsch daran?
    Code:
    CREATE OR REPLACE FUNCTION "public"."function_escape_html"(vData VARCHAR(10485760))
    RETURNS VARCHAR(10485760) AS $$
    DECLARE   vCounter SMALLINT = 0;
    DECLARE   vTag VARCHAR(100);
    DECLARE   vPos INT;
    DECLARE   vLength INT;
    BEGIN
       CREATE TEMP TABLE vTable(
               counter SMALLINT NOT NULL,
               tag VARCHAR(100) NOT NULL
               );
    
       WHILE   vData LIKE '%</%>%'
       AND       vCounter <= 100
       LOOP
           SET       vCounter = vCounter + 1
    
           SET       vPos = strpos(vData,'<')
           SET       vLength = strpos(right(vData,length(vData)-strpos(vData,'<')),'>')-1
           SET       vTag = left(right(vData,length(vData)-vPos),vLength)
    
           IF       vTag NOT LIKE '%<%'
           AND       vTag NOT LIKE '%>%'
           AND   (   vData LIKE '%<' + vTag + '>%</' + vTag + '>%'
           OR       left(vTag,1) = '/' )
           BEGIN
               IF       left(vTag,1) != '/'
               BEGIN
                   INSERT INTO vTable(counter,tag)
                   SELECT   vCounter,vTag
               END
               ELSE
               BEGIN
                   DELETE
                   FROM   vTable
                   WHERE   tag = right(vTag,length(vTag)-1)
                   AND       counter = (   SELECT   max(counter)
                                       FROM   vTable
                                       WHERE   tag = right(vTag,length(vTag)-1) )
               END
    
               SET       vData = left(vData,vPos-1) + CHAR(27) + vTag + CHAR(127) + right(vData,length(vData)-vPos-vLength-1)
           END
           ELSE
           BEGIN
               SET       vData = left(vData,vPos-1) + '&lt;' + right(vData,length(vData)-vPos)
           END
       END   LOOP
    
       SET       vData = replace(replace(replace(vData,'>','&gt;'),CHAR(27),'<'),CHAR(127),'>')
    
       RETURN vData
    END;
    $$ LANGUAGE plpgsql;
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Zu Deinen Fragen:

    • plpgsql ist eine Sprache, die z.B. auch Schleifen enthält. SQL kennt sowas nicht. Du kannst also Deine Funktion nicht als SQL definieren.
    • das $$ separiert den Quelltext des Funktion. Früher war das mal ', mit dem Nebeneffekt, daß man ' dann nicht mehr direkt im Funktionsbody verwenden konnt. Das zog lange Quoting-Orgien nach sich. Du kannst auch z.B. $BLA$ oder $meine_coole_Funktion$ um den Body herum verwenden.
    • mit SET kannst Du dynamisch Konfig-Parametern ändern, das willst Du aber gar nicht, daher einfach weglassen. Nicht weglassen dafür aber ; am Ende. Kurzdemo:

      Code:
      test=*# create or replace function bla() returns text as $$ declare my_int_var int; my_text_var text; begin my_int_var = 0; my_text_var = ''; while my_int_var < 5 loop my_int_var=my_int_var+1; my_text_var = my_text_var || my_int_var::text; end loop; return my_text_var; end; $$ language plpgsql;  
      CREATE FUNCTION
      test=*# select * from bla();
        bla  
      -------
       12345
      (1 row)
      
      test=*# create or replace function bla() returns text as $$ declare my_int_var int; my_text_var text; begin set my_int_var = 0; my_text_var = ''; while my_int_var < 5 loop my_int_var=my_int_var+1; my_text_var = my_text_var || my_int_var::text; end loop; return my_text_var; end; $$ language plpgsql;  
      CREATE FUNCTION
      test=*# select * from bla();
      FEHLER:  unbekannter Konfigurationsparameter »my_int_var«
      CONTEXT:  SQL-Anweisung »SET my_int_var = 0«
      PL/pgSQL-Funktion bla() Zeile 1 bei SET
      test=*#
      

    Hope that helps, Andreas
     
  3. castorp

    castorp Datenbank-Guru

    Weil in PL/pgSQL - wie im Handbuch erklärt - SET nicht für die Zuweisung von Werten verwendet wird.

    Ausserdem reicht ein DECLARE Block am Anfang, das muss man nicht jedesmal neu schreiben.

    Die ganze Funktion sieht aber unnötig kompliziert zu sein. Was genau soll die machen? Wenn ich das richtig sehe, wird letztendlich < durch &lt; ersetzt und > durch &gt;

    Die TEMP Tabelle scheint mir auch überflüssig zu sein, das könnte man auch in einem Array zwischenspeichern oder sogar einem JSON Wert als key/value Paare. Dafür extra eine Tabelle anzulegen scheint mir "overkill" zu sein.
     
    Zuletzt bearbeitet: 29 Mai 2019
  4. ukulele

    ukulele Datenbank-Guru

    := hatte ich auch gesehen und versucht, habe wohl vergessen gleichzeitig mit ; abzuschließen, mein Fehler. Generell bin ich es von MSSQL gewohnt SET auch für das setzen von Variablen zu verwenden, das hat mich schon irritiert.

    Der Unterschied zwischen SQL und PL/pgSQL ist doch recht komplex, aber scheinbar kann ich nur mit PL/pgSQL Variablen verwenden wie von MSSQL gewohnt, das irritiert mich tatsächlich mehr.
    Difference between language sql and language plpgsql in PostgreSQL functions
    PostgreSQL equivalent of MySQL query variables?

    Das hatte ich auch schon in MSSQL getan, dem habe ich in PG Abhilfe geschaffen.

    Das ist so nicht richtig. Die Funtion durchläuft Text und ersetzt < und > grundsätzlich wenn sie nicht zu einem HTML-Tag gehören (ausgenommen nicht geschlossene Tags). Aus '<asdf>A < B<br>C</asdf>' wird '<asdf>A &lt; B&lt;br&gt;C</asdf>'.

    Ja in MSSQL habe ich eine Tabellen-Variable, schnelles googlen hat daraus eine Temp-Tabelle gemacht. Arrays können aber nur Werte des selben Typs aufnehmen, richtig?
    Code:
    CREATE OR REPLACE FUNCTION "public"."function_escape_html"(vData VARCHAR(10485760))
    RETURNS VARCHAR(10485760) AS $$
    DECLARE   vCounter SMALLINT := 0;
           vTag VARCHAR(100);
           vPos INT;
           vLength INT;
    BEGIN
       CREATE TEMP TABLE vTable(
               counter SMALLINT NOT NULL,
               tag VARCHAR(100) NOT NULL
               );
    
       WHILE   vData LIKE '%</%>%'
       AND       vCounter <= 100
       LOOP
           vCounter := vCounter + 1;
    
           vPos := strpos(vData,'<');
           vLength := strpos(right(vData,length(vData)-strpos(vData,'<')),'>')-1;
           vTag := left(right(vData,length(vData)-vPos),vLength);
    
           IF       vTag NOT LIKE '%<%'
           AND       vTag NOT LIKE '%>%'
           AND   (   vData LIKE '%<' || vTag || '>%</' || vTag || '>%'
           OR       left(vTag,1) = '/' )
           THEN
               IF       left(vTag,1) != '/'
               THEN
                   INSERT INTO vTable(counter,tag)
                   SELECT   vCounter,vTag;
               ELSE
                   DELETE
                   FROM   vTable
                   WHERE   tag = right(vTag,length(vTag)-1)
                   AND       counter = (   SELECT   max(counter)
                                       FROM   vTable
                                       WHERE   tag = right(vTag,length(vTag)-1) );
               END IF;
    
               vData := left(vData,vPos-1) || chr(27) || vTag || chr(127) || right(vData,length(vData)-vPos-vLength-1);
           ELSE
               vData := left(vData,vPos-1) || '&lt;' || right(vData,length(vData)-vPos);
           END IF;
       END   LOOP;
    
       vData := replace(replace(replace(vData,'>','&gt;'),chr(27),'<'),chr(127),'>');
    
       DROP TABLE vTable;
    
       RETURN vData;
    END;
    $$ LANGUAGE plpgsql;
    
     
  5. castorp

    castorp Datenbank-Guru

    Praktisch alle Datenbanksysteme (z.B. Oracle, DB2, Firebird, MySQL und eben auch Postgres) machen eine klare Unterscheidung zwischen der Abfragesprache (SQL) und der Sprache (oder Sprachen) die zum Schreiben von prozeduralem Code verwendet wird. Nur SQL Server ist halt komplett anders als alle anderen.

    Da muss ich ein wenig nachdenken, aber ich denke das müsste mit Regulären Ausdrücken machbar sein (etwas was es in SQL Server gar nicht gibt).
     
  6. castorp

    castorp Datenbank-Guru

    Nach ein wenig überlegen, denke ich das geht ganz ohne Schleifen und temporäre Tabellen.

    Der Fall von "einfachen" Größer/Kleiner Zeichen ist einfach, den kann man mit regexp_replace erledigen. Das escapen aller Tags die nicht richtig geschlossen sind, ist etwas trickreicher, aber ich denke mit der folgenden Funktion klappt es - zumindest Dein Beispiel wird richtig ersetzt:

    Code:
    CREATE OR REPLACE FUNCTION escape_html(p_input text)
      RETURNS text AS
    $$
    DECLARE  
      l_clean_value text;
      l_to_replace text[];
      l_tag text;
    BEGIN
    
      -- ersetze alle "einfach" < und > zuerst
      l_clean_value := regexp_replace(p_input, '(\s+)<(\s+)', '\1&lt;\2', 'g');
      l_clean_value := regexp_replace(l_clean_value, '(\s+)>(\s+)', '\1&gt;\2', 'g');
    
      -- finde alle tags die nicht geschlossen werden
      select array_agg(tag)
         into l_to_replace
      from (
        select translate(x2.open_tag, '<>', '') as tag
        from regexp_matches(l_clean_value, '<\w+>', 'g') as x1(open_tags),
             unnest(x1.open_tags) as x2(open_tag)
        except
        select translate(x3.close_tag, '</>', '') as close_tag
        from regexp_matches(l_clean_value, '</\w+>', 'g') as x2(close_tags),
             unnest(x2.close_tags) as x3(close_tag)
      ) x;
     
      -- in dem array l_to_replace stehen jetzt alle tags die kein schliessendes Tag haben
      -- diese können direkt ersetzt werden
      foreach l_tag in array l_to_replace loop
        l_clean_value := replace(l_clean_value, concat('<', l_tag, '>'), concat('&lt;', l_tag, '&gt;'));
      end loop;
      return l_clean_value;
    END;
    $$
    LANGUAGE plpgsql;
    

    Wie funktioniert das Finden im Detail?

    Die Funktion regexp_matches('<asdf>A < B<br>C</asdf>', '<\w+>', 'g') liefert alle "echten" Tags (also Zeichenketten die zwischen < und > stehen). Das Ergebnis der Funkion ist ein Resultat bei dem in jeder Zeile ein Array mit gefundenen Tags stehen, wenn man dann jedes Array wieder "auflöst" bekommt man eine Liste aller Tags:

    Die folgende Abfrage
    Code:
    select x2.*
    from regexp_matches('<asdf>A < B<br>C</asdf>', '<\w+>', 'g') as x1(open_tags),
         unnest(x1.open_tags) as x2(open_tag);
    liefert:
    Code:
    open_tag
    --------
    <asdf>  
    <br>   
    Das translate() schmeisst dann einfach die spitzen Klammer weg.

    Die zweite Abfrage liefert dann eine Liste alle schliessenden Tags. Diese Abfrage
    Code:
    select x3.*
    from regexp_matches('<asdf>A < B<br>C</asdf>', '</\w+>', 'g') as x2(close_tags),
         unnest(x2.close_tags) as x3(close_tag) 
    liefert also
    Code:
    close_tag
    ---------
    </asdf>  
    
    Das translate entfernt dann wieder die spitzen Klammern und den Schrägstrich, so dass beide Teilabfragen nur den "nackten" Text der Tags liefern. EXCEPT lässt dann die übrig, die in der ersten Liste sind, aber nicht in der zweiten - damit haben wir alle Tags die nicht sauber geschlossen werden und deren spitze Klammern ersetzt werden müssen. Bei dem gegebenen Beispiel bleibt als Ergebnis also br übrig.

    Die Abfrage speichert diese Liste in ein Array (select ... into l_to_replace ...). Über dieses Array wird iteriert und entsprechend im String ersetzt.
     
    Walter gefällt das.
  7. ukulele

    ukulele Datenbank-Guru

    Reguläre Ausdrücke gibt es schon aber auch dort ganz anders, daher ist natürlich auch dein Code echt ungewohnt für mich das wird eine weile dauern bis ich so fitt bin in der Syntax :)

    Ich überlege schon welche Funktionalität ich zuvor noch in der Funktion hatte aufgrund dessen ich eventuell eine Schleife gebaut habe. Auch war mein Beispiel ja sehr flach aber die Daten die ich verarbeite sind zum Glück ebenfalls recht simpel strukturiert, damit kann ich das mal testen.
     
  8. castorp

    castorp Datenbank-Guru

    Huch? Seit wann denn? Ich dachte das Einzige was SQL Server unterstützt, ist die komische Syntax bei LIKE wo man "character ranges" angeben kann - aber das sind keine richtigen (bzw. vollständigen) regulären Ausdrücke. Und Suchen & Ersetzen gibt es meines Wissens nach zumindest in SQL Server 2016 nicht (2017 habe ich noch nicht ausprobiert)

    Um den Code zu verstehen (vor allem den zweiten Teil) ist es vermutlich am einfachsten Du lässt die Statements einzeln laufen und schaust Dir an, was die machen.
     
  9. ukulele

    ukulele Datenbank-Guru

    Ein schneller Test hat nicht geklappt. Hier mal die echte Struktur der meisten Datensätze (es gibt leider ein paar Merkwürdigkeiten):
    Code:
    <table><tr><td><nobr>Daten1</nobr></td><td>
    <nobr>Daten2</nobr>
    </td></tr>
    
    
    <tr><td>Daten3</td></tr>
    <tr><td>Daten4</td></tr>
    <tr><td>Daten5</td></tr>
    <tr><td></td></tr>
    <tr><td>DatenText</td></tr>
    </table>
    Deine Funktion schmeißt ein
    ERROR: FEHLER: FOREACH-Ausdruck darf nicht NULL sein CONTEXT: PL/pgSQL-Funktion escape_html_castorp(text) Zeile 27 bei FOREACH über Array
     
  10. ukulele

    ukulele Datenbank-Guru

    Ja es ist nicht so umfangreich das stimmt, kann aber auch nicht so wenig. Man kann Zeichen negieren, es gibt Platzhalter und neben Ranges auch noch "Zeichenlisten" oder wie man das nennen soll. Damit kann man ein bischen was machen.
     
  11. castorp

    castorp Datenbank-Guru

    Das mit dem NULL kann man leicht durch ein
    Code:
    select coalesce(array_agg(tag), '{}') ...
    beheben. Dann funktioniert es problemlos mit Deinem Beispieltext

    Postgres 11 | db<>fiddle
     
  12. ukulele

    ukulele Datenbank-Guru

    Jo läuft zumindest sauber und schnell damit. Meine Funtkion hingegen kackt bei vielen Datensätzen ab, allein schon wegen der temporären Tabelle.

    Leider ist die Funktion nur ein winziges Puzzleteil, das könnte ewig dauern alles in PG lauffähig zu bekommen. Ich glaube ich versuche mich lieber erstmal an einer Fortsetzung und belasse einiges in MSSQL.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden