Project

General

Profile

« Previous | Next » 

Revision 8136

Added by Aaron Marcuse-Kubitza almost 12 years ago

inputs/FIA/*/postprocess.sql: Avoid using :table, :table_str so that the commands in the script can also be run by pasting them into pgAdmin

View differences:

inputs/FIA/PLOT/postprocess.sql
1 
SELECT functions.to_global_col_names(:table_str);
1 
SELECT functions.to_global_col_names('"PLOT"');
2 2 

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "PLOT" RENAME "PLOT.STATECD"  TO ".STATECD" $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "PLOT" RENAME "PLOT.UNITCD"   TO ".UNITCD"  $$);


  		




  ......




  6
  6
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "PLOT" RENAME "PLOT.PLOT"     TO ".PLOT"    $$);


  		




  7
  7
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "PLOT" RENAME "PLOT.INVYR"    TO ".INVYR"   $$);


  		




  8
  8
  
    

  		




  9
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  9
  
    
SELECT functions.set_col_types('"PLOT"', ARRAY[


  		




  10
  10
  
    
  ('.STATECD' , 'integer')


  		




  11
  11
  
    
, ('.UNITCD'  , 'integer')


  		




  12
  12
  
    
, ('.COUNTYCD', 'integer')


  		




  ......




  16
  16
  
    

  		




  17
  17
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.ID"     UNIQUE ("PLOT.CN")                                            $$);


  		




  18
  18
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "PLOT" ADD CONSTRAINT "PLOT.unique" UNIQUE (".STATECD", ".UNITCD", ".COUNTYCD", ".PLOT", ".INVYR")$$);


  		




  19
  
  
    
SELECT functions.cluster_once(:table_str, '"PLOT.unique"');


  		




  
  19
  
    
SELECT functions.cluster_once('"PLOT"', '"PLOT.unique"');


  		












  

    
      inputs/FIA/COUNTY/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"COUNTY"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COUNTY" RENAME "COUNTY.STATECD"  TO ".STATECD" $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COUNTY" RENAME "COUNTY.UNITCD"   TO ".UNITCD"  $$);


  		




  5
  5
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COUNTY" RENAME "COUNTY.COUNTYCD" TO ".COUNTYCD"$$);


  		




  6
  6
  
    

  		




  7
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  7
  
    
SELECT functions.set_col_types('"COUNTY"', ARRAY[


  		




  8
  8
  
    
  ('.STATECD' , 'integer')


  		




  9
  9
  
    
, ('.UNITCD'  , 'integer')


  		




  10
  10
  
    
, ('.COUNTYCD', 'integer')


  		












  

    
      inputs/FIA/REF_PLANT_DICTIONARY/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"REF_PLANT_DICTIONARY"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" RENAME "REF_PLANT_DICTIONARY.SYMBOL_TYPE" TO ".SYMBOL_TYPE"$$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_PLANT_DICTIONARY" RENAME "REF_PLANT_DICTIONARY.SYMBOL"      TO ".SYMBOL"     $$);


  		




  5
  5
  
    

  		




  6
  6
  
    
-- Remove 56 duplicate symbols of type 'Old' (these are ambiguous)


  		




  7
  
  
    
DELETE FROM :table


  		




  
  7
  
    
DELETE FROM "REF_PLANT_DICTIONARY"


  		




  8
  8
  
    
WHERE ".SYMBOL" IN (


  		




  9
  9
  
    
    SELECT ".SYMBOL"


  		




  10
  
  
    
    FROM :table


  		




  
  10
  
    
    FROM "REF_PLANT_DICTIONARY"


  		




  11
  11
  
    
    WHERE ".SYMBOL_TYPE" = 'Old'


  		




  12
  12
  
    
    GROUP BY ".SYMBOL"


  		




  13
  13
  
    
    HAVING count(*) > 1


  		












  

    
      inputs/FIA/REF_UNIT/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"REF_UNIT"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_UNIT" RENAME "REF_UNIT.STATECD" TO ".STATECD"$$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_UNIT" RENAME "REF_UNIT.VALUE"   TO ".UNITCD" $$);


  		




  5
  5
  
    

  		




  6
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  6
  
    
SELECT functions.set_col_types('"REF_UNIT"', ARRAY[


  		




  7
  7
  
    
  ('.STATECD' , 'integer')


  		




  8
  8
  
    
, ('.UNITCD' , 'integer')


  		




  9
  9
  
    
]::functions.col_cast[]);


  		












  

    
      inputs/FIA/COND/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"COND"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COND" RENAME "COND.STATECD"          TO ".STATECD"      $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COND" RENAME "COND.UNITCD"           TO ".UNITCD"       $$);


  		




  ......




  9
  9
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COND" RENAME "COND.HABTYPCD1"        TO ".HABTYPCD"     $$);


  		




  10
  10
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "COND" RENAME "COND.HABTYPCD1_PUB_CD" TO ".HABTYP_PUB_CD"$$);


  		




  11
  11
  
    

  		




  12
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  12
  
    
SELECT functions.set_col_types('"COND"', ARRAY[


  		




  13
  13
  
    
  ('.STATECD' , 'integer')


  		




  14
  14
  
    
, ('.UNITCD'  , 'integer')


  		




  15
  15
  
    
, ('.COUNTYCD', 'integer')


  		




  ......




  36
  36
  
    

  		




  37
  37
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.ID"     UNIQUE ("COND.CN")                                                       $$);


  		




  38
  38
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "COND" ADD CONSTRAINT "COND.unique" UNIQUE (".STATECD", ".UNITCD", ".COUNTYCD", ".PLOT", ".INVYR", ".CONDID")$$);


  		




  39
  
  
    
SELECT functions.cluster_once(:table_str, '"COND.unique"');


  		




  
  39
  
    
SELECT functions.cluster_once('"COND"', '"COND.unique"');


  		












  

    
      inputs/FIA/REF_SPECIES/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"REF_SPECIES"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_SPECIES" RENAME "REF_SPECIES.SPCD"           TO ".SPCD"  $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_SPECIES" RENAME "REF_SPECIES.SPECIES_SYMBOL" TO ".SYMBOL"$$);


  		




  5
  5
  
    

  		




  6
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  6
  
    
SELECT functions.set_col_types('"REF_SPECIES"', ARRAY[


  		




  7
  7
  
    
  ('.SPCD', 'integer')


  		




  8
  8
  
    
]::functions.col_cast[]);


  		




  9
  9
  
    

  		












  

    
      inputs/FIA/TREE/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"TREE"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "TREE" RENAME "TREE.STATECD"  TO ".STATECD" $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "TREE" RENAME "TREE.UNITCD"   TO ".UNITCD"  $$);


  		




  ......




  9
  9
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "TREE" RENAME "TREE.SUBP"     TO ".SUBP"    $$);


  		




  10
  10
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "TREE" RENAME "TREE.SPCD"     TO ".SPCD"    $$);


  		




  11
  11
  
    

  		




  12
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  12
  
    
SELECT functions.set_col_types('"TREE"', ARRAY[


  		




  13
  13
  
    
  ('.STATECD' , 'integer')


  		




  14
  14
  
    
, ('.UNITCD'  , 'integer')


  		




  15
  15
  
    
, ('.COUNTYCD', 'integer')


  		




  ......




  22
  22
  
    

  		




  23
  23
  
    
-- ("STATECD", "UNITCD", "COUNTYCD", "PLOT", "INVYR", "CONDID", "SUBP", "TREE", "STATUSCD") is not ID


  		




  24
  24
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "TREE" ADD CONSTRAINT "TREE.ID" UNIQUE ("TREE.CN")$$);


  		




  25
  
  
    
SELECT functions.cluster_once(:table_str, '"TREE.ID"');


  		




  
  25
  
    
SELECT functions.cluster_once('"TREE"', '"TREE.ID"');


  		




  26
  26
  
    

  		




  27
  27
  
    
SELECT functions.create_if_not_exists($$CREATE INDEX "TREE.parent" ON "TREE" (".STATECD", ".UNITCD", ".COUNTYCD", ".PLOT", ".INVYR", ".CONDID", ".SUBP")$$);


  		












  

    
      inputs/FIA/REF_HABTYP_DESCRIPTION/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"REF_HABTYP_DESCRIPTION"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_HABTYP_DESCRIPTION" RENAME "REF_HABTYP_DESCRIPTION.HABTYPCD" TO ".HABTYPCD"     $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_HABTYP_DESCRIPTION" RENAME "REF_HABTYP_DESCRIPTION.PUB_CD"   TO ".HABTYP_PUB_CD"$$);


  		












  

    
      inputs/FIA/SUBPLOT/postprocess.sql
    
  





  1
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  1
  
    
SELECT functions.to_global_col_names('"SUBPLOT"');


  		




  2
  2
  
    

  		




  3
  3
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.STATECD"  TO ".STATECD" $$);


  		




  4
  4
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.UNITCD"   TO ".UNITCD"  $$);


  		




  ......




  7
  7
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.INVYR"    TO ".INVYR"   $$);


  		




  8
  8
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.SUBP"     TO ".SUBP"    $$);


  		




  9
  9
  
    

  		




  10
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  10
  
    
SELECT functions.set_col_types('"SUBPLOT"', ARRAY[


  		




  11
  11
  
    
  ('.STATECD' , 'integer')


  		




  12
  12
  
    
, ('.UNITCD'  , 'integer')


  		




  13
  13
  
    
, ('.COUNTYCD', 'integer')


  		




  ......




  18
  18
  
    

  		




  19
  19
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("SUBPLOT.CN")                                                  $$);


  		




  20
  20
  
    
SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE (".STATECD", ".UNITCD", ".COUNTYCD", ".PLOT", ".INVYR", ".SUBP")$$);


  		




  21
  
  
    
SELECT functions.cluster_once(:table_str, '"SUBPLOT.unique"');


  		




  
  21
  
    
SELECT functions.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');


  		












  

    
      inputs/FIA/REF_RESEARCH_STATION/postprocess.sql
    
  





  1
  1
  
    
-- contains the research station *and state name* for each state


  		




  2
  2
  
    

  		




  3
  
  
    
SELECT functions.to_global_col_names(:table_str);


  		




  
  3
  
    
SELECT functions.to_global_col_names('"REF_RESEARCH_STATION"');


  		




  4
  4
  
    

  		




  5
  5
  
    
SELECT functions.rename_if_exists($$ALTER TABLE "REF_RESEARCH_STATION" RENAME "REF_RESEARCH_STATION.STATECD" TO ".STATECD"$$);


  		




  6
  6
  
    

  		




  7
  
  
    
SELECT functions.set_col_types(:table_str, ARRAY[


  		




  
  7
  
    
SELECT functions.set_col_types('"REF_RESEARCH_STATION"', ARRAY[


  		




  8
  8
  
    
  ('.STATECD' , 'integer')


  		




  9
  9
  
    
]::functions.col_cast[]);


  		




  10
  10
  
    

  		












Also available in:  Unified diff