Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Posts
    30

    Unanswered: Is It possible To Create 53 Views with a For Statment?

    Hi to All,

    I' d want to create 53 VIEW, one for every week of the year as the following, but I do not want to type 53 times the query in the file.sql, changing in every query the value of the column weeks from 1 to 53:

    Code:
    CREATE VIEW week8 AS
    SELECT fkid_reparto, SUM(tot)
    FROM my_tab
    WHERE weeks=extract(week from current_date)
    GROUP BY fkid_reparto;
    Perhaps the solution could be a function which auto increments the number of the week which it is impossible for the actual Knowledge of mine.

    Could SomeOne resolve the problem?

    If the solution is or is not trough a Function which use For Statement, which is the function code?
    Tanks in advance.
    Gianni Pinna.

  2. #2
    Join Date
    Dec 2012
    Posts
    43
    Do you have also 53 tables - one for every week? I don't believe ...

    So you need only one view too - not 53 views.

    In your view you only need an additional column "week".

    Then the SQL statement for week #8 looks like
    Code:
    SELECT * FROM v_week where week=8
    Additional information:
    the 1st of January of a year is not always week #1. Could be the week #53 of the last year.

  3. #3
    Join Date
    Dec 2012
    Posts
    30
    Hi Bibjet,

    Tank You for Your Kind replay

    My wish is not to create a command as this:

    Code:
    CREATE VIEW week8 AS
    SELECT fkid_reparto, SUM(tot)
    FROM my_tab
    WHERE weeks=8
    GROUP BY fkid_reparto;
    
    CREATE VIEW week8 AS
    SELECT fkid_reparto, SUM(tot)
    FROM my_tab
    WHERE weeks=7
    GROUP BY fkid_reparto;
    
    CREATE VIEW week8 AS
    SELECT fkid_reparto, SUM(tot)
    FROM my_tab
    WHERE weeks=6
    GROUP BY fkid_reparto;
    And so on, until week=53.


    I thought that perhaps it was possible a Pg/psql function of the type next,
    but I do not know the Pg/psql language
    Code:
    for (var  i=1 ; i<=53;  i++) 
    {
    QUERY (with week=i)
    }
    Tanks in advance.

    Gianni Pinna

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not clear on how having 53 different views helps you, when the query ought to run pretty fast and produce the same results. From a database managment perspective, having the 53 views is going to cause a lot more work than it could possibly save. I think that bibjet was trying to save you from making a common mistake that people make as they transition from row oriented application programming into set oriented database programming.

    The odds are good that the views can be created, but I can't see how it could be a good idea to do so. Please help me to understand.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I agree with Pat that this sounds like a strange idea. However you can do this if you want to:
    Code:
    do
    $body$
    declare 
      week_nr integer;
      stmt    text;
    begin
      week_nr := 1;
      while week_nr <= 53 loop
        stmt := 'create view week'||week_nr::text||
                ' as select fkid_reparto, SUM(tot) '||
                'from my_tab '||
                'where weeks = '||week_nr::text||
                ' GROUP BY fkid_reparto';
        execute stmt; 
        week_nr := week_nr + 1;
      end loop;
    end;
    $body$
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Dec 2012
    Posts
    30
    Hi Pat,
    tank You for Your Kind reply.
    In order to the solution of the remote end, I found more simple, not to declare the remote end, but to declare only the next end. This is the reason for wich it seems strange the idea of 53 WIEWS.
    Tank You for precious suggestion of Your :"the query ought to run pretty fast and produce the same results". Tank to You I could experience it.

    Hi Shammat,
    tank You for Your code. It is SIMPLE and PERFECT !!.
    I am studiing some aspects of it, for which I hope to have suggestions if it is needed.

    Sincerly
    Gianni Pinna.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •