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 for
select distinct substr(tablename, 0, position('_row' in tablename))
as adr_sym from pg_tables
where tablename like '%_row' and schemaname = 'arch_'||
to_char((current_date-1),'YYYY_MM');
al_cr refcursor;
begin
for res in cr
loop
--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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
