imdb=> \d movieroles Table "public.movieroles" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- actorid | integer | | | movieid | integer | | | info_1 | text | | | info_2 | text | | | role | text | | | imdb=> \d List of relations Schema | Name | Type | Owner --------+------------------+----------+--------- public | actors | table | kkuzmin public | actors_id_seq | sequence | kkuzmin public | directors | table | kkuzmin public | directors_id_seq | sequence | kkuzmin public | imdbratings | table | kkuzmin public | moviedirectors | table | kkuzmin public | moviegenres | table | kkuzmin public | movieplots | table | kkuzmin public | movieroles | table | kkuzmin public | movies | table | kkuzmin public | movies_id_seq | sequence | kkuzmin public | twittergenres | table | kkuzmin public | twitterratings | table | kkuzmin public | twitterusers | table | kkuzmin (14 rows) imdb=> \d actors Table "public.actors" Column | Type | Collation | Nullable | Default ---------+------------------------+-----------+----------+------------------------------------ id | integer | | not null | nextval('actors_id_seq'::regclass) atype | character varying(10) | | | name | character varying(127) | | | surname | character varying(127) | | | Indexes: "actors_pkey" PRIMARY KEY, btree (id) imdb=> select count(*) from movieroles ; count -------- 265107 (1 row) imdb=> \d movies Table "public.movies" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+------------------------------------ id | integer | | not null | nextval('movies_id_seq'::regclass) title | text | | not null | full_name | text | | | type | character varying(20) | | | ep_name | text | | | ep_num | text | | | status | text | | | years | text | | | movieyear | integer | | | name | character varying(150) | | | Indexes: "movies_pkey" PRIMARY KEY, btree (id) "movies_idx" btree (title, id) imdb=> SELECT imdb-> m.title imdb-> FROM imdb-> movies m imdb-> , movieroles mr imdb-> , actors a imdb-> WHERE imdb-> a.id = mr.actorid imdb-> and m.id = mr.movieid imdb-> and a.name = 'Kevin (I)' imdb-> and a.surname = 'Bacon' ; title ------------------------------------- A Few Good Men (1992) Animal House (1978) Apollo 13 (1995) Balto (1995) Black Mass (2015/I) Crazy, Stupid, Love. (2011) Death Sentence (2007) Flatliners (1990) Footloose (1984) Friday the 13th (1980) Frost/Nixon (2008) Hollow Man (2000) JFK (1991) Murder in the First (1995) Mystic River (2003) Planes, Trains & Automobiles (1987) R.I.P.D. (2013) Sleepers (1996) Stir of Echoes (1999) Super (2010/I) The Air I Breathe (2007) The River Wild (1994) X-Men: First Class (2011) The Woodsman (2004) Tremors (1990) Wild Things (1998) (26 rows) imdb=> SELECT DISTINCT imdb-> a1.name, a1.surname imdb-> FROM imdb-> actors a imdb-> , movieroles mr --- movies with KB imdb-> , movieroles mr1 -- actors in movies for mr1 imdb-> , actors a1 imdb-> WHERE imdb-> a.id = mr.actorid imdb-> and a.name = 'Kevin (I)' imdb-> and a.surname = 'Bacon' imdb-> and mr1.movieid = mr.movieid imdb-> and mr1.actorid = a1.id imdb-> and mr1.actorid <> a.id ; name | surname ---------------------------------+-------------------- Aaron | Dorsey Aaron | Sorkin Aaron (IV) | Snyder Abigail | Friend Ace Antonio | Hall Ada | Lynn Adam | Cozens Adam | Karst Adam | LaFramboise Adam | Zalt Adam (I) | Long Adam (I) | Nelson Adam (I) | Scott Adam J. | Freeman Adlai | Stevenson Adriano | Masciarelli Adrienne | King Aeryk | Egan Afram Bill | Williams Agnes | Aurelio Ahmed | Bakhsh Aida | Turturro Aisha | Tyler Alan | Haufrect Alan | Resnic Alan (I) | Donnes Alan (IV) | Gray Albert | Miranda Albert | Talbot Albie | Cullen Alec | Gifford Alejandro (I) | de la Peña Alex | Bookston Alex | Rodine Alex | Terminel Alex | Wexo Álex (II) | González Alexander (III) | Cook Alexandra | Creteau Alexandria | Cree Algerita | Wynn Alice Marie | Sheehan Alison | Trouse Allen | Dulles Allison Pratt | Davis Alvin | Spicuzza Alyson | Muzila Alyssa Mae | Walsh Amanda | Blattner Amanda (I) | Borden Ami | Haruna Amy | Rochelle Amy (I) | Long Amy (I) | Meyers Analeigh | Tipton Andre | Royo Andre | Wiggins Andrea-Nichole | Olivas Andrea (I) | Russell Andrei | Gromyko Andrei | Zayats Andrew | Blesser Andrew | Deichman Andrew | Gianelli Andrew | Lipschultz Andrew | Mackin Andrew J. | Hentz Andrew Jackson | Murphy Andy | Callaghan Andy | Milder Andy (I) | Garcia Angel | Connell Angela | Paton Angela (I) | Rago Angelis | Alexandris Angella | Kaye Angus G. | Wynne III Ann | Strub Anna | Mikaels Annabelle | Wallis Anne (VIII) | James Anthony | Celona Anthony | Giaimo Anthony | Molinari Anthony | Sinopoli Anthony | Taurasi Anthony | Wozniak Anthony (I) | Ramirez Antoni | Corone Antonio | Polk Antony | Acker Aqeel | Hasan Ari | Graynor Ari | Lehman Ariana | Richards Ariana | Ruckle Ariel | Lazarus Arne | Starr Arthur | Darbinyan Arthur | Hiou Arthur | Rosenberg Arthur | Senzy Arthur | Yon Aseneth | Jurgenson Ashley (IV) | Jackson Ashley C. | Coombs Aunt | Dot Austin | O'Brien Austin | Tichenor Ava | Cooper B.J. | Martin Barry | Squitieri Barry (I) | Chambers Bates | Wilder Baxter | Harris Beata | Pozniak Ben | Bode Ben | Marley Ben | Pauley Ben | Slack Ben | Tanguay Ben (I) | Hammer Ben (I) | Stein Ben (II) | Sloane Benedict | Cumberbatch Benjamin | Dane Benjamin | Manoli Benjamin | Mouton Benjamin (I) | Bratt Berglind | Jonsdottir Bernard | Madrid Beth | Goddard Beth | Grant Beth | Keener Beth | Littleford Betsy | Palmer Bibi | Besch Big (I) | Al Bill | Barretta Bill | Bolender Bill | Erwin Bill | Haims Bill | Milner Bill | Mootos Bill | Paxton Bill | Pickle Bill (I) | Camp Bill (I) | Gould Bill (I) | Murray Bill (I) | Thorpe Bill (II) | Bailey Bill (III) | Richards Bill (VIII) | Porter Billy | Atchison Billy | Crudup Billy | Meleady Billy (II) | Malone Blackstone | Jacquelinn Bob | Dio Bob | Gunton Bob | Orwig Bob | Shuttleworth Bob (I) | Hoskins Bobbi D. | Laird Bobby | Hargis Bobby | Prochaska Bonnie | Keen Boyd | Drew Brad | Dourif Brad | Pitt Brad | Renfro Brad (I) | Carter Brad R. | Douglas Brandon | Belknap Brandyn | Cross Brendan | Fehr Brendan | Fraser Brendan | Stack Brett (I) | Cullen Brett (II) | Murphy Bretton | Manley Brian | Doyle-Murray Brian | Frates Brian | Leckner Brian | Markinson Brian | Van Kay Brian | Wimmer Brian (I) | Donahue Brian (IX) | Christopher Brian A. | White Brian L. | McCarty Brian Patrick | Butler Bridget | Fonda Bruce | Gelb Bruce | McGill Bruce (I) | Bonnheim Bruce (I) | Wright Bruno | Kirby Bryce Dallas | Howard Bundy | Chanock Cabran E. | Chamberlain Cage S. | Johnson Caitlin | McCollister Caitlin (III) | Thompson Caleb Landry | Jones Cameron | Bowen Cameron | Thor Camille (II) | Abelow Candy | Ibarra Carl | Rooney Carl Gabriel | Yorke Carlos (I) | Leon Carlos Besse | Peres Carmen | Trevino Carmine | Parisi Carol | Farabee Carol (I) | Bruce Carolina | McCullough Caroline | Crosthwaite-Eyre Caroline | Dolan Caroline (I) | Kennedy Carolyn T. | Wright Carrie | Snodgress Carrie Ann | Quinn Carrie Courtney | Crain Cary | Christiansen Cary 'Big Shug' | Guy Caryn | Ruby Casandra | Brooks Casey | Pieretti Casey (V) | Kelly Catherine | Kresge Cayden (I) | Boyd Cayley | Bell Cecelia | Specht Cecilia (I) | Suárez Celeste | Oliva Celine | du Tertre Cesare | Danova Chace | Frazier Chalon | Williams Charles | Boswell Charles | Brehm Charles | Cabell Charles | Cyphers Charles | de Gaulle Charles | Tyner Charles L. | Campbell Charley | Broderick Charlie | Alejandro Charlie | Hartsock Charlotte (I) | Stewart Chauntal | Lewis Chaunty | Spillane Cheryl | Penland Cheryl (I) | Howard Cheryl (II) | McMahon Chester | Thompson Chet | Huntley Childrens Choir Kids Connection | Music Chino 'Fats' | Williams Chris (I) | Ellis Chris (I) | Fries Chris (I) | Penn Chris (I) | Renna Chris (II) | Robinson Chris (III) | Miller Chris (III) | Palermo Chris (III) | Whitney Chris (LXXI) | Thompson Chris (V) | Rodriguez Chris (VII) | Moore Christian | Clemenson Christian | Pitre Christian | Stolte Christian (I) | Alvarado Christian (I) | Cage Christian (I) | Slater Christina | Everett Christine | Bently Christine | Cavanaugh Christopher | Darga Christopher | De Stefano Christopher | Jude Christopher | Kosiciuk Christopher | Meister Christopher | Privette Christopher | Villar Christopher (I) | Guest Christopher (I) | Haskell Christopher E. | Cooper Christopher John | Fields Christopher S. | Porter Chuck (I) | Low Chuck (II) | Kelley Ciaran (I) | Crawford Clara Hopkins | Daniels Clark | Gregg Claudia | Cervantes Claudio | Jacobellis Clint | Howard Clint (I) | Hill Clinton | Blackburn Codie | Scott Cody | Sousa Cole S. | McKay Colin | Nicholas Colin Lee | Jones Colleen | Dengel Colleen (XXII) | Kelly Columbia | Dubose Connor | Day Connor | Paolo Conor | McGrath Conor (I) | O'Farrell Conrad | Bachmann Conrad | Meertins Jr. Corey | Stoll Corey (I) | Johnson Corin | Grant Cory | Pendergast Craig | Deroche Craig C. | Terlino Cristina (I) | Moody Crystal (I) | Reed Cuba | Gooding Jr. Curtis | Eames Curtis | LeMay Cydnee | Taylor Cyndal | Todd Cynthia | Bassham D.J. (II) | Pierce Dakota | Johnson Dakota (I) | Shepard Dale | Dye Dale (I) | Beasley Dale F. | Appel Dalton | Dearborn Damien | Di Paola Dan | Andreiu Dan (I) | Butler Danae | Nason Daniel | Delevin Daniel | Lowney Daniel | Mandehr Daniel | Niswander Daniel (II) | Nix Danielle (IV) | Cooper Danny | DeMiller Danny | Kovacs Danny | Mastrogiorgio Danny (I) | Mann Danny (II) | Connelly Danny (II) | Cosmo Daphne | Rubin-Vega Darbi | Gwynn Darcel White | Moreno Darin | MacEachern Darius A. | Sultan Darryl (I) | Cox Dash | Mihok Dasha | Mironova Dave (XXVII) | Johnson Dave Zee | Garison David | Agranov David | Bowe David | Buck David | De Beck David | Orosz David | Pulson David | Raiport David | Riccio Jr. David | Strathairn David | Struffolino David | Valenza David | Weindel David (I) | Andrews David (I) | Benn David (I) | Crow David (I) | Harbour David (I) | Kelsey David (I) | Vogt David (IV) | Boston David (V) | Conley David Alan | Grier David C. | Roehm Sr. David F. | Powers David Hall | Page David J. | Cummins David J. | Curtis David Joseph | Martinez David M. | Shoup David Michael | Sterling David Ross | Paterson Davide (III) | Leone Dawn | Morgan-Keefe Dean H. | Huh Debbie | Adkins Deborah | Goomas Deborah | Thompson Duda Deborah | Torchio Deborah (I) | Frazier Debra S. | Hayes Declan | Mulvey Dede | Latinopoulos Demetri | Goritsas Demi | Moore Denise (I) | Richards Dennis | Keiffer Dennis | Pappalardo Dennis (I) | Garr Dennis (I) | Neal Desiree | Markella Devin | Ratray Devon | Coull Diana | Afonso Diana | Castle Diana | Douglas Diana (VIII) | García Diane | Delano Diane | Nieman Diane (I) | Adams Diane (I) | Curran Dianne | Wiest Dicky | Eklund Jr. Dillon | Neaman Dillon | O'Donnell Dominick | LaBanca Dominika | Van Santen Don | Creech Don | Mac Don (I) | Hewitt Donald | Sinden Donald (I) | Sutherland Donna Glee | Reim Donny | Soares Dora | Sexton Dorothy | Kobs Doug (I) | Jackson Doug (V) | Reed Douglas | Dirkson Douglas | Kenney Douglas | Rye Douglas (I) | Cunningham Douglas (II) | Bennett Douglas W. | Randall Douglass | Bowen Flynn Drew | Eliot Duane | Grey Duane | Sharp Duncan B. | Putney Dustin | Hoffman Dwight D. | Eisenhower Dylan (I) | Baker E.J. | Morris Earl (I) | Warren Earle | Cabell Ed (I) | Harris Ed (I) | O'Keefe Eddie | Bowz Eddie | Napolillo Eddie | Troy Eddie Bo | Smith Jr. Edi | Gathegi Edie | McClurg Edrick | Browne Eduardo | Yáñez Eduardo (I) | Victoria Edward | Asner Edward G. | Lansdale Edwin | Neal Ehrin | Marlow Elaine Victoria | Grey Eli | Wallach Elinore | O'Connell Elisabeth | Shue Eliza | Elkington Eliza | Winwood Eliza (I) | Roberts Elizabeth | Dings Elizabeth | Gorcey Elizabeth | Winwood Elizabeth (I) | Hoffman Elizabeth (III) | Huff Ellen | McElduff Ellen | Page Eloy | Casados Elva | Morgan Embeth | Davidtz Emile | Hirsch Emilio | Savinni Emily (I) | Estes Emily Ann | Lloyd Emma (III) | Stone Emmalyn | Anderson Emmy | Rossum Endre | Hules Eric | Ferraz Eric | Sevareid Eric A. | Vicini Eric Bruno | Borgman Erica | McDermott Erica | Sorrentino Erick | Vinther Erik | Golda Erin | Reinhard Ernest | Rogers Sr. Errol | McLendon Ethel (II) | Kennedy Eugene | Byrd Eugene (III) | McCarthy Éva | Magyar Evan | Brooder Evan (I) | Parke Evan (II) | Silverman Eve | Brenner Eve (I) | Curtis Eve (II) | Evelina | Fernández Fervio | Castillo Fidel (I) | Castro Finn | Carter Floriane | Miniscloux Floyd | Richardson Flynn | Welles Forest | Whitaker Forry | Buckingham Frances Lee | McCain Francis Gary | Powers Frank | Cavestani Frank | Inzerillo Frank | Langella Frank | Ridley Frank | Welker Frank (I) | Medrano Frank (I) | Whaley Frank (III) | Reynolds Frankie | Imbergamo Franz | Strassmann Fred | Franklin Fred | Simonds Fred (I) | Ward Freddy | Bouciegues Gabriel | Geer Gabriel | Jarret Gaetano | Lisi Gail | Cronauer Garrett | Hedlund Garrick | Hagon Gary | Galone Gary | Grubbs Gary | McNerney Gary | Oldman Gary | Roscoe Gary | Sinise Gary | Taggart Gary (I) | Ballard Gary (I) | Carter Gary (I) | Palmer Gary (I) | Riley Gary (IV) | Greenberg Gary A. | Hecker Gary Lee | Davis Gavin | Grazer Gayle (I) | Scott Gene | Farber Gene | Pack Gene | Whittington Geoff | Whitman Geoffrey | Blake Geoffrey | Nauffts Geoffrey | Wigdor Georg | Nikoloff George | Belanger George | Georgiadis George | Ivey George | Petrie George (I) | Maguire George (I) | Vincent George (II) | Kelly George (IV) | Anderson George J. | Vezina George R. | Robertson Georgia | Hurd Georgia | Lyman Georgia | Treantafelles Gerald (I) | Ford Gerardo | Davila Gerry | Becker Gerry (II) | May Gianno | Caputo Gil | Glasgow Gina | LaMarca Gina | Menza Giovanni | Alabiso Glenn | Morshower Gonzo | Gonzalez Googy | Gress Graham (VI) | King Grant | Forsberg Grant (VI) | Goodman Grasie | Mercedes Greg | Grunberg Greg | Hain Greg | Kolpakchi Greg | Stechman Greg (II) | Ingram Greg (II) | Savage Gregg | Henry Gregory | Alpert Gregory | Barbon Gregory | Dolan Gregory (I) | Cox H.E.D. | Redford Haile | Selassie Hale | Boggs Hannah | Pilkes Harold G. | Herthum Harry | Caesar Harry (II) | Crosby Heather | Bloom Heather | La Bella Hector Atreyu | Ruiz Helen | Vick Helen (I) | Miller Henri | Alciatore Henry | Stram Henry | Tull III Henry Cabot (I) | Lodge Henry M. | Wade Herb | Ritts Herbert | Jefferson Jr. Holly | Burt Holly | Daniels Holly (II) | Weber Hope (I) | Davis Howard (II) | Rosen Hubert H. | Humphrey Hugh | Feagin Hugh | Jackman Hyowon K. | Yoo I.D. | Brickman Ian | Leson Iilana | B'tiste Illeana | Douglas Ilona | Margolis Ingrid | Oliu Irwin | Keyes Ivan (I) | Allen J-ray | J Parker | Kent J. Edgar | Hoover J. Patrick | McCormack J. Paul | Broadhead J.A. | Preston J.C. | Day J.J. (I) | Johnston J.P. | Valenti J.T. | Walsh J.T. (I) | Turner Jack | Lemmon Jack | Serino Jack (I) | Angel Jack (I) | Conley Jack (I) | Nicholson Jack (II) | Neary Jacqueline | Astbury Jacqueline (I) | Kennedy Jacquelyn Twodat | Jackson Jaime H. | Campos Jake (I) | Koenig James | Andelin James | DeVoy James | Lentzsch James | McAvoy James | Pickens Jr. James | Remar James | Rosin James | Widdoes James 'Izzy' | Whetstine James (I) | Daughton James (I) | Faulkner James (I) | Hong James (I) | Keane James (I) | MacArthur James (I) | Marshall James (I) | Ritz James (I) | Russo James (II) | Davison James (II) | Gifford James (II) | Gunn James (V) | Currie James (XIV) | Paul James A. | Swan James Anthony | Cotton James N. | Harrell Jamie | Donnelly Jamie | Grable Jamie | Mazareas Jamie (I) | Dunlop Jamie Christopher | White Jan | Munroe Janae Nicole | Caudillo Jane | Forrestal Jane (I) | Jenkins Janet | Bushor Janine | Barris Janneke | Williamson January (I) | Jones Jared | Milmeister Jared M. | Gordon Jarid | Faubel Jasen | Salvatore Jason | Beghe Jason | Dolley Jason | Flemyng Jason | Mayoh Jason | Patric Jason | Suhrke Jason (III) | Hair-Wynn Jason (III) | Kelly Jay | Amor Jay (I) | Bernard Jay (I) | Bird Jay (III) | White Jay O. | Sanders Jayne Costello | Goode Jean Speegle | Howard Jeanne | Lohnes Jeanne (II) | Hill Jeannine | Taylor Jebidiah R. | Dumas Jeff | Kamps Jeff | Martineau Jeff (I) | Bornstein Jeff (I) | Bridges Jeff (I) | Perry Jeff (III) | Stafford Jeff T. | Buco Jeffrey | Corazzini Jeffrey | Donovan Jeffrey | Kluger Jeffrey | Scaperrotta Jeneva | Ahearn Jenique | Torres Jenn | Gotzon Jennifer | Allswang Jennifer | Barbosa Jennifer | Hanley Jennifer (I) | Blanc Jennifer (II) | Morrison Jennifer (III) | Lawrence Jennifer (XVI) | Taylor Jenny | Mollen Jenny (I) | O'Hara Jeremiah | Kissel Jeremy (I) | Strong Jerry | Trupiano Jerry (I) | Douglas Jesse | Muson Jesse | Plemons Jesse (I) | Jackson Jesse (II) | Small Jessica | Diz Jessica | Rockwood Jessica (I) | Nagle Jillian | Wheeler Jim | Keisler Jim | Meskimen Jim | Ortlieb Jim | Youngs Jim (I) | Carter Jim (I) | Cummings Jim (I) | Garrison Jim (I) | Gough Jim (I) | Lovell Jim (I) | Smith Jim (II) | Boyd Jim (III) | Ford Jim (VIII) | Powers Jim (XXVI) | White Jim (XXXIII) | White Jimmie F. | Skaggs Jimmy | Heck Jimmy | Hoffa Jimmy | Jules Jimmy | McCarthy Jimmy | Wynn Jimmy C. | Black Jimmy Joe | Maher Jimmy P. | Wong Jo (I) | Anderson Joan Seuora | Morales Joann | Taylor Joanne | Brooks Jodie | Farber Joe | Fedio Joe | Pesci Joe | Spano Joe | Urla Joe (II) | Perrino Joe (II) | Stapleton Joe (III) | Cali Joe Jafo | Carriere Joel | Dunham Joel | Edgerton Joel | Fearon Joel (I) | Arsenault Joey | Hazinsky Joey | King Joey | Slotnick Joey | Vacchio John | Belushi John | Candy John | Cho John | Di Benedetto John | Duda John | Ferus John | Gigliotti John | Kennedy Jr. John | Larroquette John | Moio John | Perryman John | Pungitore John | Seitz John | St. Paul John (I) | Bishop John (I) | Chancellor John (I) | Connally John (I) | Doman John (I) | Dullaghan John (I) | Fink John (I) | Finnegan John (I) | Franchi John (I) | Goodman John (I) | Goodwin John (I) | Kerry John (I) | Laughlin John (I) | Lithgow John (I) | Pappas John (I) | Reneau John (I) | Robert John (I) | Short John (I) | Slattery John (I) | Vernon John (I) | Wheeler John (II) | Macey John (III) | Dean John (III) | Freeman John (X) | Burke John B. | Wells John Benjamin | Martin John C. | Martin John C. (I) | Reilly John Carroll | Lynch John F. (I) | Kennedy John J. | Burke John J. | McCloy John Joseph | Lindsey John Lewis (II) | Puff John M. | Jackson John M. | Mathews John M. | Newman John Randolph | Jones John S. (I) | Davies John Sherman | Cooper John Timothy | Botka John W. | Lawson John William | Galt Johnny | Brink Johnny | Depp Johnny | O'Halloran Johnny | Pawlik Johnny | Roselli Jon | Bernthal Jon | Gould Jon (I) | Bruno Jon (I) | Joyce Jon (I) | Pierce Jon (III) | Olson Jonah | Bobo Jonathan | Cornett Jonathan | Sawicki Jonathan | Togo Jonathan | Winkler Jonathan (I) | Davila Jonathan (I) | Tucker Jordan (I) | Garrett Jordan (III) | Lloyd Jorge (II) | Fernández José Ramón | Rosario Joseph | Attanasio Joseph | Culp Joseph | Lucas Joseph | Mazzello Joseph | Nadell Joseph (I) | Cole Joseph (I) | Richards Joseph (III) | Oliveira Joseph (IV) | Richardson Joseph P. | Barrett Joseph P. | Kennedy Joseph Quinn | Simpkins Josette | Oberton Josh | Brolin Josh | Flaum Josh | Groban Josh | Sussman Josh (I) | Cohen Josh (II) | Gunderson Joshua | Koopman Joshua | Malina Joshua | Rudoy Joshua | Sternlicht Joshua (I) | Daniel Joshua (XX) | Brown Juan | Maria Jr. Juan-Carlos | Guzman Juan (XI) | Herrera Judith | Belushi-Pisano Judith (I) | Roberts Jules | Bergman Julia (I) | Roberts Julianna | Guill Julianne | Moore Julianne | Nicholson Julie | Delpy Julie | Rowen Julie (I) | Warner Julie A. | Herbert Julie Ann | Dawson Julie H. | Morgan Juliette | Brewer Julius | Varnado Junior (XXII) | Juno | Temple Justin | Halliwell Justin | Villalta Justin | Wheelon K.K. | Dodds Kachina | Dechert Kaine Bennett | Charleston Kamron | Leal Kanin (I) | Howell Karen | Meisinger Karen | Stella Karen | Stockerl-Goldstein Karen | Vaccaro Karen (I) | Allen Karen (I) | Martin Kari | Wuhrer Karl carlo | Green Karolina | Wydra Kate | Jurdi Kate Jennings | Grant Katerina | Kopel Katherine | Denning Kathleen | Quinlan Kathleen (III) | Dolan Kathryn | Erbe Kathryn | Shasha Kathryn A. | Beauchamp Kathy | LaShay Berenson Katrine | De Candole Katy | Dunham Keith | MacKechnie Keith (III) | Wilson Kelli (I) | Scott Kelly | Preston Kelly (I) | Hu Ken | Cheeseman Ken (I) | Kemp Ken (I) | Tipton Ken (III) | Murray Ken L. | Parker Kendrick | Cross Kenneth (I) | White Kent | Kasper Kesha | Reed Kevin | Costner Kevin (I) | Beard Kevin (I) | Chapman Kevin (I) | Conway Kevin (I) | Dunn Kevin (I) | Pollak Kevin (I) | Rice Kevin (I) | Woods Kevin (II) | Ash Kevin D. | Atkinson Kevin P. | Kearns Kevin Richard | Sullivan Kevin Scott | Greer Kiefer | Sutherland Kieran Patrick | Campbell Kilo | Alexander Kim | Genelle Kim (I) | Dickens Kim (I) | Jensen Kimberly | Evan Kimberly | Giardino Kimberly | Lamaze Kimberly (I) | Robin Kimberly (I) | Scott Kimmie | Johnson Kirsten | Grimes Konstantine | Kurelias Kortney | Adams Kris (III) | Williams Kristen | Crociati Kristina | Hare Kristina | Sipes Kristina (I) | Hughes Krystal | Mayo Kurt | DeVille Kyle | Viveiros Kyra | Sedgwick Lady Bird | Johnson Laila (I) | Robins Laird | Macintosh Lance | Brady Lance | Greene Lance | Norris Larry | Eudene Larry | Ludwig Larry | Nazimek Larry | Neumann Jr. Larry | Romano Larry (I) | Hankin Larry (I) | Melton Larry (XII) | Williams Larry Joe | Campbell Lasco | Atkins Laura | Kirby Laura | Linney Laura | McDavid Laura | Pizzuti Laura (II) | Holloway Laurel | Coppock Laurel | Whitsett Lauren | Caputo Lauren | Laperriere Lauren (XII) | Stone Laurence | Belcher Laurence | Fishburne Laurie | Metcalf Laurie | Wallace Laurie (I) | Bartram Laurie Jenkins | Jones Lawrence (I) | Lowe Leah | Kilpatrick Leah | Lofton Lee | Jerrum Lee (I) | Mathis Lee Anne | Matusek Leigh | Whannell Lena | Evans Lennie | Loftin Lenny | Zundel Leo | Georgallis Leo | Geter Leonard | Redlich Leonid | Brezhnev Leonor | Anthony Les | Mahoney Lewis D. | Wheeler Lexi | Giovagnoli Liam | Daniels Lily | Winn Lin | Hultgren Linda | Cardellini Linda | MacEwen Linda | Palermo Linda Flores | Wade Lindsay | Soileau Lisa | Baur Lisa | Owen Lisa (I) | Lewis Lisa (XX) | Brown Liv | Tyler Liza | Lapira Liza | Weil Lloyd | Kaufman Logan Douglas | Smith Lola | Bates-Campbell Lolita | Davidovich London | Hall Lonnie (I) | Farmer Loren | Dean Lori | Singer Lorraine | Furst Lou | Meza Louie | Mejia Louis Steven | Witt Louisa | Marie Loy (I) | Lee Loys T. | Bergeron Lucas | Till Lucia | Vecchio Ludger | Pistor Ludwig | Erhard Luke (VII) | Ryan Lulie (I) | Newcomb Lunara (I) | Devers Lusia | Strus Lyman (I) | Ward Lyndon (I) | Johnson Lyne | Odums Lynne | Marta M Charlotte | Cheatham Mackenzie | Hawe Maeve (I) | Power Maggie | Grant Maggie | Kimberley Magnus | Bjornsson Maija | Polsley Malcolm | X Manajhjanihe | Royalle Manny | Suárez Marc | Carver Marc | Macaulay Marc | Vos Marc (I) | McClure Marchello | Stewart Marcia | Reider Marcia Gay | Harden Marco | Perella Marco (I) | Greco Margo | Peace Margot | Rose Maria | Deasy Maria (I) | Mason Marianne | Chambers Mariano (II) | Mendoza Marie | Del Marco Marie-Alise | Recasner Marilyn | Dodds Frank Marilyn (II) | Lovell Marinko | Radakovic Mario | Jimenez Marios (II) | Marisa | Tomei Marisa (III) | Miller Mark | Burzenski Mark | Cheek Mark | De Alessandro Mark | Falvo Mark | McKeel Mark | Metcalf Mark | Simich Mark | Strohman Mark (I) | Finney Mark (I) | Nelson Mark (I) | O'Leary Mark (I) | Wheeler Mark (III) | Mahoney Mark D. | Newman Mark Edward | Walters Mark Jeffrey | Miller Marshall | Berenson Martha (I) | Smith Martin | Ferrero Martin | Sheen Martin Luther | King Mary | Klug Mary | Moorman Mary | Randle Mary | Rocco Mary | Testa Mary | Wexler Mary-Louise | Parker Mary (II) | Mackey Mary B. | McCann Mary Claire | Winwood Mary Ethel | Gregory Mary Kate | Schellhardt Mary Kay | Cook Mary Louise | Weller Matt | McColm Matt | Soscia Matt (I) | Craven Matt (I) | Dillon Matt (I) | O'Leary Matt (III) | Moore Matthew | Macfadyen Matthew | Saks Matthew | Skomo Matthew | Spinale Matthew (I) | Lawrence Matthew (III) | Hoffman Matthew Landon | Sparks Matthew Michael | Goodall Matty | Blake Maud | Winchester Maureen | Hanley Maureen | Mendoza Max | Grodénchik Max Elliott | Slade Maxwell (I) | Taylor Meadow | Williams Megan (I) | James Megan (I) | Stewart Meghan | Broadhead Mekia | Cox Mel | Alejandro Mel (I) | Allen Melinda | Boczanowski Melissa Renee | Graehl Melodee | Bowman Merlyn | Sexton Meryl | Streep Mia | Kirshner Micah | Wilshire Michael | Balzano III Michael | Coons Michael | DeLorenzo Michael | Fassbender Michael | Gurievsky Michael | Harkins Michael | Ironside Michael | Merrins Michael | Ozag Michael | Telmont Michael | Tow Michael | Yebba Michael (I) | Burgess Michael (I) | Gross Michael (I) | McKean Michael (I) | McShane Michael (I) | Medeiros Michael (I) | Melvin Michael (I) | Peavey Michael (I) | Rooker Michael (I) | Shannon Michael (I) | Sheen Michael (I) | Skipper Michael (V) | McGovern Michael (V) | Shannon Michael (XXIII) | Doherty Michael Dan | Wagner Michael Dean | Walker Michael F. (II) | Murphy Michael J Long | Michael J. | Burg Michael P. (I) | Moran Michael Patrick | Breen Michael S. | Connolly Michael Steven | Costello Michael Steven | Swanson Michele (I) | Little Michelle | Gunn Michelle | Manhart Michelle (I) | McKee Mick (I) | O'Rourke Mickie | Banyas Miguel (I) | Delgado Mikaela | Hoover Mike | Bacarella Mike | Di Stasio Mike | Ferrante Mike | Longman Mike | Wendt Mike (I) | Judge Mike (I) | O'Malley Mikey | Kelley Miko (I) | Hughes Miles | Herter Mimi | Broadhead Minnie | Driver Miriam | Margolyes Misty | Dickinson Mohammed | Mossadegh Mollie | Milligan Monica | Polito Monica M. | Da Silva Morgan | Bernhard Morgan | Lily Morgane | Slemp Murray | Stokes Mykel | Chaves Naheem | Garcia Nancy | Kusley Nancy | Moran Nancy (I) | Duerr Natalia | DiGesare Nate (II) | Rubin Nathan | DiGesare Nathan | Fillion Nathan (II) | Scott Natsuko | Ohama Naysa Kaira | DaSilva Ned | Vaughn Neil | Fingleton Neil (I) | Armstrong Neil (I) | Summers Nellie | Connally Nelson | Oramas Nelson | Rockefeller Neve | Campbell Ngo Dinh | Diem Nicholas | Cairis Nicholas | Hoult Nicholas | Savoia Nicholas | Wyman Nick | Battiste Nick | Dash Nick (I) | Holmes Nick (I) | Scoggin Nicole | Niblack Nicole | Parrish Nicole | Vigil Nicole (I) | Gibson Nikita | Khrushchev Nili | Levi Noah | Craft Noah | Wyle Noel (I) | Cunningham Norma | Angelica Norman (II) | Davis Odin K. | Langford Olek | Krupa Olga | Fonda Oliver | Platt Olivia | Burnette Olivia (VII) | Davis Orlando | Gallegos Oscar (I) | Jordan Oscar (I) | Rowland Otis | Day Owen (III) | Burke Pablo (I) | Espinosa Papile | Aurora Pasquale | Cajano Pat | Nixon Pat (I) | McNamara Pat Pierre | Perkins Pati | Lauren Patrice | Lumumba Patricia | Belcher Patrick | Languzzi Patrick | Mickler Patrick | Tull Patrick (II) | Gleeson Patrick (III) | Shea Patrick (VIII) | Fox Patrick M. | Walsh Patty | McCormack Patty | Raya Paul | Bronk Paul | Cantelon Paul | Majors Paul | Mantee Paul (I) | Herman Paul (I) | McGillicuddy Paul T. | Taylor Paula | Lemes Paulo | Benedeti Penelope | Kaufer Penny L. | Moore Perry R. | Russo Pete | Rockwell Peter | Appel Peter | Gerety Peter | Lawford Peter | McRobbie Peter | Riegert Peter | Rini Peter | Sarsgaard Peter (I) | Brouwer Peter (I) | Crafts Peter (I) | Maloney Peter (I) | Stark Peter (IV) | Mahoney Peter (VII) | Morse Peter Austin | Noto Phil | Tavares Phil (I) | Collins Phillip L. | Willis Phyllis | Gordon II Piper Mackenzie | Harris Pope Paul VI | Preston | Strother Price | Carson Priscilla | Lauris Pruitt Taylor | Vince R. Bruce | Elliott R. Lee | Ermey Rachael | Owens Rade | Serbedzija Raena | Cassidy Rafael (I) | Siegel Rainn | Wilson Ralph | Tabakin Rance (I) | Howard Randall | Batinkoff Randall | Dudley Randy | Pelish Randy (I) | Means Rasika | Mathur Raul | Aranas Ray | Kendrick Ray | LePere Ray | McKinnon Ray | Quartermus Ray (I) | Redd Ray (I) | Wise Raymond | Buck Raymond | Hanis Raymone | Robinson Reba | McEntire Rebecca | Flinn Rebecca | Lasky Rebecca | Romijn Rebecca (I) | Bustamante Rebecca (I) | Hall Rebecca (I) | White Red (II) | Mitchell Reed | Martin Reed | Rudy Reese | Foster Reggie (I) | Lee Reginald | Farmer Reuben Larry | Elliot Rex | Everhart Rhet | Kidd Rhona | Mitra Ricardo | Sandoval III Rich | Ceraulo Rich | Hutchman Richard | Cotovsky Richard | DeAgazio Richard | Donelly Richard | Fike Richard | Kwong Richard | Nixon Richard | Rutowski Richard (I) | Herd Richard (I) | Marcus Richard (II) | Bissell Richard (II) | Pacheco Richard (II) | Russell Richard Steven | Horvitz Richie | Allan Rick | Eby Rick | Greenough Rick | LaCour Rick (I) | Burtt Rickland | Powell Rob | Zombie Robbi | Morgan Robbie | Rist Robert | Cray Robert | De Niro Robert | Kenney Robert | Knepper Robert | Masiello Robert | Pastoriza Robert | Phares Robert (I) | Deacon Robert (I) | Eames Robert (I) | Jayne Robert (I) | Kenney Jr. Robert (I) | McNamara Robert (I) | Mobbs Robert (I) | Trout Robert (I) | Wagner Robert (I) | Wahlberg Robert (II) | Elliott Robert (II) | Marshall Robert (X) | Lee Robert (XII) | Walsh Robert F. | Kennedy Robert J. | Groden Robert W. | Castle Robin | Brodsky Robson (I) | Vieira Rocco | Musacchia Rodrigo | Santacruz Roger | Corman Roger | Delgado Roger (I) | Mudd Roman | Rudenko Ron | Eldard Ron | Millkie Ron | Ostrow Ron | Rifkin Ron (I) | Jackson Ron (I) | Perlman Ron G. (I) | Young Ronald | Boone Ronald | von Klaussen Ronn | Carroll Rory | Cochrane Rory J. | Aylward Rosario | Varela Rose | Byrne Rose | Caiola Rose (I) | Kennedy Rosemary | Willis Rosemary (II) | Howard Roswell | Gilpatric Roxie M. | Frnka Roy | Barnitt Roy | Kellerman Ruary | O'Connell Russ | McGinn Russell | Towery Ruth | de Sosa Ruth | Maleczech Ryan | Gosling Ryan | Holihan Ryan (I) | MacDonald Ryan (I) | Patrick Ryan (I) | Reynolds Ryan Barry | McCarthy Ryken | Zane Sal | DiMino Sally | Vahle Sally (I) | Kirkland Sally Anne | Golden Salvador | Garcia Jr. Salvatore Paul | Piro Sam | Bacco Sam | Ervin Sam | Giancana Sam | Rockwell Sam | Stoneburner Sam (I) | Dalton Samantha | Durand Sandra | Dickinson Sanna | Vraa Sarabeth | Tucek Sarah | Holcomb Sarah | Krasner Sarah (I) | Bowles Sarah Jeanette | Taylor Sarah Jessica | Parker Sarah Michelle | Gellar Sasha | Pieterse Saverio | Guerra Scott | Bryson Scott | Krueger Scott (I) | Dow Scott (I) | Winters Scott (XII) | Anderson Sean (I) | Gunn Sean (I) | McCartin Sean (I) | Penn Sean (I) | Stone Sean (III) | Malone Sean (XXVII) | Brown Sean Patrick | Doherty Sean Patrick | Reilly Seth | Chitwood Seth | Reich Shah Mohammad Reza | Pahlavi Shane | Schoeppner Shane (IX) | Ryan Shaun | Gerardo Shauna (I) | O'Brien Shawn | Fitzgibbon Shawn | Fogarty Sheldon | Feldner Sherry | Ham-Bernard Shontelle | Thrash Simba | Dibinga Simon | Harkness Simon (I) | James Simone | Kessell Sissy | Spacek Sonny (I) | Saggese Sonny (IV) | King Spain | Logue Spencer (I) | Ross Spencer Treat | Clark Stacey Forbes | Iwanicki Stacy | Grooman Stanley (I) | White Stefan | Gierasch Stella | Cooper Stephane | Nicoli Stephanie | Sawyer Stephanie | Szostak Stephanie (III) | Atkinson Stephen | Blackehart Stephen | Furst Stephen | O'Neil Martin Stephen | Tobolowsky Stephen | Trouskie Stephen (I) | Bishop Stephen (I) | Hawke Stephen (I) | Kyle Stephen (II) | Curran Stephen Eugene | Walker Steve | Agee Steve | Altes Steve | Bernie Steve | Carell Steve | Kehela Steve | Longmuir Steve | Rasetta Steve | Ruge Steve | Winwood Steve (I) | Martin Steve (I) | Rankin Steve (I) | Reed Steve (I) | Rifkin Steve (II) | Perry Steve (III) | Flynn Steve F. | Price Jr. Steven | DeMarco Steven | Howitt Steven | Miley Stevie | Costa Stew | Replogle Stream | Stuart | Lafferty Stuart (I) | Nisbet Sunny (II) | Johnson Sunshine | Parker Susan | Bergeron Susan | Garibotto Susan | Kellermann Susan (I) | French Susan (I) | Isaacs Susan (I) | Willis Susanna | Musotto Suzanne | Gillies Suzanne | Prunty Suzanne (I) | Dengel Sydney | Lezama Sylvia | Vitrungs T. Bruce | Page T.J. (I) | Kennedy Talley | Singer Tania | Himelfarb Tania | Wagner Taylor | Goodall Taylor (I) | Nichols Ted | Bartsch Ted | Pennebaker Ted (I) | Kennedy Ted (I) | West Ted (II) | Otis Teresa | Mahoney Terri Gay | Ulmer Terry | Conforti Terry | Kinney Theo | Mayes Theresa (I) | Russell Thom | Barry Thomas | Derrah Thomas (I) | Crawford Thomas (I) | Fenske Thomas A. | Miller Thomas F. | Duffy Thomas J. | Larsen Thomas Michael | Flynn Thomas Philip | O'Neill Tiara | Parker Tim (I) | Matheson Tim (I) | McIntyre Tim (I) | Robbins Tim (IX) | Jacobs Tim (VII) | Whalen Tim J. | Smith Time | Winters Timothy (I) | Scott Tina | Clark Tingting | Yu Tobias | Segal Toby | Huss Toby (I) | Jones Todd | Louiso Todd | Stashwick Todd (I) | Hallowell Todd Ryan | Jones Todo | Segalla Toi | Svane Stepp Tom | Cruise Tom | Guiry Tom | Hanks Tom | Hulce Tom | Kurlander Tom | Mariano Tom | Savini Tom | Signorelli Tom | Woodruff Jr. Tom (I) | Kemp Tom (II) | Carrigan Tom (II) | Howard Tom (II) | Wood Tom (V) | Bullock Tom (V) | Driscoll Tom (XI) | Murphy Tomas | Milian Tomás | Goros Tommaso | Lagana Tommy (IV) | Burke Tommy Lee | Jones Tonia | Madenford Tony | Barr Tony | Genaro Tony | Plana Tony (I) | Curran Tony (III) | Guerrero Tony Ramos | Wright Tori (I) | Davis Tory | Christopher Tracy | Mulholland Tracy | Reiner Tracy (II) | McGrady Tracy K. | Shaffer Tressa | Thomas Tricia Clift | McCrone Troy (I) | Evans Valentine | Miele Van | Brockmann Van | Cameron Venya | Manzyuk Verna | Bloom Victor | Franko Victor | Galloway Victor | Kempster Victor (I) | Rivers Victor (III) | Wong Victoria | Bass Victoria | Vanderkloot Victoria (I) | Monroe Vincent | D'Onofrio Vittorio | Gassman Vivian | Khouri W. Earl | Brown Wally | Rose Walt | Gorney Walt | Hollis Walter | Altman Walter | Breaux Walter | Matthau Walter | von Huene Walter (I) | Cronkite Warren | Spottswood Wayne | Degan Wayne | Duvall Wayne | Parks Wayne | Tippit Wayne (I) | Knight Wendell (I) | Pierce Wendy | Worthington Wendy Lee | Avon Wes | Williams II Wil | Albert Wilfried | Hochholdinger Will | Lyman Willem | Oltmans William | Cleere William | Devane William | Katt William | Lucking William | Windom William | Xifaras William (I) | Baldwin William (I) | Larsen William (II) | Greer William (II) | Hall William (III) | Maier William (III) | Roberts William Ambrose | Kennedy William H. | Macy William K. | Butler Willie (I) | Adams Willie (I) | Minor Willy (I) | Brandt Wolfgang | Bodison Xander | Berkeley Yasiin | Bey Yorgo | Constantine Yuri | Naumkin Yuval | Bibi Yvette | Rachelle Zabeth | Russell Zach | Gilford Zachary | Ansley Zachary David | Cope Zachary Dylan | Smith Zayne | Emory Zeke | Mills Zoaunne | LeRoy Zoë | Kravitz (1788 rows) imdb=> SELECT imdb-> count(DISTINCT mr1.actorid) imdb-> FROM imdb-> actors a imdb-> , movieroles mr --- movies with KB imdb-> , movieroles mr1 -- actors in movies for mr1 imdb-> , actors a1 imdb-> WHERE imdb-> a.id = mr.actorid imdb-> and a.name = 'Kevin (I)' imdb-> and a.surname = 'Bacon' imdb-> and mr1.movieid = mr.movieid imdb-> and mr1.actorid = a1.id imdb-> and mr1.actorid <> a.id ; count ------- 1788 (1 row) imdb=> SELECT imdb-> count(DISTINCT mr2.actorid) imdb-> FROM imdb-> actors a imdb-> , movieroles mr --- movies with KB imdb-> , movieroles mr1 -- actors in movies for mr1 imdb-> , movieroles mr1m imdb-> , movieroles mr2 imdb-> WHERE imdb-> a.id = mr.actorid imdb-> and a.name = 'Kevin (I)' imdb-> and a.surname = 'Bacon' imdb-> and mr1.movieid = mr.movieid imdb-> and mr1.actorid <> a.id imdb-> and mr1m.actorid = mr1.actorid imdb-> and mr2.movieid = mr1m.movieid imdb-> and mr2.actorid <> a.id; count -------- 108132 (1 row) imdb=> WITH degree1 AS ( imdb(> SELECT DISTINCT imdb(> mr1.actorid imdb(> FROM imdb(> actors a imdb(> , movieroles mr --- movies with KB imdb(> , movieroles mr1 -- actors in movies for mr1 imdb(> WHERE imdb(> a.id = mr.actorid imdb(> and a.name = 'Kevin (I)' imdb(> and a.surname = 'Bacon' imdb(> and mr1.movieid = mr.movieid imdb(> and mr1.actorid <> a.id imdb(> ) imdb-> SELECT imdb-> count(DISTINCT mr2.actorid) imdb-> FROM imdb-> actors a imdb-> , movieroles mr --- movies with KB imdb-> , movieroles mr1 -- actors in movies for mr1 imdb-> , movieroles mr1m imdb-> , movieroles mr2 imdb-> WHERE imdb-> a.id = mr.actorid imdb-> and a.name = 'Kevin (I)' imdb-> and a.surname = 'Bacon' imdb-> and mr1.movieid = mr.movieid imdb-> and mr1.actorid <> a.id imdb-> and mr1m.actorid = mr1.actorid imdb-> and mr2.movieid = mr1m.movieid imdb-> and mr2.actorid <> a.id imdb-> and mr2.actorid not in (select actorid from degree1) ; count -------- 106344 (1 row) imdb=> CREATE OR REPLACE FUNCTION imdb-> degrees(inputname varchar, inputsurname VARCHAR) RETURNS varchar AS $$ imdb$> DECLARE imdb$> result varchar ; imdb$> curdegree int; imdb$> counter int ; imdb$> inputaid int ; imdb$> myrow RECORD ; imdb$> BEGIN imdb$> imdb$> SELECT a.id INTO inputaid imdb$> FROM actors a imdb$> WHERE a.name = inputname and a.surname = inputsurname ; imdb$> imdb$> CREATE TABLE degrees ( imdb$> actorid INT imdb$> , degree INT imdb$> ) ; imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , 1 imdb$> FROM imdb$> movieroles mr --- movies with KB imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> WHERE imdb$> mr.actorid = inputaid imdb$> and mr1.movieid = mr.movieid imdb$> and mr1.actorid <> inputaid ; imdb$> imdb$> curdegree = 1 ; imdb$> for counter in 1..4 loop imdb$> --- find the next degree! imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , curdegree+1 imdb$> FROM imdb$> movieroles mr --- movies with degree X people imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> , degrees d imdb$> WHERE imdb$> mr.actorid = d.actorid imdb$> and d.degree = curdegree imdb$> and mr1.movieid = mr.movieid imdb$> and mr1.actorid <> inputaid imdb$> and mr1.actorid not in (select actorid from degrees); imdb$> imdb$> curdegree = curdegree + 1; imdb$> end loop ; imdb$> imdb$> result = ''; imdb$> imdb$> for myrow IN select degree, count(*) as num FROM degrees imdb$> GROUP BY degree ORDER BY degree asc imdb$> loop imdb$> result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; imdb$> end loop ; imdb$> imdb$> DROP TABLE IF EXISTS degrees ; imdb$> imdb$> RETURN result ; imdb$> END ; imdb$> $$ LANGUAGE plpgsql ; CREATE FUNCTION imdb=> select degrees('Tom', 'Cruise'); Cancel request sent ERROR: canceling statement due to user request CONTEXT: SQL statement "INSERT INTO degrees SELECT DISTINCT mr1.actorid , curdegree+1 FROM movieroles mr --- movies with degree X people , movieroles mr1 -- actors in movies for mr1 , degrees d WHERE mr.actorid = d.actorid and d.degree = curdegree and mr1.movieid = mr.movieid and mr1.actorid <> inputaid and mr1.actorid not in (select actorid from degrees)" PL/pgSQL function degrees(character varying,character varying) line 35 at SQL statement imdb=> CREATE OR REPLACE FUNCTION imdb-> degrees(inputname varchar, inputsurname VARCHAR) RETURNS varchar AS $$ imdb$> DECLARE imdb$> result varchar ; imdb$> curdegree int; imdb$> counter int ; imdb$> inputaid int ; imdb$> myrow RECORD ; imdb$> BEGIN imdb$> imdb$> SELECT a.id INTO inputaid imdb$> FROM actors a imdb$> WHERE a.name = inputname and a.surname = inputsurname ; imdb$> imdb$> CREATE TABLE degrees ( imdb$> actorid INT imdb$> , degree INT imdb$> ) ; imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , 1 imdb$> FROM imdb$> movieroles mr --- movies with KB imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> WHERE imdb$> mr.actorid = inputaid imdb$> and mr1.movieid = mr.movieid imdb$> and mr1.actorid <> inputaid ; imdb$> imdb$> curdegree = 1 ; imdb$> for counter in 1..2 loop imdb$> --- find the next degree! imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , curdegree+1 imdb$> FROM imdb$> movieroles mr --- movies with degree X people imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> , degrees d imdb$> WHERE imdb$> mr.actorid = d.actorid imdb$> and d.degree = curdegree imdb$> and mr1.movieid = mr.movieid imdb$> and mr1.actorid <> inputaid imdb$> and mr1.actorid not in (select actorid from degrees); imdb$> imdb$> curdegree = curdegree + 1; imdb$> end loop ; imdb$> imdb$> result = ''; imdb$> imdb$> for myrow IN select degree, count(*) as num FROM degrees imdb$> GROUP BY degree ORDER BY degree asc imdb$> loop imdb$> result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; imdb$> end loop ; imdb$> imdb$> DROP TABLE IF EXISTS degrees ; imdb$> imdb$> RETURN result ; imdb$> END ; imdb$> $$ LANGUAGE plpgsql ; CREATE FUNCTION imdb=> select degrees('Tom', 'Cruise'); degrees ------------------ Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + (1 row) imdb=> CREATE OR REPLACE FUNCTION imdb-> degrees(inputname varchar, inputsurname VARCHAR, maxdegree INT) RETURNS varchar AS $$ imdb$> DECLARE imdb$> result varchar ; imdb$> curdegree int; imdb$> counter int ; imdb$> inputaid int ; imdb$> myrow RECORD ; imdb$> BEGIN imdb$> imdb$> SELECT a.id INTO inputaid imdb$> FROM actors a imdb$> WHERE a.name = inputname and a.surname = inputsurname ; imdb$> imdb$> CREATE TABLE degrees (actorid INT, degree INT) ; imdb$> INSERT INTO degrees values(inputaid, 0); imdb$> imdb$> curdegree = 0 ; imdb$> for counter in 1..maxdegree loop imdb$> --- find the next degree! imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , curdegree+1 imdb$> FROM imdb$> movieroles mr --- movies with degree X people imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> , degrees d imdb$> WHERE imdb$> mr.actorid = d.actorid imdb$> and d.degree = curdegree imdb$> and mr1.movieid = mr.movieid imdb$> and mr1.actorid not in (select actorid from degrees); imdb$> imdb$> curdegree = curdegree + 1; imdb$> end loop ; imdb$> imdb$> result = ''; imdb$> imdb$> for myrow IN select degree, count(*) as num FROM degrees imdb$> GROUP BY degree ORDER BY degree asc imdb$> loop imdb$> result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; imdb$> end loop ; imdb$> imdb$> DROP TABLE IF EXISTS degrees ; imdb$> imdb$> RETURN result ; imdb$> END ; imdb$> $$ LANGUAGE plpgsql ; CREATE FUNCTION imdb=> select degrees('Tom', 'Cruise', 2); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ (1 row) imdb=> select degrees('Tom', 'Cruise', 3); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + (1 row) imdb=> select degrees('Tom', 'Cruise', 4); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + Degree: 4 401 + (1 row) imdb=> select degrees('Tom', 'Cruise', 5); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + Degree: 4 401 + Degree: 5 58 + (1 row) imdb=> CREATE OR REPLACE FUNCTION imdb-> degrees(inputname varchar, inputsurname VARCHAR, maxdegree INT) RETURNS varchar AS $$ imdb$> DECLARE imdb$> result varchar ; imdb$> curdegree int; imdb$> counter int ; imdb$> inputaid int ; imdb$> myrow RECORD ; imdb$> BEGIN imdb$> imdb$> SELECT a.id INTO inputaid imdb$> FROM actors a imdb$> WHERE a.name = inputname and a.surname = inputsurname ; imdb$> imdb$> CREATE TABLE degrees (actorid INT, degree INT) ; imdb$> CREATE INDEX degrees_idx1 on degrees(degree,actorid); imdb$> CREATE INDEX degrees_idx2 on degrees(actorid); imdb$> INSERT INTO degrees values(inputaid, 0); imdb$> imdb$> curdegree = 0 ; imdb$> for counter in 1..maxdegree loop imdb$> --- find the next degree! imdb$> imdb$> INSERT INTO degrees imdb$> SELECT imdb$> DISTINCT mr1.actorid imdb$> , curdegree+1 imdb$> FROM imdb$> movieroles mr --- movies with degree X people imdb$> , movieroles mr1 -- actors in movies for mr1 imdb$> , degrees d imdb$> WHERE imdb$> mr.actorid = d.actorid imdb$> and d.degree = curdegree imdb$> and mr1.movieid = mr.movieid imdb$> EXCEPT imdb$> SELECT actorid, curdegree+1 from degrees; imdb$> imdb$> curdegree = curdegree + 1; imdb$> end loop ; imdb$> imdb$> result = ''; imdb$> imdb$> for myrow IN select degree, count(*) as num FROM degrees imdb$> GROUP BY degree ORDER BY degree asc imdb$> loop imdb$> result = result || 'Degree: ' || myrow.degree::varchar || ' ' || myrow.num::varchar || E'\n'; imdb$> end loop ; imdb$> imdb$> DROP TABLE IF EXISTS degrees ; imdb$> imdb$> RETURN result ; imdb$> END ; imdb$> $$ LANGUAGE plpgsql ; CREATE FUNCTION imdb=> select degrees('Tom', 'Cruise', 2); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ (1 row) imdb=> select degrees('Tom', 'Cruise', 3); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + (1 row) imdb=> select degrees('Tom', 'Cruise', 4); degrees ------------------ Degree: 0 1 + Degree: 1 3057 + Degree: 2 118082+ Degree: 3 27472 + Degree: 4 401 + (1 row) imdb=> \c baking You are now connected to database "baking" as user "kkuzmin". baking=> select * from episodes; id | title | firstaired | viewers7day | signature | technical | showstopper ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) baking=> CREATE TABLE episodes_version AS (SELECT id, title, viewers7day FROM episodes); SELECT 10 baking=> ALTER TABLE episodes_version ADD versionno INT ; ALTER TABLE baking=> UPDATE episodes_version SET versionno = 0 ; UPDATE 10 baking=> select * from episodes_version; id | title | viewers7day | versionno ----+--------------------------+-------------+----------- 1 | Biscuits | 9.55 | 0 2 | Cakes | 9.31 | 0 3 | Bread | 8.91 | 0 4 | Desserts | 8.88 | 0 5 | Spice | 8.67 | 0 6 | Pastry | 9.3 | 0 7 | Vegan | 9.54 | 0 8 | Danish | 9.69 | 0 9 | Pâtisserie (Semi-final) | 9.5 | 0 10 | Final | 10.34 | 0 (10 rows) baking=> update episodes set viewers7day = 11 where id = 10; UPDATE 1 baking=> select * from episodes; id | title | firstaired | viewers7day | signature | technical | showstopper ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 11 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) baking=> select * from episodes_version; id | title | viewers7day | versionno ----+--------------------------+-------------+----------- 1 | Biscuits | 9.55 | 0 2 | Cakes | 9.31 | 0 3 | Bread | 8.91 | 0 4 | Desserts | 8.88 | 0 5 | Spice | 8.67 | 0 6 | Pastry | 9.3 | 0 7 | Vegan | 9.54 | 0 8 | Danish | 9.69 | 0 9 | Pâtisserie (Semi-final) | 9.5 | 0 10 | Final | 10.34 | 0 (10 rows) baking=> CREATE OR REPLACE FUNCTION baking_trigger_f() RETURNS trigger AS $$ baking$> DECLARE baking$> curversion INT ; baking$> baking$> BEGIN baking$> SELECT max(versionno) INTO curversion baking$> FROM episodes_version WHERE id = OLD.id ; baking$> baking$> INSERT INTO episodes_version baking$> VALUES(OLD.id, OLD.title, OLD.viewers7day, curversion+1) ; baking$> baking$> RETURN NEW; baking$> END; baking$> $$ LANGUAGE plpgsql; CREATE FUNCTION baking=> \df List of functions Schema | Name | Result data type | Argument data types | Type --------+------------------+------------------+---------------------+------ public | baking_trigger_f | trigger | | func (1 row) baking=> update episodes set viewers7day = 12 where id = 10; UPDATE 1 baking=> select * from episodes_version; id | title | viewers7day | versionno ----+--------------------------+-------------+----------- 1 | Biscuits | 9.55 | 0 2 | Cakes | 9.31 | 0 3 | Bread | 8.91 | 0 4 | Desserts | 8.88 | 0 5 | Spice | 8.67 | 0 6 | Pastry | 9.3 | 0 7 | Vegan | 9.54 | 0 8 | Danish | 9.69 | 0 9 | Pâtisserie (Semi-final) | 9.5 | 0 10 | Final | 10.34 | 0 (10 rows) baking=> CREATE TRIGGER baking_trigger BEFORE UPDATE ON episodes baking-> FOR EACH ROW EXECUTE FUNCTION baking_trigger_f(); CREATE TRIGGER baking=> \df List of functions Schema | Name | Result data type | Argument data types | Type --------+------------------+------------------+---------------------+------ public | baking_trigger_f | trigger | | func (1 row) baking=> \dS episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) Triggers: baking_trigger BEFORE UPDATE ON episodes FOR EACH ROW EXECUTE FUNCTION baking_trigger_f() baking=> update episodes set viewers7day = 100 where id = 10; UPDATE 1 baking=> select * from episodes; id | title | firstaired | viewers7day | signature | technical | showstopper ----+--------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 100 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) baking=> select * from episodes_versions; ERROR: relation "episodes_versions" does not exist LINE 1: select * from episodes_versions; ^ baking=> select * from episodes_version; id | title | viewers7day | versionno ----+--------------------------+-------------+----------- 1 | Biscuits | 9.55 | 0 2 | Cakes | 9.31 | 0 3 | Bread | 8.91 | 0 4 | Desserts | 8.88 | 0 5 | Spice | 8.67 | 0 6 | Pastry | 9.3 | 0 7 | Vegan | 9.54 | 0 8 | Danish | 9.69 | 0 9 | Pâtisserie (Semi-final) | 9.5 | 0 10 | Final | 10.34 | 0 10 | Final | 12 | 1 (11 rows) baking=> \df List of functions Schema | Name | Result data type | Argument data types | Type --------+------------------+------------------+---------------------+------ public | baking_trigger_f | trigger | | func (1 row) baking=> DROP FUNCTION baking_trigger_f; ERROR: cannot drop function baking_trigger_f() because other objects depend on it DETAIL: trigger baking_trigger on table episodes depends on function baking_trigger_f() HINT: Use DROP ... CASCADE to drop the dependent objects too. baking=> \dS episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) Triggers: baking_trigger BEFORE UPDATE ON episodes FOR EACH ROW EXECUTE FUNCTION baking_trigger_f() baking=> DROP TRIGGER baking_trigger; ERROR: syntax error at or near ";" LINE 1: DROP TRIGGER baking_trigger; ^ baking=> DROP TRIGGER baking_trigger on episodes; DROP TRIGGER baking=> \dS episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> DROP FUNCTION baking_trigger_f; DROP FUNCTION baking=> \df List of functions Schema | Name | Result data type | Argument data types | Type --------+------+------------------+---------------------+------ (0 rows) baking=>