doubt in function

[priyan]

hi all,
I am having a doubt of how to write a function for my situation.

I am having two tables

portfolioscrip
portfolioscripdetails
 

i have to insert into portfolioscripdetails that table is having a column called portfolioscripid and portfolioscripdetailsid portfolioscripdetailsid is the auto generated column and portfoliscripid should be insert from portfolioscrip table

CREATE OR REPLACE FUNCTION insertportfolio(pdate timestamp without time zone)
  RETURNS integer AS
$BODY$
declare
id bigint;
countid bigint;
begin
select into countid count(portfolioscripid) from portfolioscrip;
for i in 1....countid loop
select into id portfolioscripid from portfolioscrip;
insert into portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,nseclose)values(id,pdate,0,0,0,0);
end loop;
return 1;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insertportfolio(pdate timestamp without time zone) OWNER TO postgres;
 

this is inserting the date to the same id so many times it is not using next id i have to use like that please help me to solve this problem

thanks in advance
priyan

[amitpatel66]

hi all,
I am having a doubt of how to write a function for my situation.

I am having two tables

portfolioscrip
portfolioscripdetails
 

i have to insert into portfolioscripdetails that table is having a column called portfolioscripid and portfolioscripdetailsid portfolioscripdetailsid is the auto generated column and portfoliscripid should be insert from portfolioscrip table

CREATE OR REPLACE FUNCTION insertportfolio(pdate timestamp without time zone)
  RETURNS integer AS
$BODY$
declare
id bigint;
countid bigint;
begin
select into countid count(portfolioscripid) from portfolioscrip;
for i in 1....countid loop
select into id portfolioscripid from portfolioscrip;
insert into portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,nseclose)values(id,pdate,0,0,0,0);
end loop;
return 1;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insertportfolio(pdate timestamp without time zone) OWNER TO postgres;
 

this is inserting the date to the same id so many times it is not using next id i have to use like that please help me to solve this problem

thanks in advance
priyan

Why you need to write a function here?? rather you can achieve this with a single insert statement:

INSERT INTO portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,ns  eclose)
(SELECT portfolioscripid,<substitute your pdate here>,0,0,0,0);
 

The above insert will insert all the portfolioscripid from one table to another......

[priyan]

Why you need to write a function here?? rather you can achieve this with a single insert statement:

INSERT INTO portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,ns  eclose)
(SELECT portfolioscripid,<substitute your pdate here>,0,0,0,0);
 

The above insert will insert all the portfolioscripid from one table to another......

Thanks amitpatel i got the answer thanks a lot........