Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

> I assumed you were generating the progress indicator from query results in a remote DB. It turns out that's entirely
notwhat you're doing, but how were we supposed to know that? 
>

Well, it made sense to leave dblink mostly out of things (it's a
useful way to get a function to block though) until I'd figured out if
it was feasible to do this with a function that RETURNS TABLE(...).
Occam's razor and all that. It wasn't. I was trying to give background
information, an indication of my intent - there may have been an
entirely different approach that had not yet occurred to me, so that
information may have been pertinent (although probably not - the fact
that dblink is involved in incrementing the function probably doesn't
matter at all. It probably might as well be anything else for our
purposes).

> You say you want your function to behave like a cursor.
> A function can't behave like a cursor, just as a select can't; both result in a result set. What RETURN NEXT and
RETURNQUERY do is make it possible to access the function results row by row. You still need a cursor to get the
behaviourof a cursor out of that. 

I understand the distinction (after all, I suggested that the solution
was likely to involve returning a refcursor in my original post) -
let's not get bogged down in semantics though. I guess my question
boils down to: can I return a cursor, but not to return the result of
a single select, but of multiple different selects in succession, to
report progress as described, or, alternatively, do something that
will produce similar results? Here's my admittedly very rough stab at
this, using a function that RETURNS TABLE, an approach that evidently
doesn't work (which is not to say that I ever had a reasonable
expectation of this working. In fact, I was almost certain it wouldn't
work, but I needed to start somewhere):

CREATE OR REPLACE FUNCTION download_sales(download_date_arg date,
rem_arg integer[]) RETURNS TABLE (progress integer, message text) AS
$BODY$
DECLARE
    cur_progress integer DEFAULT 0;
    tup rems%rowtype;
BEGIN
    progress := cur_progress;
    cur_progress := cur_progress + 1;
    message := 'Beginning downloading sales...';
    RETURN NEXT;


    FOR tup IN SELECT * FROM rems WHERE is_active AND  id = ANY(rem_arg)
    LOOP
        DECLARE
            conn_str text;
            query_str text;
        BEGIN
            -- connection will timeout after 7 seconds.
            conn_str = 'hostaddr=' || tup.ip || ' port=' || tup.port ||
'dbname=remote_db user=' || tup.username || ' password=' ||
tup.password || ' connect_timeout=7';
            -- open persistent connection to rem DB
            progress := -1;
            cur_progress := 0;
            message := 'Connecting to rem ''' || tup.description || '''';
            RETURN NEXT;
            SELECT dblink_connect(conn_str);
                        -- TODO: Actually transfer data. INSERT it
into local tables, and indicate progress to user as above
                        -- The remote DB stores how many tuples there
are for the day, so I don't have to do a count(*) first
                        -- we send a magic number (say -1), which
indicates that number of tuples follows, then we send number of tuples
                        -- then we send which tuple we're currently
on, for each and every remote DB
        EXCEPTION
            WHEN CONNECTION_EXCEPTION THEN
                message := 'Could not connect to rem ''' || tup.description || '''';
                RETURN NEXT;
                RETURN;

        END;
    END LOOP;

    RETURN;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

As I've already said, the problem with this approach is that I see all
3 messages at once, when the CONNECTION_EXCEPTION is thrown and we
finally RETURN, after about 7 seconds (which is undoubtedly how
RETURNS TABLE is documented to behave). I want (although, as I've
said, don't expect) to see the first two messages immediately, and
only the third when the connection fails, so I know what's happening
in real-time.

Regards,
Peter Geoghegan