[SQL] problem with nested dblink function

[Bear]

Hello everyone !

I have a problem with dblink function.
I need to insert data using pl / pgsql function from one server to
another.
The data from server FS4 to server FS3.
This code does not work:

"select dblink ('dbname = ant_trendy port = 5433 host = FS3 user =
postgres password = postgres ', 'insert into rap.5_7_fil (select *
from temp.fil_packet_template)')"

-->temp.fil_packet_template<-- is table from server FS4

Code with nested function dblink works with the Query window but when
it invokes the function inside the pl / pgsql I get the message all
right but the data are not inserted.

Code work with Query window / doesn't work with pl/pgsql function:
"select dblink
(' dbname=ant_trendy port=5433 host=FS3 user=postgres
password=postgres ',
' insert into rap.5_7_fil (select * from dblink ('' dbname=ant_trendy
port=5432 host=FS4 user=postgres password=postgres '', '' select *
from temp.fil_packet_template '')
as (
  "time" timestamp without time 
zone,
  tzewn double precision,
  tzas_msc double precision,
  tpow_msc double precision,
  vflow double precision,
  pwr double precision,
  tpwymco double precision,
  tpwymco_obl double precision,
  tzas double precision,
  tzas_obl double precision,
  tpow double precision,
  tcwu double precision,
  tcwu_obl double precision,
  tcwu_cyrk double precision,
  pstat double precision,
  pwr_proc double precision,
  tpow_diff double precision,
  x_rez_1 double precision,
  x_rez_2 double precision,
  x_rez_3 double precision,
  x_rez_4 double precision
  ))
')"

Does call the nested function dblink from  pl / pgsql functions has
there any restrictions?

How can I in a different way to pass data between servers?

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

Re: [SQL] problem with nested dblink function

[Bear]

I'm sorry, everything is in order.
Today I looked with fresh eyes, a slight adjustment in the calling
function and it works.
Thanks for your attention.

Best regards.
Ps. recently I deal with postgresql and I wanted to consult with you.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Problem with inserting data produced by the function (when the function is performed).

[Bear]

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),'_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),'_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