llm inside postgres

docker-compose.yml

  ollama:
    container_name: ollama_harmonizer
    image: ollama/ollama:0.6.2
    build:
      context: ./harmonizer
    ports:
      - "11434:11434"
    restart:
      unless-stopped

llm_prompt.sql

create or replace function llm_prompt(model text, prompt text) returns text as $$

    import os
    from ollama import Client

    client = Client(host='http://ollama_harmonizer:11434')

    response = client.chat(model=model, messages=[
      { 'role': 'user', 'content': prompt }
    ])

    plpy.info(response.message.content)

    return response.message.content

$$ language plpython3u;

select llm_prompt('gemma3:27b', 'why is the sky blue?');

You are a database administrator.
	    You have a database table named "old_land_parcel". It has the following columns:
	        parcel_number,account_number,land_use_code,land_use_description,zoning_code,num_buildings,num_stories,year_built,main_building_sqft,improvement_value,land_value,agriculture_value,total_value,last_sale_amount,owner_type,owner_name,legal_description,source_updated
	    You also have a table called new_land_parcel. It has the following columns:
	        source_level,fid,objectid,lrsn,parcel_id,extension,extbldgnum,ext_description,parent_parcel_id,routing_number,leg_description,prop_street,prop_city,prop_state,prop_zip,owner1,own_street,own_city,own_state,own_zip,imagepath,sketchpath,strtnum,snumsufx,strtdir,strtname,strttype,strtsufx,unitnum,strtcity,strtzip,acreage,square_feet,acreage1,square_feet1,section_plat,improvement_id,property_class,propclassdesc,property_use,neighborhood,finish_living_area,dwelling_year_built,dwelling_condition,grade,simplified_grade,bedrooms,full_baths,half_baths,fireplace_adj1,fireplace_indicator,central_air,story_height,attic_code,crawl_code,mkt_house_type,heating,roof_type,main_roof_material,wall_framing_material,main_exterior_cover,main_interior_finish,exterior_cover,framing,finished_attic,base_attic,bsmnt_fin_area,base_basement,attgarage_imp_size,detgarage_imp_size,open_porch,enclosed_porch,wood_deck,solarium,stoop,patio,canopy,balcony,bay_window,integrated_carport,carport,bath_house,boat_dock,boat_lift,boat_house,boat_slip,bulkhead,concrete_apron,fence_walls,gazebo,greenhouse,hottub,irrigation,paving,pier,piling,pool,riprap,sauna,shed,tennis_court,workshop,tower_summary,number_units,floor_count,parcels_norfolkgis_real_estate_data2_area,hvac,use_code,class,sprinkler,paving_sf,imp_val1,land_market_val1,total1,eff_year1,imp_val2,land_market_val2,total2,eff_year2,imp_val3,land_market_val3,total3,eff_year3,imp_val4,land_market_val4,total4,eff_year4,imp_val5,land_market_val5,total5,eff_year5,pxfer_date1,owners1,grantee1,consideration1,docnum1,deed_book1,deed_page1,transtype1,deedtype1,pxfer_date2,owners2,grantee2,consideration2,docnum2,deed_book2,deed_page2,transtype2,deedtype2,pxfer_date3,owners3,grantee3,consideration3,docnum3,deed_book3,deed_page3,transtype3,deedtype3,quart_imp,quart_land,quart_total,quart_effdate,siterating,siteratingdesc
	
	        Your job is to determine which column in new_land_parcel most closely matches the column "parcel_number" from table old_land_parcel.
	        Consider each the following independently of each other:
	            - The column must exist in the table new_land_parcel. Respond with 'None' if there is no good match.
	            - Consider the likely meaning of the column name "parcel_number"
	            - Consider how similar the name "parcel_number" is in relation to the names of the columns in the table land_parcel.
	
	        Additionally, consider the following sample rows from each table in CSV format.
	
	        The following 4 rows of data are from the table "old_land_parcel":
	
	        00000003814600,0980349587,0000,Vacant,AXX-1,1,2,1999,1450,100000,75000,0,175000,199000,Individual,Panther Montgomery,LOT89 BLK A,2023-01-01
	        384-99-11111,0009919834,None,Commercial,HC-3,2,2,1922,3600,250000,100000,0,350000,410000,Government,State of Washington,PLAT 52 Part of B,2024-03-01
	        03814600,1540498893,0100,Single-Family,HC-1,1,2,1956,2450,150000,75000,0,225000,237000,Individual,Mowgli Jones,LOT89 BLK A,2024-01-01
	        239847,3245890,ABC,ALL,9990,1,2,2008,2450,150000,75000,0,225000,237000,Individual,Max Powers,None,2024-09-01
	        The following 4 rows of data are from the table "new_land_parcel":
	
	        1,3621,None,3622,9746,06910700,R01,1,,,1449718102,N 1/2-2 Blk 31  Brandon 2,3480 Johns St,Norfolk,VA,23513-1655,Martin, Herman W & Betty Jo,1440 Braden Cres,Norfolk,VA,23502,001\0691\0700R012.jpg,00000011\00000586.jpg,3480,,,Johns,ST,,,Norfolk,23513-1655,0,19986,0.4588,19986,1032,D,512,512 S/F Detached >1 but <2 Sty,S/F Detached >1 but <2 Sty,240400,1609,1946,AV,Avg-,Average,4,1,0,0,No,Central air,1.5,No Attic,3/4 Crawl Space,51 Newer conventional 1+ story,Central Warm Air,Gable,Asphalt shingles,Wood frame,Asbestos siding,Skim coat plaster,Asbestos siding,Wood frame,0,0,0,None,None,345,Yes,Yes,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,None,None,None,None,None,None,None,None,0,155700,92900,248600,07/01/2025,146500,92900,239400,07/01/2024,136000,92900,228900,07/01/2023,131400,84300,215700,07/01/2022,123400,84300,207700,07/01/2021,1198195200000,Martin, Herman W & Betty Jo,Martin, Herman W & Betty Jo,0,080017737,R/S,,S,DC,1198108800000,Wesley, Mills S Jr Et Al,Martin, Herman W & Betty Jo,118000,070045404,R/S,,S,BS,1174348800000,Wesley, Mills S Jr Et Al,Wesley, Mills S Jr Et Al,0,070041439,,,S,DC,None,None,None,None,15,Class 7
	        1,31294,None,31295,41645,30337950,R01,1,,,1449592499,12 Blk 3 (Larrymore Lawns Sec 6),7437 Spartan Av,Norfolk,VA,23518-4333,Montano Living Trust,7437 Spartan Ave,Norfolk,VA,23518-4333,001\3033\7950R012.jpg,00000039\00000763.jpg,7437,,,Spartan,AV,,,Norfolk,23518-4333,0,11359,0.2608,11359,1439B,D,512,512 S/F Detached >1 but <2 Sty,S/F Detached >1 but <2 Sty,220200,2010,1963,AV,Avg,Average,4,2,0,2725,Yes,Central air,1.75,No Attic,1/4 Crawl Space,51 Newer conventional 1+ story,Central Warm Air,Gable,Asphalt shingles,Wood frame,Brick,Plaster,Brick;  Vinyl siding,Wood frame,0,0,0,None,270,None,Yes,Yes,No,No,No,Yes,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,None,None,None,None,None,None,None,None,0,229600,107100,336700,07/01/2025,196700,107100,303800,07/01/2024,183400,107100,290500,07/01/2023,176900,76300,253200,07/01/2022,160900,76300,237200,07/01/2021,1510876800000,Montano, Jose M & Loreto M,Montano Living Trust,0,170026115,,,S,DT,59702400000,,Montano, Jose M & Loreto M,0,,9999,9999,,,None,,,None,None,None,None,None,None,None,None,None,None,09,Class 1
	        1,23582,None,23583,32817,23785900,None,None,None,,1438430124,617  25 Ft,2421 Middle Av,Norfolk,VA,23504,Butts, Brittany E,2425 Middle Ave,Norfolk,VA,23504-2030,None,None,2421,,,Middle,AV,,,Norfolk,23504,0,2581,0.0593,2581,0474,None,500,500 Residential Vacant lot,Residential Vacant lot,191100,None,None,None,None,No Info,None,None,None,None,No,None,None,No Attic,No Info,None,None,None,None,None,None,None,None,None,0,0,0,None,None,None,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,None,None,None,None,None,None,None,None,0,0,27000,27000,07/01/2025,0,27000,27000,07/01/2024,0,22500,22500,07/01/2023,0,16500,16500,07/01/2022,0,16500,16500,07/01/2021,1513900800000,Perry, Muriel C Et Als,Butts, Brittany E,99900,170028692,,,X,BS,1467936000000,Perry, Muriel C,Perry, Muriel C Et Als,0,160014634,,,X,DG,1466726400000,Perry, Muriel C Et Al,Perry, Muriel C,0,160000867,,,X,LH,None,None,None,None,14,Class 6
	        1,50294,None,50295,63330,46201360,R01,1,,,1438665154,15-16 Blk 19 Lafayette Res Pk Co,3026 Racine Av,Norfolk,VA,23509-1137,Beard, Olivia Et Al,3026 Racine Ave,Norfolk,VA,23509,001\4620\1360R012.jpg,00000058\00000442.jpg,3026,,,Racine,AV,,,Norfolk,23509-1137,0,6507,0.1494,6507,0253,D,510,510 S/F Detached,S/F Detached,190700,851,1918,AV,Avg,Average,2,1,0,0,No,Central air,1.0,No Attic,Full Crawl Space,11 Older convent'l 1/1+ story,Heat pump,Gable-Hip,Asphalt shingles,Wood frame,Vinyl siding,Skim coat plaster,Vinyl siding,Wood frame,0,0,0,None,None,216,No,Yes,Yes,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,No,Yes,No,No,None,None,None,None,None,None,None,None,0,127500,109500,237000,07/01/2025,125900,109500,235400,07/01/2024,125500,109500,235000,07/01/2023,118400,92700,211100,07/01/2022,87200,90000,177200,07/01/2021,1627948800000,Chater, Christopher R & E Joy,Beard, Olivia Et Al,205000,210024147,,,S,BS,1149897600000,Moser, Marc S,Chater, Christopher R & E Joy,163000,060030837,,,S,BS,1059350400000,Virginia Realty Partners Lc,Moser, Marc S,99500,030028443,,,S,BS,None,None,None,None,20,Class 12
	        Additionally, consider the both sample data sets and data types that were provided previously.
	
	        Which column in the table land_parcel_new most closely matches the column "parcel_number"?
	        If none of the columns in land_parcel_new match "parcel_number", respond with 'None'.