# JDBC river

**URL:** <https://discuss.elastic.co/t/jdbc-river/323>\
**Category:** Elasticsearch\
**Created:** [May 7, 2015, 4:19am UTC](https://discuss.elastic.co/t/jdbc-river/323 "2015-05-07T04:19:09Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\
**Post date:** [May 7, 2015, 4:19am UTC](https://discuss.elastic.co/t/jdbc-river/323/1 "2015-05-07T04:19:09Z")

</div>

How to run function in postgres having insert statement in jdbc river? When I run it I get error:

```
org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction 

```

I enabled write and got error:

```
org.postgresql.util.PSQLException: A result was returned when none was expected.

```

My jdbc river parameters:

```
  PUT /_river/postgres_pm_file_river/_meta
    {
        "type" : "jdbc",
        "jdbc" : {
            "url" : "jdbc:postgresql://localhost:5432/test",
            "user" : "test",
            "password" : "test#",
            "sql" : [
                 {
                    "statement" : "select t_id _id,file_name from file_base"              
                }
            ],
            "index" : "pm_file",
            "type" : "pm_file_stat_2g",
            "schedule": "0 0/1 * * * ?"
        },
        "type_mapping" : {
                  "postgres_pm_file_river" : {
                      "properties" : {
                           "modified_date" : { 
                                "type" : "date",
                                "format" : "yyyy-MM-dd HH:mm:ssz"
                           }                       
                      }
                  }
            }
    }
```

---

<div class="post-metadata">

**Author:** ![GuillaumeDievart](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guillaumedievart/32/44930_2.png) [@GuillaumeDievart](https://discuss.elastic.co/u/GuillaumeDievart)\
**Post date:** [May 7, 2015, 8:38am UTC](https://discuss.elastic.co/t/jdbc-river/323/2 "2015-05-07T08:38:24Z")

</div>

Hello,

you can't execute the write requests, however you can execute procedures in which you can execute your write request.

I'm wrote an article about this, in French and on MySQL, but I suppose you can do the same thing with PostGreSQL.

[http://blog.dev-art.fr/nosql/elastic-search-river-jdbc-incremental/](http://blog.dev-art.fr/nosql/elastic-search-river-jdbc-incremental/)

---

<div class="post-metadata">

**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\
**Post date:** [May 7, 2015, 9:13am UTC](https://discuss.elastic.co/t/jdbc-river/323/3 "2015-05-07T09:13:56Z")

</div>

I don't think postgres have stored procedure. I am doing insert using function.

---

<div class="post-metadata">

**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:** [May 7, 2015, 11:07am UTC](https://discuss.elastic.co/t/jdbc-river/323/4 "2015-05-07T11:07:46Z")

</div>

Can you show the setup with the insert statement?

Insert statement should work.

---

<div class="post-metadata">

**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\
**Post date:** [May 7, 2015, 1:49pm UTC](https://discuss.elastic.co/t/jdbc-river/323/5 "2015-05-07T13:49:13Z")

</div>

Here is my function in postgres:

```
CREATE OR REPLACE FUNCTION file_stat()
  RETURNS TABLE(name text,uid_pk integer) AS
$BODY$
DECLARE
    cnt integer;
BEGIN
select count(*) into cnt from test a where a.name > (select max(max_ins_time) from test_time);
IF cnt > 0 THEN
	insert into test
	select max(g.begin_timestamp) max_ins_time,'2G' file_type from test g where g.begin_timestamp > (select max(max_ins_time) from test_time)	
	RETURN QUERY
	select name,uid_pk from test b where b.begin_timestamp > (select max(max_ins_time) from test_time);
end if;
END;
```

---

<div class="post-metadata">

**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:** [May 7, 2015, 2:10pm UTC](https://discuss.elastic.co/t/jdbc-river/323/6 "2015-05-07T14:10:58Z")

</div>

Yes, but I asked for the setup with the insert statement.

It seems you forgot to declare the statement as callable.

---

<div class="post-metadata">

**Author:** ![kitex](https://avatars.discourse-cdn.com/v4/letter/k/f14d63/32.png) [@kitex](https://discuss.elastic.co/u/kitex)\
**Post date:** [May 8, 2015, 12:28pm UTC](https://discuss.elastic.co/t/jdbc-river/323/7 "2015-05-08T12:28:14Z")

</div>

@jprante making callable works. Thank you !

For anyone seeking reference:

```
"sql" : [
             {
                "statement" : "select t_id _id,file_name from file_base",
                 "callable" : true
            }
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 12:15am UTC](https://discuss.elastic.co/t/jdbc-river/323/8 "2017-07-06T00:15:03Z")

</div>