[PostgreSQL] ENUM type declaration with exception handling fails

[ditsuke]

Steps to reproduce the issue

Try installing Joomla with a PostgreSQL database and commit hash b130fc21cb7eea618f31d0011d4e093efcedd88e (branch plgapi). The installation fails with:

42601, 7, ERROR: cannot insert multiple commands into a prepared statement

UPDATE

As of the latest commit, I identified that moving the SQL before the declaration for soundex fixes the previous error (which was still occurring with the removal of enums)

• After shifting the com_cronjobs SQL statements to before the soundex declaration, the installation fails with:

  42601, 7, ERROR: unterminated dollar-quoted string at or near "$$ BEGIN CREATE TYPE job_type AS enum ('script', 'plugin')" LINE 2: $$ ^
  42601, 7, ERROR: syntax error at or near "EXCEPTION" LINE 1: EXCEPTION ^
  42601, 7, ERROR: unterminated dollar-quoted string at or near "$$" LINE 2: $$ ^
  42601, 7, ERROR: unterminated dollar-quoted string at or near "$$ BEGIN CREATE TYPE trigger_type AS enum ('pseudo_cron', 'cron', 'visit_count')" LINE 2: $$ ^

• Without the DO $$ block required for exception handling, installation/update scripts will fail if the types job_type / trigger already exist. Otherwise, the types are created successfully and installation completes.

Expected result

Joomla is able to run the DB complete scripts and complete installation.

Actual result

DB scripts fail when it gets to the ENUM type declarations.

• Old error

System information (as much as possible)

• DB: PostgreSQL 13 (or lower)
• Platform: Any

Additional comments

• While this issue is not fixed, the PostgreSQL tables will use a string type where ENUMs are currently used.
• Without the DO block, ENUM declarations work but fail if the types are already defined.
• The failing ENUM declarations for reference:

  
  DO
  $$
  BEGIN
      CREATE TYPE job_type AS enum ('script', 'plugin');
  EXCEPTION
      WHEN duplicate_object THEN NULL;
  END
  $$;

DO $$ BEGIN CREATE TYPE trigger_type AS enum ('pseudo_cron', 'cron', 'visit_count'); EXCEPTION WHEN duplicate_object THEN NULL; END $$;