Thread: Additional psql requirements
I have some suggestions for additional psql features. I'm not planning to work on them myself, just proposing them so others can do so if they agree and wish to do so. * default values for substitution values Need a command to set the default value of a substitution variable, so that it takes a specific value if *not* explicitly set on the command line (or any time prior to setting the default). e.g. \default * access to version number e.g. special variables called PGMINORVERSION (= 8.3.3)PGVERSION (= 8.3) * simple mechanism for conditional execution Similar to #IFDEF e.g. \ifdef (PGVERSION >= 8.3) labelname ... \label labelname (..or simply \endif, in which case I'd like \else as well please) * ability to set substitution variables from command execution Allow the result of an SQL command to be placed in a sub variable e.g. \eval fooinfo 'select info from foo;' * an option to echo an error statement to STDERR -a echoes the SQL statement to STDOUT, so if you separate them you can't match up which SQL had which error The purpose of all of them is to enhance our ability to produce install scripts for various things. Common uses would include * setting a default schema, yet allowing overrides to that * installing languages if not already installed * checking whether other components are already correctly installed * installing things based upon the encoding of the database * skipping certain features if version incorrect * conditional execution whether superuser or not -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Simon Riggs <simon@2ndquadrant.com> wrote: > * access to version number > * simple mechanism for conditional execution > * ability to set substitution variables from command execution > * conditional execution whether superuser or not Can we use pgScript for such flow controls? http://pgscript.projects.postgresql.org/INDEX.html I'm not sure pgScript can be used in pgAdmin already, but if we support it both psql and pgAdmin, the scripting syntax will be a defact standard because they are the most major user interfaces to postgres. I think it is not good to add another "dialect" that can be used only in psql. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote: > Simon Riggs <simon@2ndquadrant.com> wrote: > > > * access to version number > > * simple mechanism for conditional execution > > * ability to set substitution variables from command execution > > * conditional execution whether superuser or not > > Can we use pgScript for such flow controls? > http://pgscript.projects.postgresql.org/INDEX.html > > I'm not sure pgScript can be used in pgAdmin already, but if we support > it both psql and pgAdmin, the scripting syntax will be a defact standard > because they are the most major user interfaces to postgres. I think it is > not good to add another "dialect" that can be used only in psql. I just want good way, not two imperfect ones. And I'm not going to suggest having pgscript in core. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Fri, Jul 25, 2008 at 08:16:59AM +0100, Simon Riggs wrote: > > On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote: > > Simon Riggs <simon@2ndquadrant.com> wrote: > > > > > * access to version number > > > * simple mechanism for conditional execution > > > * ability to set substitution variables from command execution > > > * conditional execution whether superuser or not > > > > Can we use pgScript for such flow controls? > > http://pgscript.projects.postgresql.org/INDEX.html > > > > I'm not sure pgScript can be used in pgAdmin already, but if we support > > it both psql and pgAdmin, the scripting syntax will be a defact standard > > because they are the most major user interfaces to postgres. I think it is > > not good to add another "dialect" that can be used only in psql. > > I just want good way, not two imperfect ones. > > And I'm not going to suggest having pgscript in core. It seems to me that a sql-like client side scripting language should be as similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is pretty much incompatible with it for no particularly obvious reason. -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
2008/7/25 Simon Riggs <simon@2ndquadrant.com>: > > On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote: >> Simon Riggs <simon@2ndquadrant.com> wrote: >> >> > * access to version number >> > * simple mechanism for conditional execution >> > * ability to set substitution variables from command execution >> > * conditional execution whether superuser or not >> >> Can we use pgScript for such flow controls? >> http://pgscript.projects.postgresql.org/INDEX.html >> >> I'm not sure pgScript can be used in pgAdmin already, but if we support >> it both psql and pgAdmin, the scripting syntax will be a defact standard >> because they are the most major user interfaces to postgres. I think it is >> not good to add another "dialect" that can be used only in psql. > > I just want good way, not two imperfect ones. > > And I'm not going to suggest having pgscript in core. + 1 pgScript is too heavy for most purposes is enough some like \for select * from information_schema.tables grant read on $1 to public; \endfor regards Pavel Stehule > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg@sonic.net> wrote: > It seems to me that a sql-like client side scripting language should be as > similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is > pretty much incompatible with it for no particularly obvious reason. pgScript originally used a c-like syntax when it was pgUnitTest iirc. The new version is designed to be familiar to users of T-SQL. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
2008/7/25 Dave Page <dpage@pgadmin.org>: > On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg@sonic.net> wrote: > >> It seems to me that a sql-like client side scripting language should be as >> similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is >> pretty much incompatible with it for no particularly obvious reason. > > pgScript originally used a c-like syntax when it was pgUnitTest iirc. > The new version is designed to be familiar to users of T-SQL. it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and it's far to plpgsql regards Pavel Stehule > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Fri, Jul 25, 2008 at 9:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2008/7/25 Dave Page <dpage@pgadmin.org>: >> On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg@sonic.net> wrote: >> >>> It seems to me that a sql-like client side scripting language should be as >>> similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is >>> pretty much incompatible with it for no particularly obvious reason. >> >> pgScript originally used a c-like syntax when it was pgUnitTest iirc. >> The new version is designed to be familiar to users of T-SQL. > > it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and > it's far to plpgsql I see no point in replicating pl/pgsql. Better to implement anonymous blocks in the server for that. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Fri, 2008-07-25 at 09:40 +0100, Dave Page wrote: > On Fri, Jul 25, 2008 at 9:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > 2008/7/25 Dave Page <dpage@pgadmin.org>: > >> On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg@sonic.net> wrote: > >> > >>> It seems to me that a sql-like client side scripting language should be as > >>> similar as possible to plpgsql. Pgscript looks a bit like plpgsql, but is > >>> pretty much incompatible with it for no particularly obvious reason. > >> > >> pgScript originally used a c-like syntax when it was pgUnitTest iirc. > >> The new version is designed to be familiar to users of T-SQL. > > > > it is little bit unhappy - it's like T-SQL, but it isn't T-SQL - and > > it's far to plpgsql > > I see no point in replicating pl/pgsql. Better to implement anonymous > blocks in the server for that. Agreed. My suggestion was for something much simpler than either. Complex logic can be done in functions. I just wanted an easy way to write install scripts that work on various releases/schemas/environments, works on core and on any platform. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Simon Riggs <simon@2ndquadrant.com> writes: > I just wanted an easy way to write install scripts that work on various > releases/schemas/environments, works on core and on any platform. The word "easy" is out of place in that sentence. Such scripts would likely need information that's entirely outside the province of the database proper --- directory paths, system names and versions, etc. I really doubt that anything that could get accepted as a "database scripting" feature would solve such problems. regards, tom lane