search

pgcentralfoundation / pgrx

Build Postgres Extensions with Rust!
Other
3.67k stars 249 forks source link

Make raw-SQL DDL management more flexible #120

Open rustprooflabs opened 3 years ago

rustprooflabs commented 3 years ago

Details

Two related ideas for improvement to the handling of raw-SQL DDL in pgx extensions, bringing over from Discord conversation.

Part 1: Allow DDL from script

Creating raw-SQL DDL for an extension now happens in one (or more) extension_sql!(); blocks inside lib.rs. Currently I use this to create tables, views, comments, etc., and these blocks get lengthy.

extension_sql!(
    r#"
-- 100 lines of raw SQL inline, will continue to grow over time... ew.
"#
);

I would prefer to be able to remove the raw SQL from the .rs script and put in a dedicated .sql script. Something like this:

extension_sql!(include_str!("ddl_pre_pgx_v0_3.sql"));

Attempting to do so results in an error error: expected a single raw string literal with sql.

Part 2: Improve raw-DDL across extension versions

A change to the raw-SQL DDL requires adding the changes in both the SQL contained in lib.rs and in a version-to-version migration scripts, e.g. <extname>--<old_version>--<new_version>.sql. It'd be great to see pgx follow the pattern that Postgres takes with version to version scripts where subsequent versions follow the upgrade path to iterate through the version-specific DDL in order. e.g. Let me to define the initial DDL (v0.1) in a version-specific script (see Part 1). When a new feature requires DDL changes, allow me to define an additional v0.2 script to include. Installing the latest version should follow through the upgrade path to install all DDL without duplicating the code to handle clean install & upgrade separately.

Hoverbear commented 3 years ago

Part 1 is covered in #165