# stored procedures

Published articles for stored procedures.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Why You Should Execute jOOQ Queries With jOOQ

DevFeed: [Why You Should Execute jOOQ Queries With jOOQ](<https://devfeed.tech/articles/why-you-should-execute-jooq-queries-with-jooq-28974.md>)

Original publisher: [Read original article](<https://blog.jooq.org/why-you-should-execute-jooq-queries-with-jooq/>)

Author: lukaseder

Published: 2023-01-18T11:46:25Z

Content type: opinion

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Java](<https://devfeed.tech/topics/java.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [Compiler](<https://devfeed.tech/topics/compiler.md>)

Tags: [code-generation](<https://devfeed.tech/tags/code-generation.md>), [crud](<https://devfeed.tech/tags/crud.md>), [csv](<https://devfeed.tech/tags/csv.md>), [execution](<https://devfeed.tech/tags/execution.md>), [export](<https://devfeed.tech/tags/export.md>), [identity](<https://devfeed.tech/tags/identity.md>), [import](<https://devfeed.tech/tags/import.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [jpa](<https://devfeed.tech/tags/jpa.md>), [json](<https://devfeed.tech/tags/json.md>), [mapping](<https://devfeed.tech/tags/mapping.md>), [multiset](<https://devfeed.tech/tags/multiset.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [nested-records](<https://devfeed.tech/tags/nested-records.md>), [orm](<https://devfeed.tech/tags/orm.md>), [r2dbc](<https://devfeed.tech/tags/r2dbc.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [type-safety](<https://devfeed.tech/tags/type-safety.md>), [udt](<https://devfeed.tech/tags/udt.md>), [updatablerecord](<https://devfeed.tech/tags/updatablerecord.md>), [user-defined-types](<https://devfeed.tech/tags/user-defined-types.md>), [xml](<https://devfeed.tech/tags/xml.md>)

### AI overview

The article argues that queries built with jOOQ should generally also be executed with jOOQ. It explains that jOOQ preserves type safety and structured result mapping during execution, whereas extracting SQL and bind values for JDBC-level execution loses compiler knowledge of the result set. It acknowledges limited use cases for executing jOOQ-generated SQL elsewhere, such as a small number of dynamic queries in an otherwise JPA-based application.

### Source excerpt

Previously on this blog, I've written a post explaining why you should use jOOQ's code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I've answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on: jOOQ itself isn't ... Continue reading Why You Should Execute jOOQ Queries With jOOQ ->

## When to Use jOOQ and When to Use Native SQL

DevFeed: [When to Use jOOQ and When to Use Native SQL](<https://devfeed.tech/articles/when-to-use-jooq-and-when-to-use-native-sql-28972.md>)

Original publisher: [Read original article](<https://blog.jooq.org/when-to-use-jooq-and-when-to-use-native-sql/>)

Author: lukaseder

Published: 2022-12-08T13:53:02Z

Content type: comparison

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [rdbms](<https://devfeed.tech/topics/rdbms.md>), [Test-driven development](<https://devfeed.tech/topics/tdd.md>), [Testcontainers](<https://devfeed.tech/topics/testcontainers.md>)

Tags: [cons](<https://devfeed.tech/tags/cons.md>), [examples](<https://devfeed.tech/tags/examples.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jdbctemplate](<https://devfeed.tech/tags/jdbctemplate.md>), [jdbi](<https://devfeed.tech/tags/jdbi.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [native-sql](<https://devfeed.tech/tags/native-sql.md>), [plain-jdbc](<https://devfeed.tech/tags/plain-jdbc.md>), [plain-sql-templating](<https://devfeed.tech/tags/plain-sql-templating.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-json](<https://devfeed.tech/tags/sql-json.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [tdd](<https://devfeed.tech/tags/tdd.md>), [testcontainers](<https://devfeed.tech/tags/testcontainers.md>)

### AI overview

This comparison explains when to use the jOOQ API and when to use native SQL for complex queries. It describes jOOQ's advantages for dynamic SQL, multiple database dialects, type safety, data mapping, stored procedures, security, exports, and diagnostics, while noting usability drawbacks for large static queries with many CTEs or derived tables.

### Source excerpt

A frequently encountered doubt people have when using jOOQ is to decide when a "complex" query should be written using jOOQ API vs. when it should be implemented using native SQL. The jOOQ manual is full of side by side examples of the same query, e.g. Using jOOQ: Using native SQL: In the native SQL ... Continue reading When to Use jOOQ and When to Use Native SQL ->

## Calling Procedures with Default Parameters using JDBC or jOOQ

DevFeed: [Calling Procedures with Default Parameters using JDBC or jOOQ](<https://devfeed.tech/articles/calling-procedures-with-default-parameters-using-jdbc-or-jooq-28932.md>)

Original publisher: [Read original article](<https://blog.jooq.org/calling-procedures-with-default-parameters-using-jdbc-or-jooq/>)

Author: lukaseder

Published: 2022-10-21T14:35:39Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Code](<https://devfeed.tech/topics/code.md>), [Reason](<https://devfeed.tech/topics/reason.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Oracle Database](<https://devfeed.tech/topics/oracle-database.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [db2](<https://devfeed.tech/tags/db2.md>), [default-parameters](<https://devfeed.tech/tags/default-parameters.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [pl-pgsql](<https://devfeed.tech/tags/pl-pgsql.md>), [pl-sql](<https://devfeed.tech/tags/pl-sql.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql-server](<https://devfeed.tech/tags/sql-server.md>), [stored-functions](<https://devfeed.tech/tags/stored-functions.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [syntax](<https://devfeed.tech/tags/syntax.md>)

### AI overview

This tutorial explains how to call stored procedures with default parameters using JDBC or jOOQ. It shows how directly instantiating a procedure call allows Java code to omit parameters that have database-side defaults, and notes that syntax varies across supported relational database systems.

### Source excerpt

Using jOOQ's code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: jOOQ will generate code for you to call very simply, like this: This will execute the following, taking care of binding all IN and OUT parameters for ... Continue reading Calling Procedures with Default Parameters using JDBC or jOOQ ->

## How to Integration Test Stored Procedures with jOOQ

DevFeed: [How to Integration Test Stored Procedures with jOOQ](<https://devfeed.tech/articles/how-to-integration-test-stored-procedures-with-jooq-28942.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-integration-test-stored-procedures-with-jooq/>)

Author: lukaseder

Published: 2022-08-22T12:36:00Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Testing](<https://devfeed.tech/topics/testing.md>), [Database](<https://devfeed.tech/topics/database.md>), [Testcontainers](<https://devfeed.tech/topics/testcontainers.md>), [Java](<https://devfeed.tech/topics/java.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [code-generation](<https://devfeed.tech/tags/code-generation.md>), [database](<https://devfeed.tech/tags/database.md>), [gradle](<https://devfeed.tech/tags/gradle.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [integration-test](<https://devfeed.tech/tags/integration-test.md>), [integration-testing](<https://devfeed.tech/tags/integration-testing.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [junit](<https://devfeed.tech/tags/junit.md>), [maven](<https://devfeed.tech/tags/maven.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [testcontainers](<https://devfeed.tech/tags/testcontainers.md>), [unit-test](<https://devfeed.tech/tags/unit-test.md>)

### AI overview

This tutorial explains how to integration-test database stored procedures and functions with Java, jOOQ, Testcontainers, and JUnit. It shows how to run PostgreSQL in Testcontainers, generate jOOQ classes for stored procedures, and reuse Java test infrastructure instead of manually binding to procedures through JDBC.

### Source excerpt

When you write stored procedures and functions in your database, you want to ensure their correctness, just like with your Java code. In Java, this is done with unit tests, typically with JUnit. For example, if you have the following code in Java: Then, you might write a test like this: But how do we ... Continue reading How to Integration Test Stored Procedures with jOOQ ->

## The Best Way to Call Stored Procedures from Java: With jOOQ

DevFeed: [The Best Way to Call Stored Procedures from Java: With jOOQ](<https://devfeed.tech/articles/the-best-way-to-call-stored-procedures-from-java-with-jooq-28961.md>)

Original publisher: [Read original article](<https://blog.jooq.org/the-best-way-to-call-stored-procedures-from-java-with-jooq/>)

Author: lukaseder

Published: 2022-07-28T13:11:59Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Code generation](<https://devfeed.tech/topics/code-generation.md>), [Java](<https://devfeed.tech/topics/java.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [data-processing](<https://devfeed.tech/topics/data-processing.md>), [Server](<https://devfeed.tech/topics/server.md>)

Tags: [code-generation](<https://devfeed.tech/tags/code-generation.md>), [code-generator](<https://devfeed.tech/tags/code-generator.md>), [data-processing](<https://devfeed.tech/tags/data-processing.md>), [java](<https://devfeed.tech/tags/java.md>), [jdbc](<https://devfeed.tech/tags/jdbc.md>), [jdbc-drivers](<https://devfeed.tech/tags/jdbc-drivers.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [oracle](<https://devfeed.tech/tags/oracle.md>), [pl-pgsql](<https://devfeed.tech/tags/pl-pgsql.md>), [pl-sql](<https://devfeed.tech/tags/pl-sql.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [rdbms](<https://devfeed.tech/tags/rdbms.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stored-functions](<https://devfeed.tech/tags/stored-functions.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [t-sql](<https://devfeed.tech/tags/t-sql.md>), [udts](<https://devfeed.tech/tags/udts.md>)

### AI overview

This tutorial explains how to call stored procedures from Java using jOOQ-generated code. It contrasts JDBC calls with generated procedure stubs and convenience methods, covering parameter handling, database types, and support for procedures, functions, packages, and UDTs.

### Source excerpt

jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This ... Continue reading The Best Way to Call Stored Procedures from Java: With jOOQ ->

## PostgreSQL Data Types: XML

DevFeed: [PostgreSQL Data Types: XML](<https://devfeed.tech/articles/postgresql-data-types-xml-34595.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2018/04/postgresql-data-types-xml/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2018-04-23T16:18:48Z

Content type: tutorial

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [XML](<https://devfeed.tech/topics/xml.md>), [data type](<https://devfeed.tech/topics/data-type.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Databases](<https://devfeed.tech/topics/databases.md>)

Tags: [data-type](<https://devfeed.tech/tags/data-type.md>), [indexing](<https://devfeed.tech/tags/indexing.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>), [sql-database](<https://devfeed.tech/tags/sql-database.md>), [sql-xml](<https://devfeed.tech/tags/sql-xml.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [transformation](<https://devfeed.tech/tags/transformation.md>), [xml](<https://devfeed.tech/tags/xml.md>)

### AI overview

This article introduces PostgreSQL's XML data type and its SQL/XML support for storing and manipulating XML in a database. It also discusses PL/XSLT for XML processing and notes that PostgreSQL's XML processing and indexing capabilities are limited.

### Source excerpt

Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL XML type. The SQL standard includes a SQL/XML which introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database, as per the Wikipedia page.

## Migrating Sakila from MySQL to PostgreSQL

DevFeed: [Migrating Sakila from MySQL to PostgreSQL](<https://devfeed.tech/articles/migrating-sakila-from-mysql-to-postgresql-34530.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2013/11/migrating-sakila-from-mysql-to-postgresql/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2013-11-12T10:37:00Z

Content type: tutorial

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [MySQL](<https://devfeed.tech/topics/mysql.md>), [Database](<https://devfeed.tech/topics/database.md>), [data](<https://devfeed.tech/topics/data.md>)

Tags: [array](<https://devfeed.tech/tags/array.md>), [conference](<https://devfeed.tech/tags/conference.md>), [conversion](<https://devfeed.tech/tags/conversion.md>), [database](<https://devfeed.tech/tags/database.md>), [enum](<https://devfeed.tech/tags/enum.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [migrate](<https://devfeed.tech/tags/migrate.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [parallel](<https://devfeed.tech/tags/parallel.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [types](<https://devfeed.tech/tags/types.md>)

### AI overview

This tutorial demonstrates migrating the Sakila example database from MySQL to PostgreSQL with pgloader. It covers schema discovery, data type casting, transformations, default values, parallel index building, and limitations involving application queries, triggers, stored procedures, and views.

### Source excerpt

As presented at the PostgreSQL Conference Europe the new version of pgloader is now able to fully migrate a MySQL database, including discovering the schema, casting data types, transforming data and default values. Sakila is the traditional MySQL example database, in this article we're going to fully migrate it over to PostgreSQL.

## Extension White Listing

DevFeed: [Extension White Listing](<https://devfeed.tech/articles/extension-white-listing-34464.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2012/03/extension-white-listing/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2012-03-08T13:25:00Z

Content type: tutorial

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Security](<https://devfeed.tech/topics/security.md>), [Heroku](<https://devfeed.tech/topics/heroku.md>)

Tags: [extension](<https://devfeed.tech/tags/extension.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [security](<https://devfeed.tech/tags/security.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>)

### AI overview

This technical article explains how pgextwlist can let PostgreSQL database owners install approved C-coded extensions without superuser access. It describes the security risk of unrestricted C extensions and the configuration needed to whitelist authorized extensions.

### Source excerpt

PostgreSQL 9.1 includes proper extension support, as you might well know if you ever read this very blog here. Some hosting facilities are playing with PostgreSQL at big scale (hello Heroku!) and still meet with small caveats making their life uneasy. To be specific, only superusers are allowed to install C coded stored procedures, and that impacts a lot of very useful PostgreSQL extension: all those shiped in the contrib package are coded in C. Now, Heroku is not giving away superuser access to their hosted customers in order to limit the number of ways they can shoot themselves in the foot. And given PostgreSQL security model, being granted database owner is mostly good enough for day to day operation.

## Scaling Stored Procedures

DevFeed: [Scaling Stored Procedures](<https://devfeed.tech/articles/scaling-stored-procedures-34460.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2011/10/scaling-stored-procedures/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2011-10-06T16:23:00Z

Content type: opinion

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Scalability](<https://devfeed.tech/topics/scalability.md>), [postgresql clusters](<https://devfeed.tech/topics/postgresql-clusters.md>), [Architecture & Design](<https://devfeed.tech/topics/architecture-design.md>)

Tags: [api](<https://devfeed.tech/tags/api.md>), [cluster](<https://devfeed.tech/tags/cluster.md>), [database](<https://devfeed.tech/tags/database.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [scalability](<https://devfeed.tech/tags/scalability.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>), [upgrade](<https://devfeed.tech/tags/upgrade.md>), [version](<https://devfeed.tech/tags/version.md>)

### AI overview

The article argues that stored procedures do not inherently prevent database scalability. It describes PostgreSQL Extensions for managing procedure code and versions, and PLproxy for scaling out across database shards with a shared procedural API.

### Source excerpt

In the news recently stored procedures where used as an excuse for moving away logic from the database layer to application layer, and to migrate away from a powerful technology to a simpler one, now that there's no logic anymore in the database. It's not the way I would typically approach scaling problems, and apparently I'm not alone on the Stored Procedures camp. Did you read this nice blog post Mythbusters: Stored Procedures Edition already? Well it happens in another land that where my comfort zone is, but still has some interesting things to say.

## Emacs and PostgreSQL

DevFeed: [Emacs and PostgreSQL](<https://devfeed.tech/articles/emacs-and-postgresql-34372.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2010/07/emacs-and-postgresql/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2010-07-22T07:30:00Z

Content type: opinion

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [Emacs](<https://devfeed.tech/topics/emacs.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Open Source](<https://devfeed.tech/topics/open-source.md>), [free software](<https://devfeed.tech/topics/free-software.md>), [Code review](<https://devfeed.tech/topics/code-review.md>)

Tags: [emacs](<https://devfeed.tech/tags/emacs.md>), [emacs-lisp](<https://devfeed.tech/tags/emacs-lisp.md>), [free-software](<https://devfeed.tech/tags/free-software.md>), [open-source](<https://devfeed.tech/tags/open-source.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [stored-procedures](<https://devfeed.tech/tags/stored-procedures.md>)

### AI overview

An opinion piece compares Emacs and PostgreSQL, highlighting their documentation, extensibility, and ability to be modified while running. It also introduces an Emacs linum-mode extension for displaying PostgreSQL-calculated line numbers in PL/pgSQL files.

### Source excerpt

Those are my two all times favorite Open Source Software. Or Free Software in the GNU sense of the world, as both the BSD and the GPL are labeled free there. Even if I prefer the The Debian Free Software Guidelines as a global definition and the WTFPL license. But that's a digression. I think that Emacs and PostgreSQL do share a lot in common. I'd begin with the documentation, which quality is amazing for both projects. Then of course the extensibility with Emacs Lisp on the one hand and catalog-driven operations on the other hand. Whether you're extending Emacs or PostgreSQL you'll find that it's pretty easy to tweak the system while it's running. The other comparison points are less important, like the fact the both the systems get about the same uptime on my laptop (currently 13 days, 23 hours, 57 minutes, 10 seconds).