From: Bear <beatabogdanowicz@gmail.com>
Subject: Problem with inserting data produced by the function (when the
	function is performed).
Date: Wed, 21 Mar 2012 01:30:45 -0700 (PDT)
Organization: http://groups.google.com
Lines: 49
Message-ID: 
 <d49d2379-34f2-413e-8992-708ec3e54389@r27g2000vbn.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Complaints-To: groups-abuse@google.com
Injection-Info: r27g2000vbn.googlegroups.com; posting-host=212.33.81.250;
	posting-account=phv_zwoAAADWlIItqWRLEQipwamV29aK
User-Agent: G2/1.0
To: pgsql-sql@postgresql.org

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