Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> 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