[HACKERS] plpq

Wed, 17 Apr 2002 01:50:38 -0700

I come to an idea using dblink from a contrib directory:
 
Why my pl/psql function can't use common PQ stuff to connect to other database ?
So I wrote a wrapper around PQ functions and registered them in postgres.
Now I can write pl/psql functions like:
 
CREATE OR REPLACE FUNCTION TestPQ ()
    RETURNS int
AS '
DECLARE cSql varchar;
  cConnStr varchar;
  nConnPointer int;
  nRet int;
  cDb text;
  cUser text;
  cPass text;
  cHost text;
  cPort text;
  cTemp text;
  nPid int;
  nResPointer int;
  nResStatus int;
  cResStatus text;
  cResultError text;
  nTuples int;
  nFields int;
  nFieldCurr int;
BEGIN
 cSql:=''SELECT * FROM pg_database'';
 cConnStr:=''user=finteh host=bart dbname=reg_master'';
 
 --Connect and get some data from connection
 nConnPointer:=PQconnectdb(cConnStr);
 cDb:=PQdb(nConnPointer);
 cUser:=PQuser(nConnPointer);
 cPass:=PQpass(nConnPointer);
 cHost:=PQhost(nConnPointer);
 cPort:=PQport(nConnPointer);
 nPid:=PQbackendPID(nConnPointer);
 RAISE NOTICE ''Connected to : %@%:% as % with password % and backend pid is: %'',cDb,chost,cPort,cUser,cPass,nPid;
 
 --Execute a query and return some data
 nResPointer:=PQexec(nConnPointer,cSql);
 nTuples:=PQntuples(nResPointer);
 nFields:=PQnfields(nResPointer);
 RAISE NOTICE ''Query : % returned % fields in % rows.'',cSql,nFields,nTuples;
 
 nFieldCurr:=0;
 cTemp:='''';
 WHILE nFieldCurr<=nFields-1 LOOP
     cTemp:=cTemp || PQfname(nResPointer,nFieldCurr) || ''|'';
     nFieldCurr:=nFieldCurr+1;
 END LOOP;
 RAISE NOTICE ''Returned field names : %'',cTemp;
 
 nFieldCurr:=PQfnumber(nResPointer,''encoding'');
 RAISE NOTICE ''Index of field "encoding" is : %'',nFieldCurr;
 
 
 --Variable to return connection status:
 nRet:= PQstatus(nConnPointer);
 
 PERFORM PQclear(nResPointer);
 PERFORM PQreset(nConnPointer);
 PERFORM PQfinish(nConnPointer);
 RETURN nRet;
END;'
LANGUAGE 'plpgsql' ;
SELECT TestPQ();
 
In other words pl/psql function become client of another postgres backend.
 
implemented functions so far:
 
extern Datum Connectdb(PG_FUNCTION_ARGS);
extern Datum SetdbLogin(PG_FUNCTION_ARGS);
extern Datum Status(PG_FUNCTION_ARGS);
extern Datum Finish(PG_FUNCTION_ARGS);
extern Datum Reset(PG_FUNCTION_ARGS);
extern Datum Db(PG_FUNCTION_ARGS);
extern Datum User(PG_FUNCTION_ARGS);
extern Datum Password(PG_FUNCTION_ARGS);
extern Datum Host(PG_FUNCTION_ARGS);
extern Datum Port(PG_FUNCTION_ARGS);
extern Datum Tty(PG_FUNCTION_ARGS);
extern Datum ErrorMessage(PG_FUNCTION_ARGS);
extern Datum BackendPID(PG_FUNCTION_ARGS);
extern Datum Exec(PG_FUNCTION_ARGS);
extern Datum ResultStatus(PG_FUNCTION_ARGS);
extern Datum ResStatus(PG_FUNCTION_ARGS);
extern Datum ResultErrorMessage(PG_FUNCTION_ARGS);
extern Datum Clear(PG_FUNCTION_ARGS);
extern Datum EscapeString(PG_FUNCTION_ARGS);
extern Datum Ntuples(PG_FUNCTION_ARGS);
extern Datum Nfields(PG_FUNCTION_ARGS);
extern Datum Fname(PG_FUNCTION_ARGS);
extern Datum Fnumber(PG_FUNCTION_ARGS);   
 
The rest will be done in few days.
 
Now I have one problem: Is it possible to return PGresult in same way that
SQL select statement does ? I saw the code in dblink that does it. Is it
the only way ? Anyone know where in documentation to look for structure
of sql result ?
 
If anyone is interested I'll be happy to send the code.
Is it interesting enough to put it in the /contrib maybe ?
Bruce ?

Reply via email to