Tom Lane <t...@sss.pgh.pa.us> writes: > Thinking about this some more ... it seems like we now need two separate > views, because there is some information that could change per-version, > and some that really only makes sense at the per-extension level.
Makes sense. > For instance, we could have pg_available_extensions that produces a row > per primary control file, with columns > > name (view's effective primary key) > default_version > installed_version (NULL if not installed) > comment (if one is present in primary control file) Check. > and pg_available_extension_versions that produces a row per install > script, with columns > > name > version ((name, version) is primary key) > comment > requires > relocatable > schema > > where the last four columns can vary across versions due to secondary > control files. I like this primary key because that's also the one for debian stable distributions :) Joking apart, aren't we missing the encoding somewhere? > Or we could combine these into just one view with pkey (name, version), > but then the default_version and installed_version columns would be the > same across all rows with the same extension name, which seems confusing > and unnormalized. Let's go with two views. Once we have that it's easy enough to LEFT JOIN if we want a summarized view. Maybe we could even revive \dX. Without pattern it would show the short form (pg_available_extension) and given a pattern pg_available_extension_versions. > I suggest instead that we invent a SRF, say > pg_extension_update_paths(extension_name text) returns setof record, > that returns a row for each pair of distinct version names found in > the extension's install and update scripts, with columns Agreed. > source version name > target other version name > path update path from source to target, or NULL if none > > The output might look like this: > > 1.0 1.1 1.0--1.1 > 1.1 1.2 1.1--1.2 > unpackaged 1.0 unpackaged--1.0 > 1.0 1.2 1.0--1.1--1.2 > 1.0 unpackaged > 1.1 1.0 > 1.1 unpackaged > 1.2 1.1 > 1.2 1.0 > 1.2 unpackaged > unpackaged 1.1 unpackaged--1.0--1.1 > unpackaged 1.2 unpackaged--1.0--1.1--1.2 What about having this chain column be an array of version strings? If you want to see it this way, use array_to_string(path, '--')… > where the first three rows correspond to available update scripts and > the rest are synthesized. The ordering is not clearly apparent, but I don't think it matters. > (Looking at this, it looks like it could get pretty bulky pretty > quickly. Maybe we should eliminate all rows in which the path would be > NULL? Or just eliminate rows in which the target doesn't have an > install script, which would remove the three rows with target = > unpackaged in the above example?) Removing NULL path rows seems the best option to me. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers