GET call with `in` filter to POST call (414 URI too long)

[Pavan-anonymous]

We currently make a GET call thats works fine with small query, but the issue is when the query is too long its not able to handle. So i want to change it to a POST call on my code side but i am not sure how it works in PostgREST, can someone help me with how to do that. Thanks for your help in advance.

[steve-chavez]

Hey @Pavan-anonymous,

Can you share the request you're doing? Does it have a lot of filters or an in filter with lots of values?

[Pavan-anonymous]

@steve-chavez Thanks for the response. I cant share the request but yes it has only two 'in' filters and one among them has values ranging from any where between 1 - 3000 (and each value about 20 characters). Can you suggest on this

[steve-chavez]

@Pavan-anonymous I see. In that case you could convert the GET to POST with a function. It should be like:

create or replace function projects_in(vals int[]) returns setof projects as $$
  select * from projects
  where id = any(vals);
$$ language sql;

POST /rpc/projects_in # you can add any of the GET filters to POST "?id=eq.2&select=id,name,clients(*)" 

{"vals": [1,2,3,4,5,6,7,8,9,10,....]} # any size of IN values here

[Pavan-anonymous]

Thanks @steve-chavez

[Pavan-anonymous]

@steve-chavez I'm confused with the above suggestion.

Below is the sample request which will work but if the locations gets increased (if we add more states with many counties) it gives bad request message. Please suggest how we can convert this GET call to POST or any other way we can make it work.

