# Central2pg : PostgreSQL set of functions to get data from Central **URL:** \ **Category:** Showcase\ **Tags:** odk-central\ **Created:** [April 27, 2021, 6:21pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350 "2021-04-27T18:21:25Z")\ **Posts on this page:** 19\ **Page:** 2
**Author:** ![mrrodge](https://getodk.b-cdn.net/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrrodge](https://forum.getodk.org/u/mrrodge)\ **Post date:** [April 11, 2023, 1:45pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/21 "2023-04-11T13:45:43Z")
> [@mathieubossaert](#): > > But if your problem is "only" to get pictures from central, I'm sure a small python wrapper using pyODK could serve you the photos directly from Central instead of building all my database centric workflow. > But I will be glad to help if Yup - that, at the moment, is my only problem! Any pointers for where to start with a wrapper like you suggest? I've no experience with python either. Many thanks for your efforts - it's greatly appreciated. Looking at the get\_file\_from\_central function, it suggests that files are saved to the filesystem but I'm guessing I would need to host them somehow to get them into my reports. ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [April 11, 2023, 4:01pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/22 "2023-04-11T16:01:27Z")
> [@mrrodge](#): > > Looking at the get\_file\_from\_central function, it suggests that files are saved to the filesystem but I'm guessing I would need to host them somehow to get them into my reports. Yes, in our case, the folder is used and visible by both Apache and PostgreSQL (user postgres) but it could be anywhere else your reporting tool can access ---
**Author:** ![mrrodge](https://getodk.b-cdn.net/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrrodge](https://forum.getodk.org/u/mrrodge)\ **Post date:** [April 12, 2023, 7:57am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/23 "2023-04-12T07:57:19Z")
OK thanks - getting the idea now and am almost there. How do you use the get\_file\_from\_central command? It looks like that will only download one image, where I want to download all the images for the form submissions I got with odk\_central\_to\_pg. Also what does it do with the file names? What's prise\_image? Thanks again! ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [April 12, 2023, 8:28am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/24 "2023-04-12T08:28:47Z")
Let's continue the discussion with direct messages or over Github 🙂 ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [April 17, 2023, 12:18pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/25 "2023-04-17T12:18:46Z")
We achieved to get it work. Function were not ready to accept spaces chars in the form\_id. [https://forum.cen-occitanie.org/t/suivi-bourreau-des-arbres/963/6?u=mathieu](https://forum.cen-occitanie.org/t/suivi-bourreau-des-arbres/963/6?u=mathieu) I will also improve the example given on github's README page. Thanks @mrrodge, maybe we can clean the showcase and drop the messages above ? ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [May 17, 2023, 6:12am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/26 "2023-05-17T06:12:36Z")
People who used central2pg may take a look at pl-pyodk. It allows the use of filters to pull data from central. > [@Pl-pyodk : use of pyODK with PL/Python PostgreSQL functions to pull data from Central into your own database](https://forum.getodk.org/t/pl-pyodk-use-of-pyodk-with-pl-python-postgresql-functions-to-pull-data-from-central-into-your-own-database/41569): > > Two years ago, I spent time during the third COVID lock-down working on PostgreSQL functions to automatically pull data from Central into a PostgreSQL database. [Central2PG](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350) is the result of that research. And we've been able to maintain the workflows we built on Aggregate since 2015 on Central slight_smile Every time we publish a new form, we simply set up a scheduled task that uses Central2PG to automatically pull the data into a PostgreSQL database. Central2PG works very well and we never … ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [September 26, 2023, 11:04am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/27 "2023-09-26T11:04:11Z")
With 🎊 [Central 2023.4](https://forum.getodk.org/t/odk-central-2023-4-more-visible-entities-and-single-sign-on/43212) 🎊 we can now filter data from repeat table using a top level (Submissions) filter as the submission date. [central2pg can make use of it right now](https://github.com/mathieubossaert/central2pg#new-with-central-20234--filtering-subtables-for-more-lightweight-processes) and allows you to get only data that were submitted since a given timestamp or date 📅 For example, data from our main form are pulled every hour to get fresh data, without filtering, we needed to pull all the data created since the form was published (90000) ! Now we can filter subtables on submission date and pull only the 37 observations created since last known submission date 🙂 As a consequence we can increase a lot the frequency of pulling data from Central and be very close to a real time field sync between our databases or desktop tools and the field. Thanks a lot 🙏 to the ODK Team ! ---
**Author:** ![steeve\_kevin](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/steeve_kevin/32/22388_2.png) [@steeve\_kevin](https://forum.getodk.org/u/steeve_kevin)\ **Post date:** [January 31, 2024, 11:00am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/28 "2024-01-31T11:00:03Z")
Hi @mathieubossaert i wan to get data from ODK central to my own PostgreSQL database, when I run the function odk\_central.odk\_central\_to\_pg() I get the following error: NOTICE: la table « central\_json\_from\_central » n'existe pas, poursuite du traitement NOTICE: la table « central\_token » n'existe pas, poursuite du traitement ERROR: l'argument de la requête d'EXECUTE est NULL CONTEXT: fonction PL/pgSQL odk\_central.get\_form\_tables\_list\_from\_central(text,text,text,integer,text), ligne 11 à EXECUTE instruction SQL « SELECT odk\_central.get\_submission\_from\_central( user\_name, pass\_word, central\_FQDN, project, form, tablename, 'odk\_central', lower(trim(regexp\_replace(left(concat('form\_',form,'_',split\_part(tablename,'.',cardinality(regexp\_split\_to\_array(tablename,'.')))),58), '[^a-zA-Z\d_]', '_', 'g'),'_')) ) FROM odk\_central.get\_form\_tables\_list\_from\_central('_ **@gmail.com','S** _','\*\*\*\*\*',3,'support'); » fonction PL/pgSQL odk\_central.odk\_central\_to\_pg(text,text,text,integer,text,text,text), ligne 3 à EXECUTE ERREUR: l'argument de la requête d'EXECUTE est NULL SQL state: 22004 here is how the function is configured SELECT odk\_central.odk\_central\_to\_pg( '\*\*\*\*\*@gmail.com', -- user '\*\*\*\*\*\*\*_**', -- password 'f**_.com', -- central FQDN 3, -- the project id, 'support', -- form ID 'odk\_central', -- schema where to creta tables and store data '' -- columns to ignore in json transformation to database attributes (geojson fields of GeoWidgets) ); Quel peut être le problème? merci 🙏 ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [January 31, 2024, 1:05pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/29 "2024-01-31T13:05:39Z")
Hi @steeve_kevin , let's talk in private or over Github if you're ok to investigate and fix your problem. My gess is the curl function returns an error so PG does not have anything to transform. And we'll be back here to explain the problem and help other users. ---
**Author:** ![Ri\_Ri](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ri_ri/32/25702_2.png) [@Ri\_Ri](https://forum.getodk.org/u/Ri_Ri)\ **Post date:** [February 12, 2024, 7:06pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/30 "2024-02-12T19:06:32Z")
Hello,and thanks to your hard work for odkcentral2pg. I've installed and it work great. Is there way to launch the script with a "signal" from odkcentral, like a submission ? The cron job are great but use memory for nothing if not used. ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [February 12, 2024, 8:33pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/31 "2024-02-12T20:33:31Z")
Thanks a lot for ypur feedback ! And welcome to the forum. Please don't hesitate to introduce yourself on the [dedicated thread](https://forum.getodk.org/t/introduce-yourself-here/6671) 🙂 I am close to solve @steeve_kevin's issue due to curl subtilities in a windows environement... > [@Ri\_Ri](#): > > The cron job are great but use memory for nothing if not used. I don't know exactly how much memory the cron daemon uses but I think it is really light on our server. That might depend on everyone's context. Since we can filter subtables, it is lighter than ever. If one su mission occurs thé thé last time, only one submission is downloaded. > [@Ri\_Ri](#): > > Is there way to launch the script with a "signal" from odkcentral Probably... PostgreSQL can notify events and listen to notifications but I am not sure it could help to triggering central2pg. Maybe pl-pyodk in a web context and a tool like [https://github.com/crunchydata/pg\_eventserv](https://github.com/crunchydata/pg_eventserv) would be better. But it would need some modification on Central. ---
**Author:** ![Ri\_Ri](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ri_ri/32/25702_2.png) [@Ri\_Ri](https://forum.getodk.org/u/Ri_Ri)\ **Post date:** [February 12, 2024, 10:14pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/32 "2024-02-12T22:14:14Z")
thank you for your reply. I will fill in the thread soon. My need is to display each submission as soon as it is created. I can't do a cronjob for every project every second. My solution was to make a trigger on the submission table. I then use the odk\_central\_to\_pg function with the form\_id that fired the trigger. Regards ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [February 15, 2024, 8:04am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/33 "2024-02-15T08:04:12Z")
@steeve_kevin 's problem is solved and was due to curl usage on windows and the need to give the POST parameter as a file _-d @data.json_ ---
**Author:** ![steeve\_kevin](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/steeve_kevin/32/22388_2.png) [@steeve\_kevin](https://forum.getodk.org/u/steeve_kevin)\ **Post date:** [February 26, 2024, 9:50am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/34 "2024-02-26T09:50:00Z")
thank you for everyone @mathieubossaert especially for your availability. ---
**Author:** ![Ri\_Ri](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ri_ri/32/25702_2.png) [@Ri\_Ri](https://forum.getodk.org/u/Ri_Ri)\ **Post date:** [March 1, 2024, 6:26pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/35 "2024-03-01T18:26:25Z")
Hello Mathieu, it's ok with the trigger to push data when a user submit. The next step is to push image in a specific folder, in a trigger too, and i have a several problem with the transaction time, i can select all informations for get\_attachment\_from\_central, but when the fonction lunch, it's too early for the database and the fonction crash... Anyway, I have 1 question : -You haven't the problem that it's the postgres user who save the data with plpyodk ( for the rights of the image ) ? You know how i may save the image as another user with plpyodk? bye ---
**Author:** ![gcorbel](https://getodk.b-cdn.net/letter_avatar_proxy/v4/letter/g/898d66/32.png) [@gcorbel](https://forum.getodk.org/u/gcorbel)\ **Post date:** [July 19, 2024, 9:42am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/36 "2024-07-19T09:42:15Z")
Good morning, Thank you Mathieu Bossaert for this amazing job, I m testing central2pg with your example form ODKwaypoints I ran your central2pg.sql in a query tool and then I ran : "SELECT odk\_central.odk\_central\_to\_pg( 'email@domain.fr', -- user 'mypassword', -- password '[odk.gedeop.inrae.fr](http://odk.gedeop.inrae.fr)', -- central FQDN 2, -- the project id, 'ODKwaypoint', -- form ID 'odk\_central', -- schema where to creta tables and store data 'point\_auto\_5,point\_auto\_10,point\_auto\_15,point,ligne,polygone' -- columns to ignore in json transformation to database attributes (geojson fields of GeoWidgets) ); but I get the same error as @steeve_kevin I can see that you solved the problem, can you give me more details please because I don't understand how to " give the POST parameter as a file -d @data.json" Thank you and have a good day, Gaëlle ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [July 19, 2024, 5:01pm UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/37 "2024-07-19T17:01:54Z")
Hi @gcorbel , Thanks a lot 😊 I'm sorry I didn't take the time to document it... I'll send you the modified script before I publish it on gitbub (at the begining of August). ---
**Author:** ![chun\_hing\_yap](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/chun_hing_yap/32/13598_2.png) [@chun\_hing\_yap](https://forum.getodk.org/u/chun_hing_yap)\ **Post date:** [March 14, 2025, 12:31am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/38 "2025-03-14T00:31:49Z")
is there a latest version published in 2024? ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [March 14, 2025, 7:01am UTC](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350/39 "2025-03-14T07:01:31Z")
I have to fix a problem for forms with form\_id containing space and special chars. In other cases, last github file is ok. Once the problem fixed I'll publish a last one before making the effort on pl-pydok : [https://github.com/mathieubossaert/pl-pyodk](https://github.com/mathieubossaert/pl-pyodk) [Previous page](https://forum.getodk.org/t/central2pg-postgresql-set-of-functions-to-get-data-from-central/33350.md?page=1)