doubt in function

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • priyan
    New Member
    • Aug 2007
    • 54
    #1

    doubt in function

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

    I am having two tables
    [code=text]
    portfolioscrip
    portfolioscripd etails
    [/code]

    i have to insert into portfolioscripd etails that table is having a column called portfolioscripi d and portfolioscripd etailsid portfolioscripd etailsid is the auto generated column and portfoliscripid should be insert from portfolioscrip table
    [code=sql]
    CREATE OR REPLACE FUNCTION insertportfolio (pdate timestamp without time zone)
    RETURNS integer AS
    $BODY$
    declare
    id bigint;
    countid bigint;
    begin
    select into countid count(portfolio scripid) from portfolioscrip;
    for i in 1....countid loop
    select into id portfolioscripi d from portfolioscrip;
    insert into portfolioscripd etails (portfolioscrip id,date,bseopen ,bseclose,nseop en,nseclose)val ues(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;
    [/code]


    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
    Tags: None
    • amitpatel66
      Recognized Expert Top Contributor
      • Mar 2007
      • 2358
      #2
      Originally posted by priyan
      hi all,
      I am having a doubt of how to write a function for my situation.

      I am having two tables
      [code=text]
      portfolioscrip
      portfolioscripd etails
      [/code]

      i have to insert into portfolioscripd etails that table is having a column called portfolioscripi d and portfolioscripd etailsid portfolioscripd etailsid is the auto generated column and portfoliscripid should be insert from portfolioscrip table
      [code=sql]
      CREATE OR REPLACE FUNCTION insertportfolio (pdate timestamp without time zone)
      RETURNS integer AS
      $BODY$
      declare
      id bigint;
      countid bigint;
      begin
      select into countid count(portfolio scripid) from portfolioscrip;
      for i in 1....countid loop
      select into id portfolioscripi d from portfolioscrip;
      insert into portfolioscripd etails (portfolioscrip id,date,bseopen ,bseclose,nseop en,nseclose)val ues(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;
      [/code]


      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:

      [code=oracle]
      INSERT INTO portfolioscripd etails (portfolioscrip id,date,bseopen ,bseclose,nseop en,ns eclose)
      (SELECT portfolioscripi d,<substitute your pdate here>,0,0,0,0);
      [/code]

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

        Comment

        • priyan
          New Member
          • Aug 2007
          • 54
          #3
          Originally posted by amitpatel66
          Why you need to write a function here?? rather you can achieve this with a single insert statement:

          [code=oracle]
          INSERT INTO portfolioscripd etails (portfolioscrip id,date,bseopen ,bseclose,nseop en,ns eclose)
          (SELECT portfolioscripi d,<substitute your pdate here>,0,0,0,0);
          [/code]

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

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

            Comment

            Previous template Next
            Working...