*

*/

Version 0.2 (29 May, 2001):

Version 0.3 (14 June, 2001):

Function to test returning data set from remote database

Tested under Linux (Red Hat 6.2 and 7.0) and PostgreSQL 7.1 and 7.2devel

Release Notes:

Version 0.3

- fixed dblink invalid pointer causing corrupt elog message

- fixed dblink_tok improper handling of null results

- fixed examples in README.dblink

Version 0.2

- initial release

'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'

,'select f1, f2 from mytable'

) as dblink_p

union

select null,null where false

union all

select null where false

) as t1

where

f1 = 'mycriteria';

dblink_tok(t1.dblink_p,0) = 'mycriteria';

in order to work around an issue with the query optimizer. A more convenient way to approach

this problem is to create a view:

'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'

,'select f1, f2 from mytable'

) as dblink_p

union

select null,null where false

union all

select null where false

) as t1;

Then you can simply write:

conn=PQconnectdb(optstr);

if (PQstatus(conn)==CONNECTION_BAD)

{

msg=PQerrorMessage(conn);

msg=pstrdup(PQerrorMessage(conn));

PQfinish(conn);

elog(ERROR,"dblink: connection error: %s",msg);

}

res=PQexec(conn,"BEGIN");

if (PQresultStatus(res)!=PGRES_COMMAND_OK)

{

msg=PQerrorMessage(conn);

msg=pstrdup(PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

elog(ERROR,"dblink: begin error: %s",msg);

res=PQexec(conn,execstatement);

if (!res|| (PQresultStatus(res)!=PGRES_COMMAND_OK&&PQresultStatus(res)!=PGRES_TUPLES_OK))

{

msg=PQerrorMessage(conn);

msg=pstrdup(PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

elog(ERROR,"dblink: sql error: %s",msg);

res=PQexec(conn,"FETCH ALL in mycursor");

if (!res||PQresultStatus(res)!=PGRES_TUPLES_OK) {

msg=PQerrorMessage(conn);

msg=pstrdup(PQerrorMessage(conn));

PQclear(res);

PQfinish(conn);

elog(ERROR,"dblink: sql error: %s",msg);

elog(ERROR,"dblink: field number %d does not exist",fldnum);

}

text_len=PQgetlength(results->res,results->tup_num,fldnum);

if (PQgetisnull(results->res,results->tup_num,fldnum)==1) {

result= (char*)palloc(text_len+1);

PG_RETURN_NULL();

if (result!=NULL) {

strcpy(result,PQgetvalue(results->res,results->tup_num,fldnum));

strcat(result,"\0");

}else {

elog(ERROR,"dblink: insufficient memory" );

}

result_text=DatumGetTextP(DirectFunctionCall1(textin,CStringGetDatum(result)));

text_len=PQgetlength(results->res,results->tup_num,fldnum);

result= (char*)palloc(text_len+1);

PG_RETURN_TEXT_P(result_text);

if (result!=NULL) {

strcpy(result,PQgetvalue(results->res,results->tup_num,fldnum));

strcat(result,"\0");

}else {

elog(ERROR,"dblink: insufficient memory" );

}

result_text=DatumGetTextP(DirectFunctionCall1(textin,CStringGetDatum(result)));

PG_RETURN_TEXT_P(result_text);

}

}