Downloaded 272 times
![Deployment Descriptor Optional way to integrate install/uninstall SQL statements into the JAR file: SQLActions[] = { "BEGIN INSTALL CREATE FUNCTION add(int, int) RETURNS int LANGUAGE java AS 'com.example.Foo.add'; END INSTALL", "BEGIN REMOVE DROP FUNCTION add(int, int); END REMOVE" } 18](https://image.slidesharecdn.com/pljava-090619092408-phpapp02/75/PostgreSQL-and-PL-Java-18-2048.jpg)
![Parameter Type Mapping Parameter types are mapped automatically: PostgreSQL Java boolean boolean shortint short int int bigint long real float double precision double varchar, text java.lang.String bytea byte[] date java.sql.Date time java.sql.Time timestamp java.sql.Timestamp other java.lang.String 20](https://image.slidesharecdn.com/pljava-090619092408-phpapp02/75/PostgreSQL-and-PL-Java-20-2048.jpg)
![Triggers static void moddatetime(TriggerData td) throws SQLException { if(td.isFiredForStatement()) throw new TriggerException(td, "can't process STATEMENT events"); if(td.isFiredAfter()) throw new TriggerException(td, "must be fired before event"); if(!td.isFiredByUpdate()) throw new TriggerException(td, "can only process UPDATE events"); ResultSet _new = td.getNew(); String[] args = td.getArguments(); if (args.length != 1) throw new TriggerException(td, "one argument was expected"); _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis())); } 25](https://image.slidesharecdn.com/pljava-090619092408-phpapp02/75/PostgreSQL-and-PL-Java-25-2048.jpg)
Uploaded byPeter Eisentraut
PostgreSQL and PL/Java
This document summarizes PL/Java, which allows writing server-side functions in Java for PostgreSQL. It discusses how to define and deploy Java functions, configure PL/Java, handle parameters and return types, use JDBC from functions, and write triggers in Java. While compatible with Oracle's SQL/JRT standard, PL/Java has some limitations around memory usage and performance. It works best on Linux and is a stable option for adding Java code to PostgreSQL databases.
PostgreSQL and PL/Java
- 1. PostgreSQL and PL/Java Server-Side Functions in Java Peter Eisentraut petere@postgresql.org
- 2. Agenda • Functions in PostgreSQL • Enter PL/Java • Features of PL/Java • Support and Compatibility • Outlook and Wrap-Up 2
- 3. Defining a Function Exampleof an SQL function: CREATE FUNCTION add(int, int) RETURNS int LANGUAGE SQL AS 'SELECT $1 + $2;'; SELECT add(4, 5); add ----- 9 3
- 4. Defining a Function Exampleof a C function: PG_FUNCTION_INFO_V1(funcname); Datum add(PG_FUNCTION_ARGS) { int32 a = PG_GETARG_INT32(0); int32 b = PG_GETARG_INT32(1); PG_RETURN_INT32(a + b); } 4
- 5. Defining a Function Exampleof a C function, continued: gcc -fPIC -c file.c gcc -shared -o file.so file.o CREATE FUNCTION add(int, int) RETURNS int LANGUAGE C AS 'file.so', 'add'; 5
- 6. Features of Functions •Overloading • Processing sets/tables • Caching options (deterministic/nondeterministic) • Execution privileges 6
- 7. Advantages of Server-Side Functions • Encapsulation • Faster database access • Plan caching, inlining • Data validation through triggers • Side effects through triggers 7
- 8. Functions as BuildingBlocks • Operators • Data types • Aggregate functions • Index access methods • Type casts • Character set conversions 8
- 9. Procedural Languages • Choiceof SQL vs. C quite limited • Solution: pluggable language handlers • Available languages: Tcl, PL/pgSQL, Perl, Ruby, Python, Shell, R, Java, PHP 9
- 10. Procedural Language Example: PL/pgSQL CREATE FUNCTION logfunc(logtxt text) RETURNS timestamp AS ' DECLARE curtime timestamp; BEGIN curtime := ''now''; INSERT INTO logtable VALUES (logtxt, curtime); RETURN curtime; END; ' LANGUAGE plpgsql; 10
- 11. Procedural Language Example: PL/Perl CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS ' if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) { return "SKIP"; # skip INSERT/UPDATE command } elsif ($_TD->{new}{v} ne "immortal") { $_TD->{new}{v} .= "(modified by trigger)"; return "MODIFY"; # modify row and run INSERT/UPDATE } else { return; # execute INSERT/UPDATE command } ' LANGUAGE plperl; 11
- 12. Enter PL/Java • Developedby Thomas Hallgren • Stored procedures written in the Java language • Java the most popular (client) language for PostgreSQL 12
- 13. Standardization SQL standard: ISO/IEC9075-13:2003 SQL Routines and Types for the Java Programming Language ("SQL/JRT") (210 pages) driven by Oracle and IBM 13
- 14. Timeline of PL/Java • Nov. 2000: first attempt with Kaffe 1.0.6 • Dec. 2003: PL/Java project launched • Jan. 2004: first alpha release • Jan. 2005: release 1.0.0 (for PG 7.4) • Apr. 2005: release 1.1.0 (for PG 8.0) • currently “stable” 14
- 15. Concept • Write a Java class • Designate static method as entry point • Pack into JAR • Load JAR into database • Adjust classpath • Create function in PostgreSQL 15
- 16. Simple Example: Code packagecom.example; public class Foo { static int add(int a, int b) { return a + b; } } 16
- 17. Simple Example: Deployment javaccom/example/Foo.java jar cf foo.jar com/example/Foo.class SELECT sqlj.install_jar('file:/home/peter/tmp/foo.jar', 'foo', false); SELECT sqlj.set_classpath('public', 'foo:bar:etc'); CREATE FUNCTION add(int, int) RETURNS int LANGUAGE java AS 'com.example.Foo.add'; 17
- 18. Deployment Descriptor Optional wayto integrate install/uninstall SQL statements into the JAR file: SQLActions[] = { "BEGIN INSTALL CREATE FUNCTION add(int, int) RETURNS int LANGUAGE java AS 'com.example.Foo.add'; END INSTALL", "BEGIN REMOVE DROP FUNCTION add(int, int); END REMOVE" } 18
- 19. Configuration New parameters forpostgresql.conf: custom_variable_classes = 'pljava' pljava.classpath = '/some/where/pljava.jar' pljava.statement_cache_size = 10 pljava.release_lingering_savepoints = true pljava.vmoptions = '-Xmx64M' pljava.debug = false 19
- 20. Parameter Type Mapping Parametertypes are mapped automatically: PostgreSQL Java boolean boolean shortint short int int bigint long real float double precision double varchar, text java.lang.String bytea byte[] date java.sql.Date time java.sql.Time timestamp java.sql.Timestamp other java.lang.String 20
- 21. Composite Types CREATE TYPEcompositeTest AS ( base integer, incbase integer, ctime timestamp ); CREATE FUNCTION useCompositeTest (compositeTest) RETURNS varchar AS 'foo.fee.Fum.useCompositeTest' LANGUAGE java; 21
- 22. Composite Types Represented asjava.sql.ResultSet with one row. public static String useCompositeTest(ResultSet compositeTest) throws SQLException { int base = compositeTest.getInt(1); int incbase = compositeTest.getInt(2); Timestamp ctime = compositeTest.getTimestamp(3); return "Base = "" + base + "", incbase = "" + incbase + "", ctime = "" + ctime + """; } 22
- 23. Returning Sets CREATE FUNCTIONgetNames() RETURNS SETOF varchar AS 'Bar.getNames' LANGUAGE java; import java.util.Iterator; public class Bar { public static Iterator getNames() { ArrayList names = new ArrayList(); names.add("Lisa"); names.add("Bob"); names.add("Bill"); return names.iterator(); } } 23
- 24. Built-in JDBC Driver •Prepare/execute queries • Query metadata • No transaction management (can use savepoints) Connection conn = DriverManager.getConnection("jdbc:default:connectio n"); 24
- 25. Triggers static void moddatetime(TriggerDatatd) throws SQLException { if(td.isFiredForStatement()) throw new TriggerException(td, "can't process STATEMENT events"); if(td.isFiredAfter()) throw new TriggerException(td, "must be fired before event"); if(!td.isFiredByUpdate()) throw new TriggerException(td, "can only process UPDATE events"); ResultSet _new = td.getNew(); String[] args = td.getArguments(); if (args.length != 1) throw new TriggerException(td, "one argument was expected"); _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis())); } 25
- 26. Other Features • Exception handling • Logging • DatabaseMetaData • Multithreading • IN/OUT parameters (PostgreSQL 8.1) • Security 26
- 27. Problem Areas • Memoryusage • Performance? • Stack handling 27
- 28. Build Options • Buildswith: • Sun JDK ≥ 1.4 (shared library + JAR) • GCJ ≥ 4.0 (shared library) • Does not work with: • Kaffe • SableVM 28
- 29. GCJ Issues • Missingjava.security implementation • GCJ-based PL/Java installations are untrusted. 29
- 30. Supported Platforms • Linux(most architectures) • Cygwin • Windows (PostgreSQL 8.1/recent) • More reports welcome! 30
- 31. Compatibility • vs. Oracle: • data type system not as good • trigger procedures not compatible (wrappers possible) • vs. DB/2, Firebird, ...: • unknown 31
- 32. The Future • Dynamictype system (SQL:2003) • Work on SQL conformance and compatibility • More work on J4SQL • Cooperation with PL/J project 32
- 33. Conclusion • PL/Java is stable today. • It is being used. • It is almost feature complete. • Get it now! http://gborg.postgresql.org/project/pljava/projdisplay.php 33