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 also need to manually set the path on the database so that python can find where you installed the ollama module. pip install stuff and then plpython should find the ollama module, connect to the ollama server, and send the prompt.

e.g

ENV PYTHONPATH="/var/lib/postgresql/.local/lib/python3.11/site-packages:$PYTHONPATH"

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'.