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.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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.com
PostgreSQL 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(at)2ndquadrant(dot)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(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.

2008/7/25 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>
> On Fri, 2008-07-25 at 10:00 +0900, ITAGAKI Takahiro wrote:
>> Simon Riggs <simon(at)2ndquadrant(dot)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(at)postgresql(dot)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(at)sonic(dot)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(at)pgadmin(dot)org>:
> On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg(at)sonic(dot)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(at)postgresql(dot)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(dot)stehule(at)gmail(dot)com> wrote:
> 2008/7/25 Dave Page <dpage(at)pgadmin(dot)org>:
>> On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg(at)sonic(dot)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(dot)stehule(at)gmail(dot)com> wrote:
> > 2008/7/25 Dave Page <dpage(at)pgadmin(dot)org>:
> >> On Fri, Jul 25, 2008 at 8:52 AM, daveg <daveg(at)sonic(dot)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.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndquadrant(dot)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