Abfrageergebnisse

Hallo Zusammen

Mit Hilfe von pg_trgm möchte ich mehrere Einträge aus einer Tabelle exportieren.
Und zwar mit der similiarity Funktion.

In etwa so hab ich mir das Vorgestellt:

– similiarity Werte
sim_minimum_strasse := 0.45;

Perform set_limit(sim_minimum_strasse);
SELECT INTO rec street, similarity(norm.strasse, street) AS sim
FROM address WHERE
street % norm.strasse
ORDER BY sim DESC;

Diese Abfrage sollte in vielen Fällen mehrere Ergebnisse liefern.

Frage:
Wie kann man all die Ergebniszeilen in einem Output abspeichern?
Ist dies nur über eine Tabelle möglich?

Beispiel Ergebnis:
street sim
Teststrasse 1
Teststrase 0.5
etc.

Gruss

Problem gelöst

Schön. Schöner wäre, Du würdest uns an Deiner Lösung teilhaben lassen.

Andreas

Ja ich dachte ich hätte das Problem gelöst.

Doch nun steh ich wieder am Anfang und komm nicht weiter.

Irgendwie müsste das doch mit record funktionieren!!!

Das ist bei Datenbanken der übliche Weg …

Andreas

Ja, man kann Record-Variablen verwenden.


Andreas

So in etwa möchte ich das bewerkstelligen:

CREATE OR REPLACE FUNCTION tm_match(“input” character varying)
RETURNS setof records AS
$BODY$
DECLARE
rec record
Begin
PERFORM set_limit(sim_minimum_country);
SELECT INTO rec DISTINCT country AS VARCHAR, similarity(norm.Land, country) AS sim
FROM address
WHERE
country % norm.Ort
ORDER BY sim DESC;

Die Frage ist nur, wie ich es hinbekomme dass er mir alle Ergebnisse in rec speichert.

Brauch ich dafür eine Loop und dann mit RETURN NEXT rec?
Oder versteh ich da was falsch?

Ja. Die Doku enthält sogar Beispiele dafür. Du kannst auch RETURN QUERY verwenden. Eine Record-Variable enthält nur eine Zeile einer Tabelle, nicht die komplette Tabelle.

Andreas

Ich hab es mal auf diese Weise versucht:

FOR rec IN SELECT street FROM address WHERE street = norm.strasse LOOP
RETURN NEXT rec;
END LOOP;
RETURN;

Die Abfrage wird durchgeführt, doch bei RETURN NEXT rec kommt folgende Fehlermeldung:
FEHLER: Funktion mit Mengenergebnis in einem Zusammenhang aufgerufen, der keine Mengenergebnisse verarbeiten kann.
Verste ich irgendwie nicht ganz.

In was für einem Zusammenhang ruftst das denn auf? Vermutlich nicht als select * from my_srf(), oder?

Andreas, der grade merkt, daß 8.4 da nicht meckert …

test=*> create table foo (a int);
CREATE TABLE
test=*> copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 4
>> 5
>> 6
>> \.
test=*> create or replace function get_foo(out x int, out y int) returns setof record as $$declare r record; begin for r in select a from foo loop x:=r.a; y:= r.a * 10; return next; end loop; end; $$language plpgsql;
CREATE FUNCTION
test=*> select get_foo();
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "get_foo" line 1 at return next
test=!>

das war 8.1.

versus 8.4:

test=*# select * from foo;
 a
---
 4
 5
 6
(3 rows)

test=*# select get_foo();
 get_foo
---------
 (4,40)
 (5,50)
 (6,60)
(3 rows)

Ich hab versucht die Funktion im Debugger durchlaufen zu lassen. Dabei kam der Fehler.
Hier ist die komplette Funktion:

CREATE OR REPLACE FUNCTION tm_match2(“input” character varying, out x character varying)
RETURNS SETOF record AS
$BODY$
DECLARE

NORM norm;
rec RECORD;

BEGIN

– Normalisierung
NORM := tm_normalize(input);


– Free Text Search

FOR rec IN SELECT street FROM address WHERE street = norm.strasse LOOP

RETURN NEXT;

END LOOP;

RETURN;

END
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE
COST 100;
ALTER FUNCTION tm_match2(character varying) OWNER TO postgres;

Seufz. Wie rufst Du das auf?

Andreas

Aufruf via:
SELECT DISTINCT * FROM tm_match2(‘Inputstring’) AS (street VARCHAR);

Das funktioniert wunderbar.

Doch wenn ich versuche die selbe Funktion (tm_match2) im Debugger von EnterpriseDB durchlaufen zu lassen,
kommt bei ‘RETURN NEXT rec’ die erwähnte Fehlermeldung.
Das versteh ich nicht ganz.

Was genau soll denn hier zurückgeliefert werden?

Sorry mein Fehler.

Der Befehl lautet ‘RETURN NEXT rec;’

Das Problem ist folgendermassen:
Wenn via Aufruf
SELECT DISTINCT * FROM tm_match2(‘Inputstring’) AS (street VARCHAR);
die Funktion tm_match2 aufgerufen wird, erhalt ich die gewünschten Resultate.

Wenn ich jedoch via Debugger step by step versuche die Funktion durchzuführen, kommt beim Befehl ‘RETURN NEXT rec’ die beschriebene Fehlermeldung.

Ist beim Debugger-Aufruf auch das “AS (…)” dabei?

Ja.

Der aktuelle Befehl in der Funktion lautet:

PERFORM set_limit(sim_minimum_street);
FOR rec IN SELECT street, similarity(norm.strasse, street) AS sim
FROM address WHERE street % norm.strasse
ORDER BY sim DESC
LIMIT 5
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;

Das Problem ist, dass ich schon länger Schwierigkeiten mit dem DBEnterprise Debugger habe (Siehe Problem mit Pl/pgSQL Debugger).

Aber ich hätte sonst noch eine weitere völlig andere Frage, bei der ihr mir weiterhelfen könntet.
Und zwar will ich für meine Adresstabelle für jede Spalte einen GIN und einen GIST-Index kreieren. Um die Performace für unterschiedliche Abfragen zu testen.

CREATE INDEX address_city_gin
ON address
USING gin
(city gin_trgm_ops);

Die Frage ist bezüglich den Operatorklassen gin_trgm_ops. Was genau bewirkt die?
Und gibt es noch andere mögliche Operatorenklassen?
Hab leider nichts gefunden in der Doku.