Problem with inserting data produced by the function (when the function is performed). - Mailing list pgsql-sql

From Bear
Subject Problem with inserting data produced by the function (when the function is performed).
Date
Msg-id d49d2379-34f2-413e-8992-708ec3e54389@r27g2000vbn.googlegroups.com
Whole thread Raw
List pgsql-sql
The function calculates the data line by line, and inserts into the
table, at the end of the function I need to send data from a table to
another database via dblink. I noticed that the problem is to perform
this operation in one function (or the main function of the two
subfunction). My guess is that unless the function has finished
running postgres no data dumps. Data is available in the table after
the function finished, and I can send them without a problem.
The operation must be done in one function or the main function and
two of subfunction (first subfunction loads the data into a table,
second subfunction sends data to another database via dblik).

How to force a permanent record of data in a table when executing the
function?

Sample code:
------------------------------
CREATE OR REPLACE FUNCTION filter_reports_yesterday() RETURNS void AS
$BODY$declare
target_table_name varchar;
res record;
cr cursor forselect distinct substr(tablename, 0, position('_row' in tablename))
as adr_sym from pg_tableswhere tablename like '%_row' and schemaname = 'arch_'||
to_char((current_date-1),'YYYY_MM');
al_cr refcursor;
beginfor res in crloop    --first subfunction generating data in table    execute 'select
packet_filter_yesterday('''||(current_date-1)||''',
'''||res.adr_sym||''')';    target_table_name := 'rap_' || to_char((current_date-1),'YYYY_MM')
|| '.' || res.adr_sym || '_fil';
    --second function inserting data from table to another database via
dblink    execute 'select insert_FS3('''||target_table_name||''')';
end loop;
end$BODY$ LANGUAGE plpgsql
------------------------------
When doing the first function (for a single table, no loops) and after
the second run separately the data is sent. When both feature walk in
a loop in main function is the result of lack of data.

thanks in advance


pgsql-sql by date:

Previous
From: "Edward W. Rouse"
Date:
Subject: Re: ignore case in where clause
Next
From: John Fabiani
Date:
Subject: Can I read the data without commit