> I assumed you were generating the progress indicator from query results in a remote DB. It turns out that's entirely not what 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 RETURN QUERY do is make it possible to access the function results row by row. You still need a cursor to get the behaviour of 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general