Home > mailing lists

plpq - Mailing list pgsql-hackers

From	Darko Prenosil
Subject	plpq
Date	April 17, 2002 08:09:54
Msg-id	000d01c1e5f0$3981bc80$f600000a@darko Whole thread Raw
List	pgsql-hackers

Tree view

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 ?

pgsql-hackers by date:

From: Dragos Manzateanu
Date: 17 April 2002, 07:36:53
Subject: date_in function

From: Michael Loftis
Date: 17 April 2002, 08:17:50
Subject: Re: Index Scans become Seq Scans after VACUUM ANALYSE

plpq - Mailing list pgsql-hackers

Previous

Next