http://domain/sales?practice=in.(practice_type)&locations=in.%28florida%2Falachua%2Cflorida%2Fbaker%2Cflorida%2Fbay%2Cflorida%2Fbradford%2Cflorida%2Fbrevard%2Cflorida%2Fbroward%2Cflorida%2Fcalhoun%2Cflorida%2Fcharlotte%2Cflorida%2Fcitrus%2Cflorida%2Fclay%2Cflorida%2Fcollier%2Cflorida%2Fcolumbia%2Cflorida%2Fde+soto%2Cflorida%2Fdixie%2Cflorida%2Fduval%2Cflorida%2Fescambia%2Cflorida%2Fflagler%2Cflorida%2Ffranklin%2Cflorida%2Fgadsden%2Cflorida%2Fgilchrist%2Cflorida%2Fglades%2Cflorida%2Fgulf%2Cflorida%2Fhamilton%2Cflorida%2Fhardee%2Cflorida%2Fhendry%2Cflorida%2Fhernando%2Cflorida%2Fhighlands%2Cflorida%2Fhillsborough%2Cflorida%2Fholmes%2Cflorida%2Findian+river%2Cflorida%2Fjackson%2Cflorida%2Fjefferson%2Cflorida%2Flafayette%2Cflorida%2Flake%2Cflorida%2Flee%2Cflorida%2Fleon%2Cflorida%2Flevy%2Cflorida%2Fliberty%2Cflorida%2Fmadison%2Cflorida%2Fmanatee%2Cflorida%2Fmarion%2Cflorida%2Fmartin%2Cflorida%2Fmiami-dade%2Cflorida%2Fmonroe%2Cflorida%2Fnassau%2Cflorida%2Fokaloosa%2Cflorida%2Fokeechobee%2Cflorida%2Forange%2Cflorida%2Fosceola%2Cflorida%2Fpalm+beach%2Cflorida%2Fpasco%2Cflorida%2Fpinellas%2Cflorida%2Fpolk%2Cflorida%2Fputnam%2Cflorida%2Fsaint+johns%2Cflorida%2Fsaint+lucie%2Cflorida%2Fsanta+rosa%2Cflorida%2Fsarasota%2Cflorida%2Fseminole%2Cflorida%2Fsumter%2Cflorida%2Fsuwannee%2Cflorida%2Ftaylor%2Cflorida%2Funion%2Cflorida%2Fvolusia%2Cflorida%2Fwakulla%2Cflorida%2Fwalton%2Cflorida%2Fwashington%2Calabama%2Fautauga%2Calabama%2Fbaldwin%2Calabama%2Fbarbour%2Calabama%2Fbibb%2Calabama%2Fblount%2Calabama%2Fbullock%2Calabama%2Fbutler%2Calabama%2Fcalhoun%2Calabama%2Fchambers%2Calabama%2Fcherokee%2Calabama%2Fchilton%2Calabama%2Fchoctaw%2Calabama%2Fclarke%2Calabama%2Fclay%2Calabama%2Fcleburne%2Calabama%2Fcoffee%2Calabama%2Fcolbert%2Calabama%2Fconecuh%2Calabama%2Fcoosa%2Calabama%2Fcovington%2Calabama%2Fcrenshaw%2Calabama%2Fcullman%2Calabama%2Fdale%2Calabama%2Fdallas%2Calabama%2Fde+kalb%2Calabama%2Felmore%2Calabama%2Fescambia%2Calabama%2Fetowah%2Calabama%2Ffayette%2Calabama%2Ffranklin%2Calabama%2Fgeneva%2Calabama%2Fgreene%2Calabama%2Fhale%2Calabama%2Fhenry%2Calabama%2Fhouston%2Calabama%2Fjackson%2Calabama%2Fjefferson%2Calabama%2Flamar%2Calabama%2Flauderdale%2Calabama%2Flawrence%2Calabama%2Flee%2Calabama%2Flimestone%2Calabama%2Flowndes%2Calabama%2Fmacon%2Calabama%2Fmadison%2Calabama%2Fmarengo%2Calabama%2Fmarion%2Calabama%2Fmarshall%2Calabama%2Fmobile%2Calabama%2Fmonroe%2Calabama%2Fmontgomery%2Calabama%2Fmorgan%2Calabama%2Fperry%2Calabama%2Fpickens%2Calabama%2Fpike%2Calabama%2Frandolph%2Calabama%2Frussell%2Calabama%2Fsaint+clair%2Calabama%2Fshelby%2Calabama%2Fsumter%2Calabama%2Ftalladega%2Calabama%2Ftallapoosa%2Calabama%2Ftuscaloosa%2Calabama%2Fwalker%2Calabama%2Fwashington%2Calabama%2Fwilcox%2Calabama%2Fwinston%2Carizona%2Fapache%2Carizona%2Fcochise%2Carizona%2Fcoconino%2Carizona%2Fgila%2Carizona%2Fgraham%2Carizona%2Fgreenlee%2Carizona%2Fla+paz%2Carizona%2Fmaricopa%2Carizona%2Fmohave%2Carizona%2Fnavajo%2Carizona%2Fpima%2Carizona%2Fpinal%2Carizona%2Fsanta+cruz%2Carizona%2Fyavapai%2Carizona%2Fyuma%2Cconnecticut%2Ffairfield%2Cconnecticut%2Fhartford%2Cconnecticut%2Flitchfield%2Cconnecticut%2Fmiddlesex%2Cconnecticut%2Fnew+haven%2Cconnecticut%2Fnew+london%2Cconnecticut%2Ftolland%2Cconnecticut%2Fwindham%2Cgeorgia%2Fappling%2Cgeorgia%2Fatkinson%2Cgeorgia%2Fbacon%2Cgeorgia%2Fbaker%2Cgeorgia%2Fbaldwin%2Cgeorgia%2Fbanks%2Cgeorgia%2Fbarrow%2Cgeorgia%2Fbartow%2Cgeorgia%2Fben+hill%2Cgeorgia%2Fberrien%2Cgeorgia%2Fbibb%2Cgeorgia%2Fbleckley%2Cgeorgia%2Fbrantley%2Cgeorgia%2Fbrooks%2Cgeorgia%2Fbryan%2Cgeorgia%2Fbulloch%2Cgeorgia%2Fburke%2Cgeorgia%2Fbutts%2Cgeorgia%2Fcalhoun%2Cgeorgia%2Fcamden%2Cgeorgia%2Fcandler%2Cgeorgia%2Fcarroll%2Cgeorgia%2Fcatoosa%2Cgeorgia%2Fcharlton%2Cgeorgia%2Fchatham%2Cgeorgia%2Fchattahoochee%2Cgeorgia%2Fchattooga%2Cgeorgia%2Fcherokee%2Cgeorgia%2Fclarke%2Cgeorgia%2Fclay%2Cgeorgia%2Fclayton%2Cgeorgia%2Fclinch%2Cgeorgia%2Fcobb%2Cgeorgia%2Fcoffee%2Cgeorgia%2Fcolquitt%2Cgeorgia%2Fcolumbia%2Cgeorgia%2Fcook%2Cgeorgia%2Fcoweta%2Cgeorgia%2Fcrawford%2Cgeorgia%2Fcrisp%2Cgeorgia%2Fdade%2Cgeorgia%2Fdawson%2Cgeorgia%2Fdecatur%2Cgeorgia%2Fdekalb%2Cgeorgia%2Fdodge%2Cgeorgia%2Fdooly%2Cgeorgia%2Fdougherty%2Cgeorgia%2Fdouglas%2Cgeorgia%2Fearly%2Cgeorgia%2Fechols%2Cgeorgia%2Feffingham%2Cgeorgia%2Felbert%2Cgeorgia%2Femanuel%2Cgeorgia%2Fevans%2Cgeorgia%2Ffannin%2Cgeorgia%2Ffayette%2Cgeorgia%2Ffloyd%2Cgeorgia%2Fforsyth%2Cgeorgia%2Ffranklin%2Cgeorgia%2Ffulton%2Cgeorgia%2Fgilmer%2Cgeorgia%2Fglascock%2Cgeorgia%2Fglynn%2Cgeorgia%2Fgordon%2Cgeorgia%2Fgrady%2Cgeorgia%2Fgreene%2Cgeorgia%2Fgwinnett%2Cgeorgia%2Fhabersham%2Cgeorgia%2Fhall%2Cgeorgia%2Fhancock%2Cgeorgia%2Fharalson%2Cgeorgia%2Fharris%2Cgeorgia%2Fhart%2Cgeorgia%2Fheard%2Cgeorgia%2Fhenry%2Cgeorgia%2Fhouston%2Cgeorgia%2Firwin%2Cgeorgia%2Fjackson%2Cgeorgia%2Fjasper%2Cgeorgia%2Fjeff+davis%2Cgeorgia%2Fjefferson%2Cgeorgia%2Fjenkins%2Cgeorgia%2Fjohnson%2Cgeorgia%2Fjones%2Cgeorgia%2Flamar%2Cgeorgia%2Flanier%2Cgeorgia%2Flaurens%2Cgeorgia%2Flee%2Cgeorgia%2Fliberty%2Cgeorgia%2Flincoln%2Cgeorgia%2Flong%2Cgeorgia%2Flowndes%2Cgeorgia%2Flumpkin%2Cgeorgia%2Fmacon%2Cgeorgia%2Fmadison%2Cgeorgia%2Fmarion%2Cgeorgia%2Fmcduffie%2Cgeorgia%2Fmcintosh%2Cgeorgia%2Fmeriwether%2Cgeorgia%2Fmiller%2Cgeorgia%2Fmitchell%2Cgeorgia%2Fmonroe%2Cgeorgia%2Fmontgomery%2Cgeorgia%2Fmorgan%2Cgeorgia%2Fmurray%2Cgeorgia%2Fmuscogee%2Cgeorgia%2Fnewton%2Cgeorgia%2Foconee%2Cgeorgia%2Foglethorpe%2Cgeorgia%2Fpaulding%2Cgeorgia%2Fpeach%2Cgeorgia%2Fpickens%2Cgeorgia%2Fpierce%2Cgeorgia%2Fpike%2Cgeorgia%2Fpolk%2Cgeorgia%2Fpulaski%2Cgeorgia%2Fputnam%2Cgeorgia%2Fquitman%2Cgeorgia%2Frabun%2Cgeorgia%2Frandolph%2Cgeorgia%2Frichmond%2Cgeorgia%2Frockdale%2Cgeorgia%2Fschley%2Cgeorgia%2Fscreven%2Cgeorgia%2Fseminole%2Cgeorgia%2Fspalding%2Cgeorgia%2Fstephens%2Cgeorgia%2Fstewart%2Cgeorgia%2Fsumter%2Cgeorgia%2Ftalbot%2Cgeorgia%2Ftaliaferro%2Cgeorgia%2Ftattnall%2Cgeorgia%2Ftaylor%2Cgeorgia%2Ftelfair%2Cgeorgia%2Fterrell%2Cgeorgia%2Fthomas%2Cgeorgia%2Ftift%2Cgeorgia%2Ftoombs%2Cgeorgia%2Ftowns%2Cgeorgia%2Ftreutlen%2Cgeorgia%2Ftroup%2Cgeorgia%2Fturner%2Cgeorgia%2Ftwiggs%2Cgeorgia%2Funion%2Cgeorgia%2Fupson%2Cgeorgia%2Fwalker%2Cgeorgia%2Fwalton%2Cgeorgia%2Fware%2Cgeorgia%2Fwarren%2Cgeorgia%2Fwashington%2Cgeorgia%2Fwayne%2Cgeorgia%2Fwebster%2Cgeorgia%2Fwheeler%2Cgeorgia%2Fwhite%2Cgeorgia%2Fwhitfield%2Cgeorgia%2Fwilcox%2Cgeorgia%2Fwilkes%2Cgeorgia%2Fwilkinson%2Cgeorgia%2Fworth%2Chawaii%2Fhawaii%2Chawaii%2Fhonolulu%2Chawaii%2Fkauai%2Chawaii%2Fmaui%2Chawaii%2Fkalawao%2Cillinois%2Fadams%2Cillinois%2Falexander%2Cillinois%2Fbond%2Cillinois%2Fboone%2Cillinois%2Fbrown%2Cillinois%2Fbureau%2Cillinois%2Fcalhoun%2Cillinois%2Fcarroll%2Cillinois%2Fcass%2Cillinois%2Fchampaign%2Cillinois%2Fchristian%2Cillinois%2Fclark%2Cillinois%2Fclay%2Cillinois%2Fclinton%2Cillinois%2Fcoles%2Cillinois%2Fcook%2Cillinois%2Fcrawford%2Cillinois%2Fcumberland%2Cillinois%2Fdekalb%2Cillinois%2Fdewitt%2Cillinois%2Fdouglas%2Cillinois%2Fdupage%2Cillinois%2Fedgar%2Cillinois%2Fedwards%2Cillinois%2Feffingham%2Cillinois%2Ffayette%2Cillinois%2Fford%2Cillinois%2Ffranklin%2Cillinois%2Ffulton%2Cillinois%2Fgallatin%2Cillinois%2Fgreene%2Cillinois%2Fgrundy%2Cillinois%2Fhamilton%2Cillinois%2Fhancock%2Cillinois%2Fhardin%2Cillinois%2Fhenderson%2Cillinois%2Fhenry%2Cillinois%2Firoquois%2Cillinois%2Fjackson%2Cillinois%2Fjasper%2Cillinois%2Fjefferson%2Cillinois%2Fjersey%2Cillinois%2Fjo+daviess%2Cillinois%2Fjohnson%2Cillinois%2Fkane%2Cillinois%2Fkankakee%2Cillinois%2Fkendall%2Cillinois%2Fknox%2Cillinois%2Fla+salle%2Cillinois%2Flake%2Cillinois%2Flawrence%2Cillinois%2Flee%2Cillinois%2Flivingston%2Cillinois%2Flogan%2Cillinois%2Fmacon%2Cillinois%2Fmacoupin%2Cillinois%2Fmadison%2Cillinois%2Fmarion%2Cillinois%2Fmarshall%2Cillinois%2Fmason%2Cillinois%2Fmassac%2Cillinois%2Fmcdonough%2Cillinois%2Fmchenry%2Cillinois%2Fmclean%2Cillinois%2Fmenard%2Cillinois%2Fmercer%2Cillinois%2Fmonroe%2Cillinois%2Fmontgomery%2Cillinois%2Fmorgan%2Cillinois%2Fmoultrie%2Cillinois%2Fogle%2Cillinois%2Fpeoria%2Cillinois%2Fperry%2Cillinois%2Fpiatt%2Cillinois%2Fpike%2Cillinois%2Fpope%2Cillinois%2Fpulaski%2Cillinois%2Fputnam%2Cillinois%2Frandolph%2Cillinois%2Frichland%2Cillinois%2Frock+island%2Cillinois%2Fsaint+clair%2Cillinois%2Fsaline%2Cillinois%2Fsangamon%2Cillinois%2Fschuyler%2Cillinois%2Fscott%2Cillinois%2Fshelby%2Cillinois%2Fstark%2Cillinois%2Fstephenson%2Cillinois%2Ftazewell%2Cillinois%2Funion%2Cillinois%2Fvermilion%2Cillinois%2Fwabash%2Cillinois%2Fwarren%2Cillinois%2Fwashington%2Cillinois%2Fwayne%2Cillinois%2Fwhite%2Cillinois%2Fwhiteside%2Cillinois%2Fwill%2Cillinois%2Fwilliamson%2Cillinois%2Fwinnebago%2Cillinois%2Fwoodford%2Cindiana%2Fadams%2Cindiana%2Fallen%2Cindiana%2Fbartholomew%2Cindiana%2Fbenton%2Cindiana%2Fblackford%2Cindiana%2Fboone%2Cindiana%2Fbrown%2Cindiana%2Fcarroll%2Cindiana%2Fcass%2Cindiana%2Fclark%2Cindiana%2Fclay%2Cindiana%2Fclinton%2Cindiana%2Fcrawford%2Cindiana%2Fdaviess%2Cindiana%2Fde+kalb%2Cindiana%2Fdearborn%2Cindiana%2Fdecatur%2Cindiana%2Fdelaware%2Cindiana%2Fdubois%2Cindiana%2Felkhart%2Cindiana%2Ffayette%2Cindiana%2Ffloyd%2Cindiana%2Ffountain%2Cindiana%2Ffranklin%2Cindiana%2Ffulton%2Cindiana%2Fgibson%2Cindiana%2Fgrant%2Cindiana%2Fgreene%2Cindiana%2Fhamilton%2Cindiana%2Fhancock%2Cindiana%2Fharrison%2Cindiana%2Fhendricks%2Cindiana%2Fhenry%2Cindiana%2Fhoward%2Cindiana%2Fhuntington%2Cindiana%2Fjackson%2Cindiana%2Fjasper%2Cindiana%2Fjay%2Cindiana%2Fjefferson%2Cindiana%2Fjennings%2Cindiana%2Fjohnson%2Cindiana%2Fknox%2Cindiana%2Fkosciusko%2Cindiana%2Fla+porte%2Cindiana%2Flagrange%2Cindiana%2Flake%2Cindiana%2Flawrence%2Cindiana%2Fmadison%2Cindiana%2Fmarion%2Cindiana%2Fmarshall%2Cindiana%2Fmartin%2Cindiana%2Fmiami%2Cindiana%2Fmonroe%2Cindiana%2Fmontgomery%2Cindiana%2Fmorgan%2Cindiana%2Fnewton%2Cindiana%2Fnoble%2Cindiana%2Fohio%2Cindiana%2Forange%2Cindiana%2Fowen%2Cindiana%2Fparke%2Cindiana%2Fperry%2Cindiana%2Fpike%2Cindiana%2Fporter%2Cindiana%2Fposey%2Cindiana%2Fpulaski%2Cindiana%2Fputnam%2Cindiana%2Frandolph%2Cindiana%2Fripley

[steve-chavez]

@Pavan-anonymous It should be like this:

create or replace function sales_locations_in(vals text[]) returns setof sales as $$
  select * from sales
  where locations = any(vals);
$$ language sql;

Then you can use POST for the locations:

POST /rpc/sales_locations_in?practice=in.(practice_type)

{"vals": ["florida/Falachua", "florida/baker", "etc"]}

[steve-chavez]

The upcoming HTTP QUERY method could help us with this issue.