CREATE FUNCTION, PL/pgSQL vs. SQL

[ukulele]

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?

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;

 

[akretschmer]

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:
  
  

  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

 

[castorp]

In der Funktion meckert er mir SET vCounter = vCounter + 1 an, was ist für plpgsql falsch daran?

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.

 

[ukulele]

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

:= 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?

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

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

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;

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>'.

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.

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?

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;

 

[castorp]

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.

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.

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>'.

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).

 

[castorp]

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:

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

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:

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

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

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.

 

[ukulele]

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.

 

[castorp]

Reguläre Ausdrücke gibt es schon aber auch dort ganz anders

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.

 

[ukulele]

Ein schneller Test hat nicht geklappt. Hier mal die echte Struktur der meisten Datensätze (es gibt leider ein paar Merkwürdigkeiten):

<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

 

[ukulele]

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.

 

[castorp]

Das mit dem NULL kann man leicht durch ein

select coalesce(array_agg(tag), '{}') ...

beheben. Dann funktioniert es problemlos mit Deinem Beispieltext

Postgres 11 | db<>fiddle

 

[ukulele]

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.