I'm trying to use this extension in PostgreSQL 9.5.7 to import bulk data from mysql tables.
I have a piece of plpsql code that loops and calls a function which is responsible to insert the data and should return the number of records inserted.
SELECT * INTO v_counter FROM pg_background_result(pg_background_launch('SELECT insert_data(...)')) AS (res integer);
Then the insert function looks like that:
BEGIN
INSERT INTO table (....)
SELECT .... FROM ...
WHERE ...
ORDER BY ...;
GET DIAGNOSTICS v_ct = ROW_COUNT;
RETURN v_ct;
END;
When I call this function like that, nothing is inserted and the value returned in v_ct is always 0.
However, if I modify the insert function and display the value in v_ct variable before it returns, then it works as expected.
BEGIN
INSERT INTO table (....)
SELECT .... FROM ...
WHERE ...
ORDER BY ...;
GET DIAGNOSTICS v_ct = ROW_COUNT;
RAISE NOTICE '%', v_ct;
RETURN v_ct;
END;
That's weird, is there anything I haven't understood correctly ?
The problem is my code or in the way the extension is coded. The function called with pg_background does not see the data in the table populated by the plpgsql function the first time that I run it.
Hi,
I'm trying to use this extension in PostgreSQL 9.5.7 to import bulk data from mysql tables. I have a piece of plpsql code that loops and calls a function which is responsible to insert the data and should return the number of records inserted.
SELECT * INTO v_counter FROM pg_background_result(pg_background_launch('SELECT insert_data(...)')) AS (res integer);
Then the insert function looks like that:
When I call this function like that, nothing is inserted and the value returned in v_ct is always 0. However, if I modify the insert function and display the value in v_ct variable before it returns, then it works as expected.
That's weird, is there anything I haven't understood correctly ?