Hlavní navigace

Rozšiřování PostgreSQL v C - Tabulkové funkce

Pavel Stěhule 27. 12. 2002

Table Function API umožňuje navrhovat vlastní funkce vracející tabulku (dále tabulkové funkce TF). TF může vracet tabulku obsahující skalární, nebo složené (více sloupcové) typy. TF jsou specifické svým opakovaným voláním při vracení tabulky - TF je volána pro každý vrácený řádek.

Pokud budeme pracovat s vícesloupcovými tabulkami (a to ve většině případů budeme), musíme dokázat vytvořit hodnotu složeného typu. Postup je zhruba následující: vytvoříme jakousi šablonu, pak podle této šablony prostor pro samotná data, a na závěr tento prostor vyplníme vlastními daty, kdy my data poskytujeme v cstring formátu a funkce PostgreSQL se pak postarají o správnou konverzi (podle šablony) do typů PostgreSQL. Mohou nastat dva základní případy, a to, když existuje složený typ

tupdesc = RelationNameGetTupleDesc ("_tabsin");

nebo, pokud složený typ dynamicky definujeme

tupdesc = CreateTemplateTupleDesc (8 , false);
TupleDescInitEntry (tupdesc, 1, "Typ",      CHAROID,    -1, 0, false);
TupleDescInitEntry (tupdesc, 2, "Prava",    VARCHAROID, -1, 0, false);
TupleDescInitEntry (tupdesc, 3, "Uzivatel", VARCHAROID, -1, 0, false);
TupleDescInitEntry (tupdesc, 4, "Skupina",  VARCHAROID, -1, 0, false);
TupleDescInitEntry (tupdesc, 5, "Velikost", INT4OID,    -1, 0, false);
TupleDescInitEntry (tupdesc, 6, "Vytvoreno",ABSTIMEOID, -1, 0, false);
TupleDescInitEntry (tupdesc, 7, "Modif",    ABSTIMEOID, -1, 0, false);
TupleDescInitEntry (tupdesc, 8, "Jmeno",    VARCHAROID, -1, 0, false);

Id typů najdeme v catalog/pg_ty­pe.h. Další postup je pak stejný pro obě zmíněné varianty:

slot = TupleDescGetSlot (tupdesc);
  attinmeta = TupleDescGetAttInMetadata (tupdesc);
      ... plnění hodnot values ....
  tuple = BuildTupleFromCStrings (attinmeta, values);
  result = TupleGetDatum (slot, tuple);

Poslední příkaz provádí konverzi typu Tuple na Datum, který jediný můžeme vrátit. TF musí používat V1 volající konvenci. Konverzi do nativních typů PostgreSQL provádí funkce BuildTupleFrom­CStrings. Parametrem je struktura typu AttInMetadata (šablona pro vytvoření hodnoty) a pole s ukazateli na jednotlivé hodnoty v textovém formátu. Pokud je některý z ukazatelů NULL, pak se jako konvertovaná hodnota použije NULL. Struktury tabdesc, slot a attinmeta lze (a měly by se) použít opakovaně (pro každý řádek). Je neefektivní je vytvářet pro každý vrácený řádek znovu.

Dříve, než se budu věnovat samotným tabulkovým funkcím, je třeba alespoň nastínit způsob, jakým PostgreSQL přiděluje dynamickou paměť. Paměťový prostor můžeme dynamicky alokovat voláním funkcí palloc, případně změnit jeho velikost funkcí repalloc. Paměť je přidělována z tzv. aktuálního paměťového kontextu. Standardně je paměťový kontext vytvořen při staru funkce a zrušen po doběhu funkce. Tím je zajištěno, že se veškerá dynamická paměť alokovaná funkcí nebo funkcemi, které volala, uvolní.

Toto chování je ale v případě tabulkových funkcí nežádoucí. Tabulková funkce se spouští opakovaně pro každý řádek vrácené tabulky. My ale potřebujeme prostor, kam můžeme uložit své datové struktury, které budou existovat po celou dobu opakovaného volání tabulkové funkce (výše zmíněné struktury). Proto máme k dispozici multicall paměťový kontext, který je uvolněn až po posledním volání tabulkové funkce (to se pozná tak, že funkce nevrací žádná data). Každý paměťový kontext se musí ještě aktivovat voláním fce. MemoryContextSwit­chTo.

