From: Bear <beatabogdanowicz@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Problem with inserting data produced by the function (when the function is performed).
Date: Wed, 21 Mar 2012 01:30:45 -0700 (PDT)
Message-ID: <d49d2379-34f2-413e-8992-708ec3e54389@r27g2000vbn.googlegroups.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: beatabogdanowicz@gmail.com
Subject: Re: Problem with inserting data produced by the function (when the function is performed).
In-Reply-To: <d49d2379-34f2-413e-8992-708ec3e54389@r27g2000vbn.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox