Pokud si vzpomínáte na předchozí seriál Úvod do PL/pgSQL, pak si jistě vybavíte příklad agregační funkce html_list, která vracela seznam položek v sloupci formátovaný jako html seznam. Totožnou fonkci bude mít funkce html_list. Jejími parametry bude SQL dotaz, sloupec, jehož položky chceme uložit do seznamu, maximální počet zpracovávaných řádek a velikost bloku alokované paměti.
Pro náš příklad si vystačíme s pěti základními funkcemi: SPI_connect, SPI_finish, SPI_exec, SPI_fnumber a SPI_getvalue. S touto množinou funkcí dokážeme pouze číst výsledek dotazu, nicméně je to v tuto chvíli dostačující. Funkce SPI_connect inicializuje rozhraní a musí proto být volána jako první. Naproti tomu SPI_finish uvolňuje veškerou paměť alokovanou funkcí palloc po dobu běhu funkce a musí být proto volána jako poslední. Pokud naše funkce vrací ukazatel na paměť alokovanou v proceduře (u všech hodnot předávaných pdkazem), nesmí se paměť alokovat příkazem palloc. To z toho důvodu, že ještě než stihneme hodnotu předat volající funkci, SPI_finish stihne pamět s touto hodnotou uvolnit. Tento „neřešitelný“problém řeší alternativní procedury pro práci s pamětí: SPI_palloc a SPI_replloc. Volání SPI_finish nemá vliv na paměť alokovanou těmito funkcemi. Pokud dojde k ukončení funkce voláním elog (ERROR, …), potom je SPI_finish voláno automaticky.
Pokud dojde k chybě, pak SPI funkce vrací obvykle záporné číslo. Po aktivaci rozhraní můžeme funkcí SPI_exec nechat provést SQL příkaz. Druhým parametrem funkce SPI_exec je maximální počet vrácených záznamů. Pokud je nula, pak se použijí všechny záznamy. Po bezchybném provedení dotazu můžeme používat globální proměnnou SPI_processed obsahující počet vrácených záznamů a globální ukazatel SPI_typtable.
Ukazatel SPI_tuptable je ukazatel na strukturu obsahující jednak pole s vrácenými řádkami, jednak odkaz na popis struktury vrácených dat. Pro získání hodnoty ve sloupečku potřebujeme vědět pořadí sloupce v dotazu. Pořadí vrací funkce SPI_fnumber, např.
sloupec = SPI_fnumber(SPI_tuptable->tupdesc, "jmeno"); if (sloupec <= 0) elog (ERROR, "SPI_ERROR_NOATTRIBUTE");
Přístup k n-tému sloupečku x-tého řádku zajišťuje funkce SPI_getvalue. Je nutné si uvědomovat, že všechny hodnoty vrácené SPI_getvalue jsou textové. Získání první hodnoty prvního řádku provedeme příkazem:
printf ("%s", SPI_getvalue(
SPI_tuptable->vals[0],
SPI_tuptable->tupdesc, 1));
Abych urychlil provádění funkce, nealokuji paměť při každém přidání řetězce, ale po blocích – stránkách, jejichž velikost si sami určíme.
text *sts_strcatex2 (char *str, text *txt,
long *zapsano, long *zbyva, long stranka)
{
long len = strlen (str);
if (len > stranka)
elog (ERROR, "Retezec je vetsi nez velikost stranky");
if (*zbyva < len)
{
long nsize = (((*zapsano + len) / stranka) + 1) * stranka;
txt = (text *) SPI_repalloc (txt, nsize + VARHDRSZ);
*zbyva = nsize - *zapsano;
}
memcpy ((char*)VARDATA (txt) + *zapsano, str, len);
*zapsano += len; VARATT_SIZEP (txt) = *zapsano + VARHDRSZ;
*zbyva -= len;
return (txt);
}
Paměť alokuji voláním funkce SPI_repalloc a nikoliv repalloc z již zmíněného důvodu – pokud bych použil palloc, pak fce SPI_finish by tuto paměť uvolnila. Následné volání PG_RETURN_TEXT_P by vracelo neplatný pointer, což vede (jak jsem se sám přesvědčil zhruba v 80%) k pádu backendu.
Základ funkce html_list je následující:
SPI_connect ();
if ((ret = SPI_exec (query, max)) < 0)
elog (ERROR, "SPI_exec vrátilo %d", ret);
sloupec = SPI_fnumber(SPI_tuptable->tupdesc, jm_sloupce);
if (sloupec <= 0)
elog (ERROR, "SPI_ERROR_NOATTRIBUTE");
zapsano = 0; zbyva = stranka;
txt = (text*) SPI_palloc (VARHDRSZ + zbyva);
txt = sts_strcatex2 ("<ul>\n", txt, &zapsano, &zbyva, stranka);
for (radek = 0; radek < SPI_processed; radek++)
{
txt = sts_strcatex2 ("<li>", txt, &zapsano, &zbyva, stranka);
txt = sts_strcatex2 (SPI_getvalue (SPI_tuptable->vals[radek],
SPI_tuptable->tupdesc, sloupec),
txt, &zapsano, &zbyva, stranka);
txt = sts_strcatex2 ("</li>\n", txt, &zapsano,
&zbyva, stranka);
}
txt = sts_strcatex2 ("</ul>", txt, &zapsano, &zbyva, stranka);
SPI_finish ();
PG_RETURN_TEXT_P(txt);
K textu není co dodat. V cyklu čtu jednotlivé hodnoty sloupce (SPI_getvalue vrací vždy ukazatel na řetězec), které přidávám do struktury txt typu text.
Po překladu můžeme funkci zaregistrovat v PostgreSQL
CREATE OR REPLACE FUNCTION html_list (cstring, cstring, int, int) RETURNS text AS 'html_list.so','html_list' LANGUAGE 'C';
Tabulka, kterou jsem pro test použil, obsahuje 192 záznamů, výsledkem je text o délce mírně přesahující 6KB. Pro změření casu jsem napsal krátký skript v Pythonu. Jelikož jsem chtel použít profiler Pythonu, rozdělil jsem příkazy do samostatných funkcí.
#!/usr/local/bin/python
import psycopg, time
def plain(cur):
cur.execute ("SELECT jmeno from jmena")
def html_list(cur):
cur.execute ("SELECT html_list ('select jmeno from jmena',
'jmeno',0,8000)")
rows = cur.fetchall ()
print rows[0][0]
def pyt(cur):
cur.execute ("SELECT jmeno from jmena")
rows = cur.fetchall ()
print "<ul>"
for r in rows:
print "<li>%s</li>" % r[0]
print "</ul>"
def main ():
for i in range(100):
con = psycopg.connect ("dbname=testdb011")
cur = con.cursor ()
plain(cur)
html_list(cur)
pyt(cur)
import profile
profile.run ('main()')
Výsledkem je tato tabulka
ncalls tottime percall cumtime percall filename:lineno(function)
1 0.000 0.000 4.670 4.670 <string>:1(?)
1 0.090 0.090 4.760 4.760 profile:0(main())
0 0.000 0.000 profile:0(profiler)
100 3.670 0.037 3.670 0.037 test.py:12(pyt)
1 0.620 0.620 4.670 4.670 test.py:20(main)
100 0.250 0.003 0.250 0.003 test.py:4(plain)
100 0.130 0.001 0.130 0.001 test.py:7(html_list)
Z ní je zřejmé, že vytvoření seznamu funkcí html_list je oproti „normálnímu“ sestavení seznamu zhruba 28krát rychlejší. Důvodem jsou nejspíš pomalejší operace Pythonu s řetězci, ale i větší režie spojená s procházením pole v Pythonu.
Celý příklad naleznete v archivu.
Příští díl bude poslední a bude věnován tabulkovým funkcím PostgreSQL.
Zdroje
- Dokumentace
- Ostatní
