Skip site navigation (1) Skip section navigation (2)

libpq, getting array from table... (PostGreSQL8)

From: Basile STARYNKEVITCH <basile(at)starynkevitch(dot)net>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: libpq, getting array from table... (PostGreSQL8)
Date: 2005-11-02 22:34:49
Message-ID: 20051102223449.GA22475@ours.starynkevitch.net (view raw or flat)
Thread:
Lists: 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,
                    (const Oid *)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 another way ***/
  }

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 by date

Next:From: Ranen GhoshDate: 2005-11-03 15:22:55
Subject: error encountered in windows when libpq sends large query string to server
Previous:From: Zlatko MatićDate: 2005-11-02 22:10:46
Subject: installing PygreSQL and psychopg2 ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group