Hello,
Here is my whole function...
*************************************************************************************************************
CREATE OR REPLACE FUNCTION retriev() RETURN void AS
$tmp$
DECLARE
colnam text[];
j text;
sum1 numeric;
totsum numeric;
datfrm text;
datto text;
BEGIN
datfrm := quote_literal('2000-11-16');
datto := quote_literal('2015-11-02');
totsum :=0;
colnam := array(select distinct table_schema::text from information_schema.tables where table_schema like '%ab%');
EXECUTE 'CREATE TABLE db2.public.temp(totalsum numeric(100,20))';
FOR j in array_lower(colnam,1).. array_upper(colnam,1)
LOOP
EXECUTE ' SELECT coalesce(sum(db1_col),0)date between '||datfrm||' AND '||datto into sum1;
totsum := totsum + sum1;
INSERT INTO db2.public.temp(totalsum) VALUES(totsum);
END LOOP;
END
$tmp$
LANGUAGE 'plpgsql' VOLATILE;
--Calling the function
select retriev()
***************************************************************************************************************************************
Here the underlined queries are for db2. I need to create function in the second database since first database contain data that is not for public access. So from second database I call the db1.retriev and it uses necessary data from db1 and result is saved as a table in second database. Actual function has more parameters and result may contain many rows , for that I want to create table in db2.
Regards
Archana
