Tom Lane wrote: > Andrew Dunstan <andrew@xxxxxxxxxxxx> writes: >> Part of the motivation for allowing inline blocks was to allow for >> conditional logic. > > I don't think that argument really applies to this case, because the > complaint was about not being sure if plpgsql is installed. If it > isn't, you can hardly use a plpgsql DO block to fix it. > > (Is anyone up for revisiting the perennial topic of whether to install > plpgsql by default? Andrew's argument does suggest that DO might offer > a new consideration in that tradeoff.) It'd be a HUGE benefit in deployment and update scripts to have PL/PgSQL installed and available by default, at least to the superuser and to the DB owner. One issue I run into with DB deployment is that a schema often requires several roles. If the schema has been imported into another (possibly since-dropped) database in the cluster before, global changes such as role creations will fail since they've already been done by a prior run. This makes it necessary to split the schema into global and database-specific parts or to ignore errors that arise as the SQL is processed. Neither option lets me reasonably apply a schema update transactionally. Having PL/PgSQL available right from an initial connection to `template1' as superuser for a 'CREATE DATABASE' would be great, as I could use appropriate logic to avoid or handle errors, and could run schema create/update scripts with ON_ERROR_ROLLBACK . If CREATE LANGUAGE silently succeeded if the language was already installed with the same params, perhaps PL/PgSQL could be made available by default with no impact on existing scripts and apps? Is there any harm in making it succeed if it need take no action to achieve the requested state? After all, the end result is as the user requested. Do they really care whether CREATE LANGUAGE had to modify the catalogs? As for CREATE [USER|ROLE] ... IF NOT EXISTS; I was concerned about just that issue, which is why I was unsure whether it was sane for users and roles. Being able to easily test for the presence of a user (say, within a DO block with default-installed PL/PgSQL) would be nicer and safer than having ... IF EXISTS for users/roles. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
