libpq, getting array from table... (PostGreSQL8)
От | Basile STARYNKEVITCH |
---|---|
Тема | libpq, getting array from table... (PostGreSQL8) |
Дата | |
Msg-id | 20051102223449.GA22475@ours.starynkevitch.net обсуждение исходный текст |
Список | pgsql-interfaces |
Dear All, This is probably a simple question, and I admit I am a newbie in PostgreSQL (but I have a small MySQL experience). But I read quickly the documentations (but didn't dive into PostGreSQL8 source code). I'm running PostGreSQL8 on Linux (Debian/Sid/x86 & amd64) and coding in C. Suppose I have a simple table like CREATE TABLE tab1 ( num INTEGER UNIQUE, vals BYTEA[]); and assume that the table is somehow filled (with for different num, an array vals of various length) I want to code in C (using libpq) the routine whose signature could be char** get_values(int num); which to a given num returns a C (calloc-ated) array of C strings (probably strdup-ed) which matches the number num in table tab1 as vals My first guess was something like char *params[1]; char numbuf[20]; snprintf(numbuf, sizeof(numbuf), "%d", num); params[0] = numbuf; PGresult* res = PQexecParams(conndb, "SELECT vals FROM tab1 WHERE num=$1", 1, (constOid *)0, //paramTypes, params, (const int*)0, (const int*)0, 0); if (res && PQresultStatus(res)==PGRES_TUPLES_OK) { char* vstr = PQgetvalue(res,0,0); /****but here I am lost, how can I decode vstr? Do I have to parse the SQL syntax for arrays or is there anotherway ***/ } I cannot easily figure out how to decode (or parse) the resulting vstr string. I want to avoid (both for perfomance and for coding ease reasons) parsing complex syntax - each element of a vals array in the database is an arbitrary byte string (containing null bytes, and other naughty stuff). I guess that the reply message (in the protocol) may contain everything I need (without having to parse stuff which is pretty-printed within PostgreSQL server) An ashaming [pseudo] solution, very ugly, would be to get the length of the vals array with a request like SELECT array_upper(vals,1) AS nb_val FROM tab1 WHERE num=$1 and then to build a request string suited for the particular value N of nb_val, assuming it is 3, I would build: SELECT vals[1] AS v1, vals[2] AS v2, vals[3] AS v3 FROM tab1 WHERE num=$1 and then the PQgetvalue-s are expected to be the real byte strings. But this solution is ugly (and might violate atomicity) http://www.postgresql.org/docs/8.0/interactive/libpq-exec.html suggest to get the Oid of the type from table pg_type, but I don't fully understand how., then what should I do with this Oid http://www.postgresql.org/docs/current/static/catalog-pg-type.html? Should I use the typreceive field? Some other basic questions showing my ignorance: How can I get, within an interactive psql session, the structure of a table (ie the equvalent of DESCRIBE in MySQL) and the list of all my tables (like SHOW TABLES in MySQL)? BTW, the motivation of all this is to implement a toy language with persistency Thanks for your attention. PS (maybe such basic questions could go into the documentation?) -- Basile STARYNKEVITCH http://starynkevitch.net/Basile/ email: basile(at)starynkevitch(dot)net 8, rue de la Faïencerie, 92340 Bourg La Reine, France
В списке pgsql-interfaces по дате отправления: