By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

doubt in function

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

I am having two tables
Expand|Select|Wrap|Line Numbers
  1. portfolioscrip
  2. portfolioscripdetails
  3.  
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
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION insertportfolio(pdate timestamp without time zone)
  2.   RETURNS integer AS
  3. $BODY$
  4. declare
  5. id bigint;
  6. countid bigint;
  7. begin
  8. select into countid count(portfolioscripid) from portfolioscrip;
  9. for i in 1....countid loop
  10. select into id portfolioscripid from portfolioscrip;
  11. insert into portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,nseclose)values(id,pdate,0,0,0,0);
  12. end loop;
  13. return 1;
  14. END
  15. $BODY$
  16.   LANGUAGE 'plpgsql' VOLATILE;
  17. ALTER FUNCTION insertportfolio(pdate timestamp without time zone) OWNER TO postgres;
  18.  

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
Sep 11 '07 #1
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
hi all,
I am having a doubt of how to write a function for my situation.

I am having two tables
Expand|Select|Wrap|Line Numbers
  1. portfolioscrip
  2. portfolioscripdetails
  3.  
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
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION insertportfolio(pdate timestamp without time zone)
  2.   RETURNS integer AS
  3. $BODY$
  4. declare
  5. id bigint;
  6. countid bigint;
  7. begin
  8. select into countid count(portfolioscripid) from portfolioscrip;
  9. for i in 1....countid loop
  10. select into id portfolioscripid from portfolioscrip;
  11. insert into portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,nseclose)values(id,pdate,0,0,0,0);
  12. end loop;
  13. return 1;
  14. END
  15. $BODY$
  16.   LANGUAGE 'plpgsql' VOLATILE;
  17. ALTER FUNCTION insertportfolio(pdate timestamp without time zone) OWNER TO postgres;
  18.  

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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,ns  eclose)
  2. (SELECT portfolioscripid,<substitute your pdate here>,0,0,0,0);
  3.  
The above insert will insert all the portfolioscripid from one table to another......
Sep 11 '07 #2

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO portfolioscripdetails (portfolioscripid,date,bseopen,bseclose,nseopen,ns  eclose)
  2. (SELECT portfolioscripid,<substitute your pdate here>,0,0,0,0);
  3.  
The above insert will insert all the portfolioscripid from one table to another......

Thanks amitpatel i got the answer thanks a lot........
Sep 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.