if (SRF_IS_FIRSTCALL ())
 {
  MemoryContext  oldcontext;
  funcctx = SRF_FIRSTCALL_INIT ();
  oldcontext = MemoryContextSwitchTo
     (funcctx->multi_call_memory_ctx);

     ... získání ukazatelů z multicall kontextu

  MemoryContextSwitchTo (oldcontext);
     ... od teto chvile vse po starem
 }
funcctx = SRF_PERCALL_SETUP ();

Funkce SRF_IS_FIRSTCALL vrací nenulovou hodnotu, pokud je funkce spuštěna poprvé (v rámci vyřizování jedné žádosti o tabulku, nikoliv po dobu existence knihovny v paměti). SRF_FIRSTCALL_INIT vytvoří multicall paměťový kontext. Ze struktury functx ještě použijeme pole user_fctx, které použijeme k uložení ukazatele na vlastní „statická“ data.

Prvním příkladem TF je funkce fcetabsin, která vrací tabulku s hodnotami funkce sin. Parematry funkce mají význam od, do a krok.

#include "postgres.h"
#include "funcapi.h"
#include <math.h>

typedef struct fcetabsin_info {
  float8 iter;
  float8 krok;
  float8 max;
} fcetabsin_info;

PG_FUNCTION_INFO_V1 (fcetabsin);

Datum
fcetabsin (PG_FUNCTION_ARGS)
{
 FuncCallContext *funcctx;
 TupleDesc        tupdesc;
 TupleTableSlot  *slot;
 AttInMetadata   *attinmeta;
 fcetabsin_info  *fceinfo;

 if (SRF_IS_FIRSTCALL ())
  {
   MemoryContext  oldcontext;
   funcctx = SRF_FIRSTCALL_INIT ();
   oldcontext = MemoryContextSwitchTo
      (funcctx->multi_call_memory_ctx);

   tupdesc = RelationNameGetTupleDesc ("_tabsin");
   slot = TupleDescGetSlot (tupdesc);
   funcctx -> slot = slot;
   attinmeta = TupleDescGetAttInMetadata (tupdesc);
   funcctx -> attinmeta = attinmeta;

   fceinfo = (fcetabsin_info*) palloc (sizeof (fcetabsin_info));
   fceinfo -> iter = PG_GETARG_FLOAT8 (0);
   fceinfo -> max  = PG_GETARG_FLOAT8 (1);
   fceinfo -> krok = PG_GETARG_FLOAT8 (2);
   funcctx -> user_fctx = (void*) fceinfo;

   MemoryContextSwitchTo (oldcontext);
  }
 funcctx = SRF_PERCALL_SETUP ();
 fceinfo = (fcetabsin_info*) funcctx -> user_fctx;
 if (fceinfo -> iter <= fceinfo -> max)
  {
   Datum result; char **values;
   HeapTuple tuple;

   values = (char **) palloc (2 * sizeof (char *));
   values [0] = (char*) palloc (16 * sizeof (char));
   values [1] = (char*) palloc (16 * sizeof (char));

   snprintf (values [0], 16, "%f", fceinfo -> iter);
   snprintf (values [0], 16, "%f", sin(fceinfo -> iter));
   fceinfo -> iter += fceinfo -> krok;

   tuple = BuildTupleFromCStrings (funcctx -> attinmeta, values);
   result = TupleGetDatum (funcctx -> slot, tuple);

   SRF_RETURN_NEXT (funcctx, result);
   }
 else
   {
     SRF_RETURN_DONE (funcctx);
   }
}

Funkci zaregistrujeme SQL příkazy:

CREATE TYPE _tabsin AS (i float8, j float8);

CREATE OR REPLACE FUNCTION fcetabsin (float8, float8, float8)
 RETURNS SETOF _tabsin AS 'tab_fce_sin.so', 'fcetabsin' LANGUAGE 'C';

a otestujeme příkazem

SELECT * FROM fcetabsin (0.1, 3, 0.1);

