Re: dblink and plpq

On Friday 30 August 2002 01:25, you wrote:
> Darko Prenosil wrote:
> > Hi Joe !
> >
> > I wrote some code to implement PQlib so it can be used in SQL functions.
> > With those functions pl/psql script becomes client of another SQL server.
> > In file attachment is plpq.tar.gz that should be copied into
> > /contrib/plpq. Add this dir in /contrib/Makefile  (WANTED_DIRS).
> >
> > The thing is that I did used some stuff from dblink, and I need your
> > opinion on it.
> > Maybe we can make one library that has all the functions ?
> >
> > Best regards !
>
> Hi Darko,
>
> I'm working on dblink right now, with a deadline of September 1 to get
> it finished. I'll try to take a look at your stuff and include some of
> it, but I doubt I'll have the time to include it all. Do you have a
> preference for one or two of the functions you wrote? We can always add
> more for the next release (i.e. 7.4) which hopefully won't be too long
> in the future.

There is no meaning to include PQexec wrapper function for example, and not
include PQGetValue. We can skip only few functions (such as PQsetdbLogin) and
preserve functionality. If there is not enough time to do it now, then we
should wait (IMHO).
I just think that such interface should be interesting.
For example there is dbmirror replication engine in contrib directory,
and it is using perl script to actually replicate the data. I did it in
similar way as dbmirror, but replication is done with
pl/pgsql. I think that with those wrapper functions even dbmirror perl script
can be ported to pl/pgsql.
There is my rsync function (just to get a picture what I was trying to do. It
is riped out of the context but it is illustrative):

CREATE FUNCTION "rsync" () RETURNS integer AS '
DECLARE
        connString text;
        rsync_row RECORD;
        connHandle int;
        cTmp text;
        resultHande int;
        nResStatus int;
BEGIN
    connString:=srv_conn_string(''reg_master'');
    connHandle:=PQconnectdb(connString);
    cTmp:=PQstatusStr(connHandle);
    IF  cTmp = ''CONNECTION_BAD'' THEN
        --NOT CONNECTED - RAISE ERROR
        RAISE EXCEPTION ''rlog sync: Connection error %'',cTmp;
    ELSE
        --CONNECTED
        RAISE NOTICE ''rlog sync: CONNECT (%) - status[%]'',connString,cTmp;
    END IF;
    RAISE NOTICE ''rlog sync: BEGIN .... '';
    --Start transaction on host server
    resultHande:=PQExec(connHandle,''BEGIN;'');
    nResStatus=PQresultStatus (resultHande);
    IF nResStatus != 1 THEN
        PERFORM PQfinish(connHandle);
        RAISE EXCEPTION ''rlog sync: Execution status % on BEGIN
TRANSACTION'',nResStatus;
    END IF;
    --Loop through unsync records and send them to host server
    FOR rsync_row IN SELECT oid,* from rlog_data WHERE rsync_time IS NULL LOOP
        resultHande:=PQExec(connHandle,rsync_row.query_src);
        nResStatus=PQresultStatus (resultHande);
        IF nResStatus != 1 THEN
            cTmp:=PQResultErrorMessage(resultHande);
            PERFORM PQfinish(connHandle);
            RAISE EXCEPTION ''rlog sync: Execution of SQL statement % finished with
error: %'',rsync_row.query_src,cTmp;
        END IF;
        UPDATE rlog_data SET rsync_time = current_timestamp WHERE oid=rsync_row.oid;
        RAISE NOTICE ''rlog sync:    %'',rsync_row.query_src;
    END LOOP;
    resultHande:=PQExec(connHandle,''END;'');
    nResStatus=PQresultStatus (resultHande);
    IF nResStatus != 1 THEN
        PERFORM PQfinish(connHandle);
        RAISE EXCEPTION ''rlog sync: Execution status % on END
TRANSACTION'',nResStatus;
    END IF;
    PERFORM PQfinish(connHandle);
    RAISE NOTICE ''rlog sync: END .... '';
    RETURN 1;
END;' LANGUAGE 'plpgsql';