# Postgresql stored procedure with elastic search **URL:** \ **Category:** Elasticsearch\ **Created:** [August 7, 2016, 2:49am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411 "2016-08-07T02:49:02Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 2:49am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/1 "2016-08-07T02:49:03Z")
I integrated postgresql stored procedure with elastic search. My scenario is : is it possible to write a stored procedure functionality in elastic search. Because while querying in elastic search it retrives the data from postgresql and displaying in elastic search.I strucked in stored procedure with parameters. I need to be run time data in elastic search and display it in elastic search. Currently im working on ecommerce website to better search functionality . Please help me. Thanks in advance. It's worked but in runtime how it helps echo ' { "jdbc" : { "driver": "org.postgresql.Driver", "url" : "jdbc:postgresql://localhost:5432/testdb", "user" : "sqoop", "password" : "sqoop", "sql" : [ { "callable" : true, "statement" : "{call GET\_SUPPLIER\_OF\_COFFEE(?)}", "parameter" : [ "Colombian" ] ``` } ], "index" : "my_jdbc_river5", "type" : "my_jdbc_river5" } ``` }' ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 7, 2016, 2:55am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/2 "2016-08-07T02:55:53Z")
> [@Pavan\_123](#): > > is it possible to write a stored procedure functionality in Elasticsearch Nope. What does the stored proc do? ---
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 3:16am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/3 "2016-08-07T03:16:50Z")
we written search functionality in stored procedure . Now we want to move to elastic search and dispaly in elastic search. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 7, 2016, 3:18am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/4 "2016-08-07T03:18:36Z")
You've a search function in a stored procedure in a MySQL DB, and you want to replicate that in a search engine (ES)? That's kinda reinventing the wheel here. ---
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 3:22am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/5 "2016-08-07T03:22:56Z")
Yes sir. i want to display data in ES run time. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 7, 2016, 3:23am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/6 "2016-08-07T03:23:15Z")
What do you mean by that? ---
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 3:26am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/7 "2016-08-07T03:26:24Z")
Instead of postgresql stored procedure i want to search data in elastic search. Postgresql on top of elastic search. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 7, 2016, 3:28am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/8 "2016-08-07T03:28:19Z")
You want a SQL interface to Elasticsearch? ---
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 3:29am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/9 "2016-08-07T03:29:35Z")
Yes. I call stored procedure in elastic search like this. echo ' { "jdbc" : { "driver": "org.postgresql.Driver", "url" : "jdbc:postgresql://localhost:5432/testdb", "user" : "sqoop", "password" : "sqoop", "sql" : [ { "callable" : true, "statement" : "{call GET\_SUPPLIER\_OF\_COFFEE(?)}", "parameter" : [ "Colombian" ] ``` } ], "index" : "my_jdbc_river5", "type" : "my_jdbc_river5" ``` } }' ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [August 7, 2016, 3:31am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/10 "2016-08-07T03:31:22Z")
There are no stored procedures in ES. You cannot do that. It's really not clear what you are after here sorry. ---
**Author:** ![Pavan\_123](https://avatars.discourse-cdn.com/v4/letter/p/a183cd/32.png) [@Pavan\_123](https://discuss.elastic.co/u/Pavan_123)\ **Post date:** [August 7, 2016, 3:38am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/11 "2016-08-07T03:38:16Z")
in my application my web services calling postgresql stored procedure its taking time to display data. Is it possible to postgresql on top of elastic search for searching ecommerce data. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [August 7, 2016, 9:47am UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/12 "2016-08-07T09:47:14Z")
No. You have to query Elasticsearch. You can't add PostgreSQL on top of ES and it does not make sense. You need to adapt your ecommerce application. Which one are you using BTW? ---
**Author:** ![agonen](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@agonen](https://discuss.elastic.co/u/agonen)\ **Post date:** [August 7, 2016, 3:03pm UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/13 "2016-08-07T15:03:23Z")
> **[Mikulas/pg-es-fdw](https://github.com/Mikulas/pg-es-fdw)** > > pg-es-fdw - \[PoC\] PostgreSQL Elasticsearch Foreign Data Wrapper ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [August 7, 2016, 4:33pm UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/14 "2016-08-07T16:33:06Z")
> [@Pavan\_123](#): > > Currently im working on ecommerce website to better search functionality . The JDBC importer syntax, which you quote, is a community supported application. You can use Postgresql JDBC call syntax to invoke stored procedures from the JDBC importer, which uses the results to index them into Elasticsearch. If you want a real time solution, when new events must be pushed immediately to Elasticsearch, you have to choose another solution and change your application, e.g. by adding an Elasticsearch client to create a JSON document und index the data. JDBC importer can only fetch data periodically, which might not be sufficient for your use case. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:29pm UTC](https://discuss.elastic.co/t/postgresql-stored-procedure-with-elastic-search/57411/15 "2017-07-05T22:29:33Z")