Downloaded 13 times
![Arrays Or, if no array size is to be specified: pay_by_quarter integer ARRAY pay_by_quarter integer ARRAY[4]](https://image.slidesharecdn.com/businesslogicwithpostgresqlandpython-150619015710-lva1-app6891/75/Business-logic-with-PostgreSQL-and-Python-13-2048.jpg)
![Arrays And get it INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row)](https://image.slidesharecdn.com/businesslogicwithpostgresqlandpython-150619015710-lva1-app6891/75/Business-logic-with-PostgreSQL-and-Python-14-2048.jpg)
![Still arrays SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; schedule ------------------------------------------- {{meeting,lunch},{training,presentation}} (1 row) SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);](https://image.slidesharecdn.com/businesslogicwithpostgresqlandpython-150619015710-lva1-app6891/75/Business-logic-with-PostgreSQL-and-Python-15-2048.jpg)
![Compile python Compilation takes some time… Once it’s done… we need easy_install and other python modules [hubert@ThePit]~/stuff/Python-2.7.10% ./configure —PREFIX=/opt/py --enable- shared](https://image.slidesharecdn.com/businesslogicwithpostgresqlandpython-150619015710-lva1-app6891/75/Business-logic-with-PostgreSQL-and-Python-21-2048.jpg)
Uploaded byHubert Piotrowski
Business logic with PostgreSQL and Python
The document discusses the integration of business logic with PostgreSQL and Python using functions, triggers, and PL/Python. It highlights the capabilities of PostgreSQL, including dynamic data types, procedural functions, and the use of arrays and composite types. Additionally, it provides a guide for compiling PostgreSQL with Python and creating example functions and triggers.
Business logic with PostgreSQL and Python
- 1. Business logic with PostgreSQLand Python Using functions and triggers and PostgreSQL magic with plpy.
- 2. Knock knock, who's there? HubertPiotrowski Senior systems architect
- 3. How to buildAPI
- 4. How to buildAPI Perfect API
- 5. My awesome software Where API functionalitiesare part of it
- 6.
- 7. My awesome software API Another app Ohthis this small tool
- 8. My awesome software API Oh thisthis small tool Another magic Fancy twisted stackless
- 9.
- 10. My awesome software API Oh thisthis small tool Another magic Business LOGIC
- 11. What is sogreat about PostgreSQL • Dynamic data typing • Procedural functions (many languages) • Horizontal scaling • Plugins • Triggers • Granularity for access privileges
- 12. Data types • Standard:Strings, float, inter, etc. • JSON • Dynamic (composite) • Arrays
- 13. Arrays Or, if noarray size is to be specified: pay_by_quarter integer ARRAY pay_by_quarter integer ARRAY[4]
- 14. Arrays And get it INSERTINTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}'); SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row)
- 15. Still arrays SELECT schedule[1:2][2]FROM sal_emp WHERE name = 'Bill'; schedule ------------------------------------------- {{meeting,lunch},{training,presentation}} (1 row) SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
- 16. Composite CREATE TYPE inventory_itemAS ( name text, supplier_id integer, price numeric ); CREATE TABLE on_hand ( item inventory_item, count integer ); INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
- 17. Procedures plPythonu(?) one second CREATEFUNCTION merge_fields(t_row table1) RETURNS text AS $$ DECLARE t2_row table2%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2 WHERE ... ; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
- 18. Better formatting CREATE FUNCTIONmerge_fields(t_row table1) RETURNS text AS $$ DECLARE t2_row table2%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2 WHERE ... ; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
- 19. Let there belight...and there was light Compile PostgreSQL 9.4 with Python 2.7.10
- 20. • PYTHONHOME • PYTHONPATH •PYTHONY2K • PYTHONOPTIMIZE • PYTHONDEBUG • PYTHONVERBOSE • PYTHONCASEOK • PYTHONDONTWRITEBYTECODE • PYTHONIOENCODING • PYTHONUSERBASE Python compiled with special flags
- 21. Compile python Compilation takessome time… Once it’s done… we need easy_install and other python modules [hubert@ThePit]~/stuff/Python-2.7.10% ./configure —PREFIX=/opt/py --enable- shared
- 22. issues? export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/py/lib py ➤bin/python2.7 bin/python2.7: error while loading shared libraries: libpython2.7.so.1.0: cannot open shared object file: No such file or directory simple fix
- 23. Compile essential contribdata contrib ➤ cd ~/stuff/postgresql-9.4.3/contrib contrib ➤ make contrib ➤ make install Compile PostgreSQL postgresql-9.4.3 ➤ ./configure --prefix=/opt/pgsql —with-python PYTHON=/opt/py/bin/python postgresql-9.4.3 ➤ make postgresql-9.4.3 ➤ make install
- 24. ~ ➤ mkdir/opt/pg_data ~ ➤ /opt/pgsql/bin/initdb /opt/pg_data ~ ➤ /opt/pgsql/bin/postmaster -p 5432 -D /opt/pg_data/ ~ ➤ /opt/pgsql/bin/createdb -h localhost -E utf8 pie initialize database Initialize postgresql data space
- 25. Create language plpythonin database ~ ➤ /opt/pgsql/bin/createlang -h localhost -d pie plpythonu ~ ➤ /opt/pgsql/bin/postmaster -p 5432 -D /opt/pg_data/ LOG: database system was shut down at 2015-06-07 17:29:05 SGT LOG: database system is ready to accept connections LOG: autovacuum launcher started pie=# select * from pg_extension ; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -----------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | plpythonu | 10 | 11 | f | 1.0 | | (2 rows)
- 26. Let uncle Googlehelp you didn’t find the answer? Probably the issue that you have means that what you try to achieve is not possible or… doesn’t make sense
- 27.
- 28. Hello world create orreplace function logic.hello_world() returns void as $$ """ Code code code code """ print "hello world" $$ LANGUAGE plpythonu VOLATILE;
- 29. Hello world create orreplace function logic.hello_world() returns void as $$ """ Code code code code """ plpy.info("hello world") $$ LANGUAGE plpythonu VOLATILE;
- 30. Triggers • can befired before or after (instead of) • upon action: insert, update or delete • trigger fires function (also by using when condition) • trigger function can but don’t have to manipulate data • multiple triggers on the same table
- 31. Let’s see someaction
- 32.