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 ?







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 intAS '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 ?
Wed, 17 Apr 2002 11:14:02 +0200
"Darko Prenosil" <Darko.Prenosil@finteh.hr>
plpq