Funktionsargument fü

am 16.12.2010 11:54:00 von Andreas Tille

[Sorry, wenn die Mail doppelt erscheinen sollte. Nach einiger Verzöger=
ung konnte
ich die Liste nun subscriben und da meine Mail von heute früh nicht im=
Webarchiv
auftaucht, schicke ich sie nun noch einmal.]

Hallo,

irgendwie bekomme ich nicht die Confirmation-Mail zum subscriben, bin als=
o nicht
subscribed und bitte um ein CC bei den Antworten - danke.

Angenommen ich habe eine Tabelle der Art:

CREATE TABLE mytable (field text, value int) ;
INSERT INTO mytable VALUES ('eins', 1);
INSERT INTO mytable VALUES ('zwei', 2);
INSERT INTO mytable VALUES ('drei', 3);

und möchte mehrere Felder in der Art

SELECT * FROM mytable WHERE field IN ('eins', 'drei');

selektieren. (Das ganze Problem ist eigentlich etwas komplexer und
daher soll die letztere Anweisung in einer Funktion untergebracht
werden. Ich habe folgendes versucht:

CREATE OR REPLACE FUNCTION test_set_of_arguments (text) RETURNS SETOF REC=
ORD AS $$
Declare
r RECORD;
query text;
BEGIN
query =3D 'SELECT * FROM mytable WHERE field IN ( || $1 || ) ;';
FOR r IN EXECUTE query LOOP
RETURN NEXT r;
END LOOP;
END; $$ LANGUAGE 'plpgsql';

Wenn ich versuche, diese Funktion z.B. per

SELECT * FROM test_set_of_arguments( '''eins'', ''drei''' ) AS (field tex=
t, value int) ;

aufzurufen, erhalte ich

FEHLER: es gibt keinen Parameter $1
ZEILE 1: SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
^
ANFRAGE: SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
KONTEXT: PL/pgSQL function "test_set_of_arguments" line 7 at FOR-über-=
EXECUTE-Anweisung

Ich habe auch schon andere Möglichkeiten versucht, um den String geschi=
ckt als Argument
zu übergeben - alles schlägt fehl. Die Frage ist nun:

Wie formuliert man die Funktion bzw. den Funktionsaufruf korrekt, um das
oben beschriebene zu erreichen.

Die Bonusfrage dazu wäre noch, wie ich den Aufruf mittels
Python so formulieren kann, daß ich eine Liste als Argument
übergeben kann.

Viele Grüße

Andreas.

--=20
http://fam-tille.de

----- End forwarded message -----

--=20
http://fam-tille.de

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: [pgsql-de-allgemein] Funktionsargument fÃ¼r eine

am 16.12.2010 12:17:00 von Nicolas Barbier

Am 16. Dezember 2010 11:54 schrieb Andreas Tille :

> Â Â Â Â query =3D 'SELECT * FROM mytable WHERE field I=
N ( || $1 || ) ;';

[..]

> FEHLER: Â es gibt keinen Parameter $1
> ZEILE 1: SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
> Â Â Â Â Â Â Â Â Â Â =C2=
=A0 Â Â Â Â Â Â Â Â Â Â =
Â Â Â Â ^
> ANFRAGE: Â SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
> KONTEXT: Â PL/pgSQL function "test_set_of_arguments" line 7 at FOR-=
Ã¼ber-EXECUTE-Anweisung

Das Â»$1Â« soll von PL/PgSQL interpretiert werden (also nicht Teil =
des
SQL Statements werden), es soll also nicht im String eingebettet
werden:

query =3D 'SELECT * FROM mytable WHERE field IN (' || $1 || ') ;';

(Achte auf die zusÃ¤tzlichen AnfÃ¼hrungszeichen.)

Das Ganze is natÃ¼rlich ein bisschen gefÃ¤hrlich bezÃ¼glich SQL=
-Injection
(falls der String vom Benutzer beeinflusst werden kann), und aufgrund
des EXECUTE Statements auch nicht sonderlich performant (das SQL
Statement muss jedes mal erneut gebildet, geparset und geplant
werden).

Nicolas

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: Fu

am 16.12.2010 13:21:55 von Andreas Tille

On Thu, Dec 16, 2010 at 12:17:00PM +0100, Nicolas Barbier wrote:
> > ZEILE 1: SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ^
> > ANFRAGE: =A0SELECT * FROM mytable WHERE field IN ( || $1 || ) ;
> > KONTEXT: =A0PL/pgSQL function "test_set_of_arguments" line 7 at FOR-ü=
ber-EXECUTE-Anweisung
>=20
> Das =BB$1=AB soll von PL/PgSQL interpretiert werden (also nicht Teil de=
s
> SQL Statements werden), es soll also nicht im String eingebettet
> werden:
>=20
> query =3D 'SELECT * FROM mytable WHERE field IN (' || $1 || ') ;';
>=20
> (Achte auf die zusätzlichen Anführungszeichen.)

Ja sicher. Grr, manchmal sieht man's einfach nicht. Danke.
=20
> Das Ganze is natürlich ein bisschen gefährlich bezüglich SQL-Inje=
ction
> (falls der String vom Benutzer beeinflusst werden kann), und aufgrund
> des EXECUTE Statements auch nicht sonderlich performant (das SQL
> Statement muss jedes mal erneut gebildet, geparset und geplant
> werden).

SQL-Injection ist in dem Fall nicht relevant, da die Funktion nur von
einem eigenen Script aufgerufen wird und dort festgelegt wird, was in
der Liste steht.

Hinsichtlich Performance: Welche Alternativen hätte ich in einem solche=
n
Fall? Konkret geht es mir darum, bestimmte Paketeigenschaften aus der
Ultimate Debian Database[1] abzufragen. Die Eigenschaften werden immer
gleichzeitig für einen Satz von Paketnamen benötigt. Ein solcher Sat=
z
kann in der Größenordnung von 10 bis 100 Paketnamen haben. Die Tabel=
le
die abgefragt wird hat die Struktur:

package text,
eigenschaft1 text,
eigenschaft2 text,
...
eigenschaftn text,
version debversion,
architecture text

Ich möchte die eigenschaften 1-n zu allen Paketen der Liste ('package1'=
,
'package2', ..., 'packagen') abfragen aber im Ergebnis brauche ich nur
die höchste version (debversion ist ein sortierbarer Datentyp, für de=
n
MAX() definiert ist) und eine beliebige architecture (in der diese
höchste version existiert). Für jeden Hinweis, wie das auch noch
*performant* geht, bin ich natürlich sehr dankbar, denn die sinnvolle
Aggregierung der in meiner Ausgangsfrage erhaltenen Tabelle liegt mir
noch etwas schwer im Magen.

Viele Grüße

Andreas.

[1] http://wiki.debian.org/UltimateDebianDatabase

--=20
http://fam-tille.de

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: [pgsql-de-allgemein] Funktionsargument fÃ¼r eine

am 16.12.2010 14:05:54 von Nicolas Barbier

Am 16. Dezember 2010 13:21 schrieb Andreas Tille :

> On Thu, Dec 16, 2010 at 12:17:00PM +0100, Nicolas Barbier wrote:
>
>> query =3D 'SELECT * FROM mytable WHERE field IN (' || $1 || ') ;';
>>
>> (Achte auf die zusÃ¤tzlichen AnfÃ¼hrungszeichen.)

[..]

> Hinsichtlich Performance: Welche Alternativen hÃ¤tte ich in einem sol=
chen
> Fall?

Das EXECUTE-Statement kann man mithilfe von Arrays vermeiden:

CREATE OR REPLACE FUNCTION test_set_of_arguments (text[]) RETURNS
SETOF RECORD AS $$
Declare
r RECORD;
BEGIN
FOR r IN SELECT * FROM mytable WHERE field =3D ANY ($1) LOOP
RETURN NEXT r;
END LOOP;
END; $$ LANGUAGE 'plpgsql';

...und..

SELECT * FROM test_set_of_arguments( '{"eins", "drei"}' ) AS (field
text, value int) ;

Falls es mÃ¶glich ist, die Funktion im Form eines einzelnen
SQL-Statements zu schreiben (so wie es jetzt eigentlich ist), ist es
typischerweise besser LANGUAGE 'sql' zu benutzen: solche Funktionen
kÃ¶nnen nÃ¤mlich im aufrufenden SQL-Statement ge-inline-t werden, w=
as
oft zu mehr OptimierungsmÃ¶glichkeiten fÃ¼hrt. (VIEWs haben den gle=
ichen
Vorteil.)

Nicolas

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: Funktionsargument füreine "field IN ($1)" Bedingung

am 20.12.2010 23:47:41 von Peter Eisentraut

On tor, 2010-12-16 at 13:21 +0100, Andreas Tille wrote:
> Ich mÃ¶chte die eigenschaften 1-n zu allen Paketen der Liste ('pack=
age1',
> 'package2', ..., 'packagen') abfragen aber im Ergebnis brauche ich nur
> die hÃ¶chste version (debversion ist ein sortierbarer Datentyp, f=C3=
=BCr den
> MAX() definiert ist) und eine beliebige architecture (in der diese
> hÃ¶chste version existiert). FÃ¼r jeden Hinweis, wie das auch =
noch
> *performant* geht, bin ich natÃ¼rlich sehr dankbar, denn die sinnvo=
lle
> Aggregierung der in meiner Ausgangsfrage erhaltenen Tabelle liegt mir
> noch etwas schwer im Magen.

Ich wÃ¼rde ungefÃ¤hr so anfangen:

SELECT package, version, architecture, maintainer, description FROM
packages WHERE (package, version) IN (SELECT package, max(version) FROM
packages WHERE package IN ('gcc', 'python', 'postgresql') GROUP BY
package);

Das ist allerdings totlangsam, weil die UDD keine passenden Indexe
gesetzt hat. Vielleicht mal einen Dump lokal einspielen und versuchen,
ein paar Indexe zu setzen.

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: Funktionsargument fü

am 21.12.2010 22:44:56 von Andreas Tille

On Tue, Dec 21, 2010 at 12:47:41AM +0200, Peter Eisentraut wrote:
> Ich würde ungefähr so anfangen:
>=20
> SELECT package, version, architecture, maintainer, description FROM
> packages WHERE (package, version) IN (SELECT package, max(version) FROM
> packages WHERE package IN ('gcc', 'python', 'postgresql') GROUP BY
> package);

Ich habe es bereits so gemacht:

SELECT pkg.package, pkg.version, pkg.architecture, (SELECT release FROM r=
eleases WHERE sort =3D MAX(r.sort)) AS release
FROM packages pkg
JOIN (
SELECT pv1.package, MIN(architecture) AS architecture, pv1.version
FROM packages pv1
JOIN (
SELECT package, MAX(version) AS VERSION
FROM packages WHERE package IN ('worldwind', 'xplanet-images',=
'xplanet', 'xulrunner-dev')
GROUP BY package
) mv ON pv1.version =3D mv.version AND pv1.package =3D mv.packag=
e
WHERE pv1.package IN ('worldwind', 'xplanet-images', 'xplanet', 'xulrun=
ner-dev')
GROUP BY pv1.package, pv1.version
) sv1 ON pkg.version =3D sv1.version AND pkg.architecture =3D sv1.arch=
itecture
JOIN releases r ON r.release =3D pkg.release
WHERE pkg.package IN ('worldwind', 'xplanet-images', 'xplanet', 'xul=
runner-dev')
GROUP BY pkg.package, pkg.architecture, pkg.version
;

was pro Paket die maximale Version, das letzte Release und "irgendeine"
Architektur (hier halt das Minimum) liefert. Die Konstruktion

WHERE (a , b) IN (SELECT a, b FROM ...)

kannte ich noch nicht. Bevor ich jetzt verschiedene Tests mache: Was ist
vorzuziehen: JOIN oder WHERE (a, b, c, d) IN (SELECT a, b, c, d) ?
=20
> Das ist allerdings totlangsam, weil die UDD keine passenden Indexe
> gesetzt hat. Vielleicht mal einen Dump lokal einspielen und versuchen,
> ein paar Indexe zu setzen.

Ich habe eine "Test-UDD" und kann das dort mal probieren. Danke für de=
n
Tip

Andreas.=20

--=20
http://fam-tille.de

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Re: Funktionsargument fü

am 22.12.2010 09:32:08 von Andreas Tille