Příklad je hodně školský, ale díky tomu je ještě přehledný. Podobný příklad (značně smysluplnější) naleznete v contrib, kde jsou funkce vracející tabulky s číselnými hodnotami v daném rozdělení atd.

Druhým příkazem je funkce ls, vracející výpis adresáře. Zdrojový kód pro danou funkci naleznete zde. Funkci zaregistrujte:

CREATE OR REPLACE FUNCTION ls (cstring) RETURNS SETOF record
   AS 'ls.so', 'ls', LANGUAGE 'C';

Poté si můžeme nechat vypsat obsah adresáře.

testdb011=# select * from ls ('/home/') as
  (typ "char", prava varchar, uzivatel varchar, skupina varchar,
velikost int4, vytvoreno abstime, modifikovano abstime,
jmeno varchar);
 typ |   prava   | uzivatel | skupina  | velikost |
----+-----------+----------+----------+----------+-
 d   | rwxr-xr-x | root     | root     |     4096 |
 d   | rwxr-xr-x | root     | root     |     4096 |
 d   | rwxr-xr-x | pavel    | users    |     4096 |
 d   | rwx------ | zdenek   | zdenek   |     4096 |
 d   | rwx------ | postgres | postgres |     4096 |

       vytvoreno        |    modifikovano        | jmeno
------------------------+------------------------+----------
 2002-09-03 12:04:09-04 | 2002-09-03 12:04:09-04 | .
 2002-11-19 20:47:43-05 | 2002-11-19 20:47:43-05 | ..
 2002-11-19 22:25:56-05 | 2002-11-19 22:25:56-05 | pavel
 2002-09-03 08:52:54-04 | 2002-09-03 08:52:54-04 | zdenek
 2002-11-19 22:26:02-05 | 2002-11-19 22:26:02-05 | postgres
(5 rows)

Tato funkce je již užitečnější. Díky ní můžeme z PL/pgSQL monitorovat obsah adresářů, výpisem adresáře /tmp můžeme zjistit, kdy a jak dlouho je PostgreSQL spuštěn atd. V případě TF se fantazii meze nekladou. Můžeme si napsat TF pro import CSV nebo XML souborů, pro parsování MIME dokumentů, pro přístup k IMAP, POP3 nebo LDAP serverům.

Celý příklad naleznete v archivu.

Zdroje

Našli jste v článku chybu?
Měšec.cz: Zdravotní a sociální pojištění 2017: Připlatíte

Zdravotní a sociální pojištění 2017: Připlatíte

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

Vitalia.cz: Nestlé vyvinula nový typ „netloustnoucího“ cukru

Nestlé vyvinula nový typ „netloustnoucího“ cukru

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy

Lupa.cz: Co se dá měřit přes Internet věcí

Co se dá měřit přes Internet věcí

Vitalia.cz: Chtějí si léčit kvasinky. Lék je jen v Německu

Chtějí si léčit kvasinky. Lék je jen v Německu

Vitalia.cz: Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Láska na vozíku: Přitažliví jsme pro tzv. pečovatelky

Vitalia.cz: Baletky propagují zdravotní superpostel

Baletky propagují zdravotní superpostel

DigiZone.cz: ČT má dalšího zástupce v EBU

ČT má dalšího zástupce v EBU

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

DigiZone.cz: Rádio Šlágr má licenci pro digi vysílání

Rádio Šlágr má licenci pro digi vysílání

Vitalia.cz: Dáte si jahody s plísní?

Dáte si jahody s plísní?

Root.cz: Certifikáty zadarmo jsou horší než za peníze?

Certifikáty zadarmo jsou horší než za peníze?

120na80.cz: Rakovina oka. Jak ji poznáte?

Rakovina oka. Jak ji poznáte?

DigiZone.cz: Sony KD-55XD8005 s Android 6.0

Sony KD-55XD8005 s Android 6.0

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

Podnikatel.cz: Babiš: E-shopy z EET možná vyjmeme

Babiš: E-shopy z EET možná vyjmeme

Vitalia.cz: To není kašel! Správná diagnóza zachrání život

To není kašel! Správná diagnóza zachrání život

DigiZone.cz: Flix TV má set-top box s HEVC

Flix TV má set-top box s HEVC