Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: Creating a View results In a Temporary View... Why?

    Dear Sirs et Madames,
    Am trying to create a view that will provide me the data to create a population pyramid. However when I run the Create View script, it concludes successfully but tells me "NOTICE: view 'population_pyramid_1' will be a temporary view
    Query returned successfully with no result in 42 ms."

    But why is it a temporary view? The sql is as below:

    PHP Code:

    create 
    or replace view population_pyramid_1 AS   
        
    select     count(*),
        
    sex,
        
    extract('year' from jun30) as year,
        
    age_ranges.min
    from     rufiji_individual
    left join 
        rufiji_mortal
    on
    (rufiji_individual.uuid rufiji_mortal."individualUUID")
    cross join
        
    (select distinct date_trunc('year'"entryDate") + interval '5 months' interval '29 days' as jun30 from rufiji_individual where extract('year' from "entryDate") >= 1997 order by jun30) as distinct_years
    cross join
        age_ranges
    where 
    -- person was born before date 
    dob 
    distinct_years.jun30
    -- and died after date
    -- or is still alive 
    and ("deathDate" distinct_years.jun30
        
    or "deathDate" is null)
    -- and 
    is currently living in the survey area
    -- they entered before date 
    and "entryDate" <  distinct_years.jun30
    -- and left after date or are still living here 
    and     ("exitDate" >  distinct_years.jun30
        
    or "exitDate" is null)
    -- and 
    within age range 
    and age(distinct_years.jun30dob) > age_ranges.min
    and age(distinct_years.jun30dob) <= age_ranges.max

    group by age_ranges
    .minsexyear 
    order by age_ranges
    .minsexyear 
    BTW, age_ranges is another view (not temporary) in the database.

    Could anyone please assist me in finding out why this only creates a temporary view?

    Kind regards,
    Last edited by phoenixx; 11-22-10 at 04:34.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Check out the manual at
    http://www.postgresql.org/docs/current/static/sql-createview.html

    It explains your error:
    Quote Originally Posted by Postgres Manual
    If any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMPORARY is specified or not).

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've deleted the dupe in ANSI SQL forum. This is specific to the RDBMS.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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