Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Fabien COELHO


Hello Robert,


Calling the server is already available:

  SELECT  AS varname \gset


Sure, but people are going to want to do it inline with the \if.


Yes... and my changed opinion is that the answer to this approach should 
be "no", only client side after if.



Anything that can be done that way can also be done this way, but
people will want it just to make the code look nicer.


That is what I thought, but I have not seen any sane/nice solution, and I 
wish to avoid the opposite.


I now think that whether an expression is server side or client side 
should be cristal clear, thus the rule "write a SELECT for server-side" 
and "write a backslash command" for client-side is pretty attractive.


I do not think it is so bad: this is probably a rare occurence (psql spent 
22 years without "\if") ; for server side expressions, it means that an 
intermediate meaningful variable name must be thought for, which is not 
necessarily a bad thing ; any significant SQL query would not fit cleanly 
on one line, especially if made longer by a special prefix.


Finally, it does not bring any new semantics.

I don't think we should restrict \if to be ONLY an SQL callout, but if 
people want that as an option, and I bet they do, then I think we should 
give it to them.


I changed my mind on this one. I think we should not for the reason stated 
above.


Now it would be possible to have some compromise, and we could accept some 
ugly prefix to mark server-side expressions after \if and no special 
prefix would mean client-side, but the I would prefer if we avoid that.



I somewhat disagree: Does building postgres should depend on lua? I think
adding such a mandatory dependency would not be a good idea. If it is not
mandatory, then it would mean that psql could be compiled with or without
lua embedding, thus psql would not be dependable because features may or may
not be available when writing a "psql script".


That's true, but you could say the same thing about SSL or NLS.


Hmmm. I'm not sure how NLS or SSL would show up inside a psql-script.

Another point I would like to make is that lua popularity is somewhere 
between COBOL and Fortran on the Tiobe index.


[...] I don't think it's likely that adding one or two additional simple 
constructs is going to be sufficient to keep people from wanting more.


I think that the next hurdle is high enough for not being jumped over in a 
hurry: for getting a while, one need to re-execute the body over and over, 
which requires holding the lines somewhere, meaning an significant 
infrastructure which does not exists. So someone would have to need it 
really badly to spend time on this one.



Generating a error message with ${foo:?} is nice, but what psql need is just
a way to test whether a variable is defined or not.


I'm not saying we should slavishly follow bash, but don't confuse what
you need with what other people need.  bash (well, sh, really) grew
that syntax for a reason, and it may be more than "there was this one
guy back in the seventies who wanted it, and ...".


Sure. I think that the reason is to be able to write shell scripts without 
bothering with undefined variables error handling. Lazy programmers:-)


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Pavel Stehule
2017-04-13 19:46 GMT+02:00 Corey Huinker :

>
>> > I suggest to reuse pgbench expression engine, developed by Haas
>> Robert:-)
>>
>> Not a bad idea (though I'm sure there are other reasonable options, too).
>>
>>
> I don't want to stand in the way of any progress in getting expressions
> into \if and some subspecies of \set. But, assuming we don't get it into
> v10, our documentations currently says this about expressions:
>
> Expressions that do not properly evaluate to true or false will generate a
> warning and be treated as false.
>
>
> We should probably amend that to say something about the potential future
> directions of expressions, perhaps this:
>
> Expressions that do not properly evaluate to true or false will generate a
> warning and be treated as false, though that behavior is subject to change
> in future versions of psql.
>
>
> That should guard us against people getting too attached to that behavior
> in the interim.
>
> With that said, I'm starting to like the idea of not boxing ourselves into
> one type of expression. Maybe the first token could be the expression
> context with the expression to follow
>
> The expression type we have now
> \if true
>
> "defined" is a context (or "mode") that expects one token that might be a
> psql varname
> \if defined varname
>
> "sql" is a context that treats the rest of the line as a SQL statement to
> the current connection, and looks at the first column of first row for
> "truth"
> \if sql SELECT EXISTS(SELECT null FROM item WHERE manufacturer = 'Frobozz')
>
> "pgbench" could invoke the pgbench expression engine.
> \if pgbench 
>
> Anything else is treated as an external expression evaluator. If an
> expression has an unknown context "foo", check the ENV vars for EXPR_FOO,
> and pipe the remaining expression tokens to $PSQL_EXPR_FOO if it exists,
> and read the output of that for psql-boolean truth.  I think having a
> context/mode token could allow us to have lots of pluggable expression
> types with minimal effort to psql itself.
>
> "python" invokes a python interpreter (if PSQL_EXPR_PYTHON is defined,
> fails otherwise)
> \if python print(:'varname' == 'Approved' or :'othervar' == 'Special')
> which would echo
> print('Approved' == 'Approved' or 'Regular' == 'Special')
> to python, which would give the response "True", which is true
>
> likewise with "bash" (assuming PSQL_EXPR_BASH=bash)
> \if bash expr :'varname' = 'Approved'
> would echo
> expr 'Approved' = 'Approved'
> to bash, which would return 1, which would be true.
>
> So we'd get all that, with only having to internally code for an external
> launcher, naked booleans, pgbench, defined, and I suppose we should have a
> negation
>
>
> \if not  
>
>
> Which I guess would allow
>
> \if not not not  
>
>
it looks like overengineering - I don't think so string comparation should
be supported in two three languages.

Can live with it, but more prefer simple pgbench only language there

The one line commands is limited due readability

we can introduce "language blocks" where can be possible set some values.
This looks really scary.

bash is supported already

probably you can write today

\if `basexpr`





>
> For consistency, we might want to change the default context to require an
> explicit "bool", so
>
> \if bool true
>
> but if we want to do that, we should change it very soon.
>
> tl;dr:
> My proposal is:
> * do the bare minimum of expression testing in psql (simple scalar truth,
> variable definition, negation)
> * do platform independent client-only-expressions in pgbench mode
> * allow inline \gset-ish expressions with sql-mode
> * and allow for platform/install dependent expressions via PSQL_EXPR_* env
> vars.
>


Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Pavel Stehule
>
> > I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
>
> Not a bad idea (though I'm sure there are other reasonable options, too).
>

I checked the pgbench code - and I think it can work well - just add
logical operators and compare operators.

Don't need to create more complex language there.


> > Generating a error message with ${foo:?} is nice, but what psql need is
> just
> > a way to test whether a variable is defined or not.
>
> I'm not saying we should slavishly follow bash, but don't confuse what
> you need with what other people need.  bash (well, sh, really) grew
> that syntax for a reason, and it may be more than "there was this one
> guy back in the seventies who wanted it, and ...".
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>


Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Corey Huinker
>
>
> > I suggest to reuse pgbench expression engine, developed by Haas Robert:-)
>
> Not a bad idea (though I'm sure there are other reasonable options, too).
>
>
I don't want to stand in the way of any progress in getting expressions
into \if and some subspecies of \set. But, assuming we don't get it into
v10, our documentations currently says this about expressions:

Expressions that do not properly evaluate to true or false will generate a
warning and be treated as false.


We should probably amend that to say something about the potential future
directions of expressions, perhaps this:

Expressions that do not properly evaluate to true or false will generate a
warning and be treated as false, though that behavior is subject to change
in future versions of psql.


That should guard us against people getting too attached to that behavior
in the interim.

With that said, I'm starting to like the idea of not boxing ourselves into
one type of expression. Maybe the first token could be the expression
context with the expression to follow

The expression type we have now
\if true

"defined" is a context (or "mode") that expects one token that might be a
psql varname
\if defined varname

"sql" is a context that treats the rest of the line as a SQL statement to
the current connection, and looks at the first column of first row for
"truth"
\if sql SELECT EXISTS(SELECT null FROM item WHERE manufacturer = 'Frobozz')

"pgbench" could invoke the pgbench expression engine.
\if pgbench 

Anything else is treated as an external expression evaluator. If an
expression has an unknown context "foo", check the ENV vars for EXPR_FOO,
and pipe the remaining expression tokens to $PSQL_EXPR_FOO if it exists,
and read the output of that for psql-boolean truth.  I think having a
context/mode token could allow us to have lots of pluggable expression
types with minimal effort to psql itself.

"python" invokes a python interpreter (if PSQL_EXPR_PYTHON is defined,
fails otherwise)
\if python print(:'varname' == 'Approved' or :'othervar' == 'Special')
which would echo
print('Approved' == 'Approved' or 'Regular' == 'Special')
to python, which would give the response "True", which is true

likewise with "bash" (assuming PSQL_EXPR_BASH=bash)
\if bash expr :'varname' = 'Approved'
would echo
expr 'Approved' = 'Approved'
to bash, which would return 1, which would be true.

So we'd get all that, with only having to internally code for an external
launcher, naked booleans, pgbench, defined, and I suppose we should have a
negation


\if not  


Which I guess would allow

\if not not not  


For consistency, we might want to change the default context to require an
explicit "bool", so

\if bool true

but if we want to do that, we should change it very soon.

tl;dr:
My proposal is:
* do the bare minimum of expression testing in psql (simple scalar truth,
variable definition, negation)
* do platform independent client-only-expressions in pgbench mode
* allow inline \gset-ish expressions with sql-mode
* and allow for platform/install dependent expressions via PSQL_EXPR_* env
vars.


Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Robert Haas
On Thu, Apr 13, 2017 at 8:56 AM, Fabien COELHO  wrote:
> Calling the server is already available:
>
>   SELECT  AS varname \gset

Sure, but people are going to want to do it inline with the \if.
Anything that can be done that way can also be done this way, but
people will want it just to make the code look nicer.   I don't think
we should restrict \if to be ONLY an SQL callout, but if people want
that as an option, and I bet they do, then I think we should give it
to them.

> I somewhat disagree: Does building postgres should depend on lua? I think
> adding such a mandatory dependency would not be a good idea. If it is not
> mandatory, then it would mean that psql could be compiled with or without
> lua embedding, thus psql would not be dependable because features may or may
> not be available when writing a "psql script".

That's true, but you could say the same thing about SSL or NLS.  In
practice, any vendor distribution of PostgreSQL will be built with
those options even though, for good reason, they are not hard
dependencies.  I don't see why this should be any different, assuming
the dependency is something that those vendors are for the most part
already packaging anyway.

> Does programming as such in psql is such a good idea?

I think we've pretty much crossed that line already with \if.

> ISTM that cpp-like capabilities (include, if, variables, some expressions)
> are somewhat both useful and enough for the limited use cases I have
> encountered. Similar languages are offered in other instances, such as
> readline inputrc or vim vimrc.

Yeah, but nobody would claim that cpp is a fun thing to program with,
and it's pretty clear from discussions here that some people are
squeezing last possible bit of juice out of every existing facility
and still wanting more.  I don't think it's likely that adding one or
two additional simple constructs is going to be sufficient to keep
people from wanting more.  I mean, I would have switched to Perl and
DBD::Pg rather than write some of the crazy psql scripts that have
been posted here, but we're not building PostgreSQL to meet my needs
particularly.

> I suggest to reuse pgbench expression engine, developed by Haas Robert:-)

Not a bad idea (though I'm sure there are other reasonable options, too).

> Generating a error message with ${foo:?} is nice, but what psql need is just
> a way to test whether a variable is defined or not.

I'm not saying we should slavishly follow bash, but don't confuse what
you need with what other people need.  bash (well, sh, really) grew
that syntax for a reason, and it may be more than "there was this one
guy back in the seventies who wanted it, and ...".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-13 Thread Fabien COELHO


Hello Robert,

My 0.02€ about your interesting questions and points.


On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane  wrote:

So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it.


I heartily agree.  There should be some kind of client-side expression
language, and one thing it should allow is calling out to the server.


Calling the server is already available:

  SELECT  AS varname \gset

What is missing is some client-side expressions.

As \if is a client-side thing, I now think that it should just rely on 
client-side evaluation. Note that it is possible to do better, but 
solutions are either ugly (strange prefixes) or too clever and possibly 
not extensible (regex filtering), and from a user experience point of view 
I finally thing that ugly or clever should be avoided.



Then people who only want to call out to the server can do that, but
people who want to do something else have the option.  Insisting that
this facility isn't allowed to do anything other than consult the
server is (1) inconsistent with what we've already got in v10 and (2)
boxing ourselves into a corner for no very good reason.

Now, the optimal shape for that client-side expression language is not
very clear to me.  Do we want to invent our own language, or maybe
consider using something that already exists?


It's been previously suggested that we should somehow embed Lua, and 
this might not be a bad place to consider doing something like that.


I somewhat disagree: Does building postgres should depend on lua? I think 
adding such a mandatory dependency would not be a good idea. If it is not 
mandatory, then it would mean that psql could be compiled with or without 
lua embedding, thus psql would not be dependable because features may or 
may not be available when writing a "psql script".


For me, client embedded language pg-{lua,pl,tcl,i?py,bf...} (chose your 
favorite:-) projects could make sense, but it does not have to be done 
within the existing psql client, especially with trying to keep upward 
compatibility... If started, such a thing should be a distinct project, 
possibly hosted within postgres source tree if it works well at some 
point.


That might be a way to add a lot of power without having to invent an 
entirely new programming language one bit at a time.


Does programming as such in psql is such a good idea?

ISTM that cpp-like capabilities (include, if, variables, some expressions) 
are somewhat both useful and enough for the limited use cases I have 
encountered. Similar languages are offered in other instances, such as 
readline inputrc or vim vimrc.


If I have something really complicated, then I really want a programming 
language, probably I do not want to learn a new one just for this purpose, 
so I switch to something else that I already know which will do some SQL 
when necessary.


If we want to invent our own expression language, what kind of syntax 
should it use?


After about 35 years of programming, I've convinced myself that mixing 
languages is most often a bad idea (think HTML/CSS/JS/PHP/SQL all in one 
file). Currently psql has SQL, backslash commands, :* client-side 
variables, all with good justifications. That is somewhat 3 languages (or 
2.5 if counting variable substitions for half a language), and I think it 
should not go up if avoidable.


This leads to the opinion that if there is a client side language (or 
client-side expressions as we are considering here), then it should look 
like SQL, hence my constant ranting about the "defined varname" somehow 
perlish thing. Tom helped forge this opinion when argumenting about some 
pgbench changes I submitted, and he is the one suggesting this.



Upon what kind of design principles should it be based?


I submit that client side expressions should be a subset of SQL and 
possible existing or extended variable substitution.


There's likely to be vigorous debate on these topics, and probably also 
complaints that the good designs are too much work and the 
easy-to-implement designs are too limiting. (Regular readers of this 
mailing list will likely be able to guess which side of those debates 
I'll be on, but we need to have them all the same.)


I suggest to reuse pgbench expression engine, developed by Haas Robert:-)

I have submitted a patch to add some functions and boolean support, which 
seems like a definite requirement for "\if". Although pgbench expressions 
are a bit overkill for psql, I think that developing another expression 
engine is a bad idea, just reuse the one.



Regarding the ostensible topic of this thread, one thought I had while
reading through these various responses is that the original need
would be well-served by the (somewhat dubious) syntax that bash uses
for variable substitution.


Obviously, we aren't going to change the interpolate-this-variable 
character from : to $, but bash has ${parameter:-word} to 

Re: [HACKERS] Undefined psql variables

2017-04-12 Thread Pavel Stehule
2017-04-12 17:05 GMT+02:00 Robert Haas :

> On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane  wrote:
> > So my view of this is that "send the expression to the server" ought
> > to be just one option for \if, not the only way to do it.
>
> I heartily agree.  There should be some kind of client-side expression
> language, and one thing it should allow is calling out to the server.
> Then people who only want to call out to the server can do that, but
> people who want to do something else have the option.  Insisting that
> this facility isn't allowed to do anything other than consult the
> server is (1) inconsistent with what we've already got in v10 and (2)
> boxing ourselves into a corner for no very good reason.
>
> Now, the optimal shape for that client-side expression language is not
> very clear to me.  Do we want to invent our own language, or maybe
> consider using something that already exists?  It's been previously
> suggested that we should somehow embed Lua, and this might not be a
> bad place to consider doing something like that.  That might be a way
> to add a lot of power without having to invent an entirely new
> programming language one bit at a time.  If we want to invent our own
> expression language, what kind of syntax should it use?  Upon what
> kind of design principles should it be based?  There's likely to be
> vigorous debate on these topics, and probably also complaints that the
> good designs are too much work and the easy-to-implement designs are
> too limiting.  (Regular readers of this mailing list will likely be
> able to guess which side of those debates I'll be on, but we need to
> have them all the same.)
>
>
Integration Lua engine can help lot of - and it can change the design
significantly. For this purpose it is maybe overkill, but it can be fresh
air in psql customisation and usage.

\setlua varname one line lua expression

or

\lua
  ...
  lua code

  psqlvar.set("", somevalue)

\endlua

I like this idea. We can use Math libraries, random generators, ...

If Lua engine and dependency are too strong cafe - very basic calculator
like
https://www.l2f.inesc-id.pt/~david/w/pt/The_YACC_Parser_Generator/Example:_Calculator_with_Variables
can
be good enough (Don't need a variables there)


> Regarding the ostensible topic of this thread, one thought I had while
> reading through these various responses is that the original need
> would be well-served by the (somewhat dubious) syntax that bash uses
> for variable substitution.  Obviously, we aren't going to change the
> interpolate-this-variable character from : to $, but bash has
> ${parameter:-word} to substitute a default for an unset parameter,
> ${parameter:=word} to substitute a default for an unset parameter and
> also set the parameter to that value, ${parameter:?word} to error out
> with word as the error mesage if parameter is not set, and so forth.
> If we decide to roll our own, we might consider taking inspiration
> from those constructs.
>
>
It is great and it can work

\set varname :{varname?some error message} ..
\set varname :{varname:-0} ..

Good ideas

Regards

Pavel




> I think that one of the general problems of language design is, as
> Larry Wall once said, that a good language should make simple things
> simple and complex things possible.  But simple is not an absolute; it
> depends on context.  The things which a language needs to make simple
> are those things which will be done frequently *in that language*.  So
> for example in this case, out-calls to SQL need to be very easy to
> write.  Maybe the empty-parameter thing needs to be easy; not sure.
> Coming up with a good solution here will involve understanding what
> people typically want to do with a language of this type and then
> making sure that stuff can be done succinctly - and ideally also
> making sure that other stuff is also possible if you're willing to put
> in more legwork.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>


Re: [HACKERS] Undefined psql variables

2017-04-12 Thread Robert Haas
On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane  wrote:
> So my view of this is that "send the expression to the server" ought
> to be just one option for \if, not the only way to do it.

I heartily agree.  There should be some kind of client-side expression
language, and one thing it should allow is calling out to the server.
Then people who only want to call out to the server can do that, but
people who want to do something else have the option.  Insisting that
this facility isn't allowed to do anything other than consult the
server is (1) inconsistent with what we've already got in v10 and (2)
boxing ourselves into a corner for no very good reason.

Now, the optimal shape for that client-side expression language is not
very clear to me.  Do we want to invent our own language, or maybe
consider using something that already exists?  It's been previously
suggested that we should somehow embed Lua, and this might not be a
bad place to consider doing something like that.  That might be a way
to add a lot of power without having to invent an entirely new
programming language one bit at a time.  If we want to invent our own
expression language, what kind of syntax should it use?  Upon what
kind of design principles should it be based?  There's likely to be
vigorous debate on these topics, and probably also complaints that the
good designs are too much work and the easy-to-implement designs are
too limiting.  (Regular readers of this mailing list will likely be
able to guess which side of those debates I'll be on, but we need to
have them all the same.)

Regarding the ostensible topic of this thread, one thought I had while
reading through these various responses is that the original need
would be well-served by the (somewhat dubious) syntax that bash uses
for variable substitution.  Obviously, we aren't going to change the
interpolate-this-variable character from : to $, but bash has
${parameter:-word} to substitute a default for an unset parameter,
${parameter:=word} to substitute a default for an unset parameter and
also set the parameter to that value, ${parameter:?word} to error out
with word as the error mesage if parameter is not set, and so forth.
If we decide to roll our own, we might consider taking inspiration
from those constructs.

I think that one of the general problems of language design is, as
Larry Wall once said, that a good language should make simple things
simple and complex things possible.  But simple is not an absolute; it
depends on context.  The things which a language needs to make simple
are those things which will be done frequently *in that language*.  So
for example in this case, out-calls to SQL need to be very easy to
write.  Maybe the empty-parameter thing needs to be easy; not sure.
Coming up with a good solution here will involve understanding what
people typically want to do with a language of this type and then
making sure that stuff can be done succinctly - and ideally also
making sure that other stuff is also possible if you're willing to put
in more legwork.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-08 Thread Pavel Stehule
2017-04-08 12:25 GMT+02:00 Fabien COELHO :

>
> Hello Pavel,
> n
>
>> you proposal disallow client side expressions.
>>
>
> I do agree that some client side expressions are necessary. I do not want
> to disallow them.
>
> I agree so is not possible to mix server side and client side expressions.
>>
>
> My point is that a minimal of cross-support is possible.
>
> But I am sceptic so benefit of server side expression is higher than a
>> lost of client side expressions.
>>
>
> There is a misunderstanding. I am not against client side expression. I do
> want to allow the same server & client side capabilities suggested by Tom,
> I'm just arguing about the syntax, to avoid a prefix oriented approach.
>
> If we disallow server side expressions, then your examples are only two
>> lines longer, but the implementation can be more simpler.
>>
>> SELECT version FROM  app_version
>> \gset
>> \if :version >= 2.0
>> ...
>>
>> Still I don't think so server side expression in \if is good idea.
>>
>
> Ok, so you do not like server-side expression capabities integrated to
> \if. I understood that you were in favor of Tom's proposal.
>

In this moment I see difficult implementation of \if expressions if we
should to separate server side and client side expressions. The prefix
oriented approach is used well in PLpgSQL

FOR IN SELECT, FOR IN ARRAY, FOR IN 1..x

Tom's design should be more simply for implementation and can be simply
extended. There is clean if expr is client side or server side.



>
> From a semantical point of view they are not necessary because the same
> effect can be obtained through \gset, at the price of an intermediate
> variable. So the server-side thing is just a syntax convenience. I think
> that independently of whether they are added, Tom's point is that it should
> be possible to add those features later on, hence the discussion about a
> design.


We are talking about primitive scripting language - that should be simple
how it is possible. One command more, or performance there are not
important - if the performance will not be terrible. The overhead of
intermediate variable is +/- zero against remote expression. But back to
main issue. We should to find syntax if some variable is defined and can be
used or not.

Maybe the solution should not be directly joined with \if command.

what do you think about special \set command, that can be active only when
variable is not defined.

some like

  \setifempty xxx default

Regards

Pavel


>
>
> --
> Fabien.
>


Re: [HACKERS] Undefined psql variables

2017-04-08 Thread Fabien COELHO


Hello Pavel,
n

you proposal disallow client side expressions.


I do agree that some client side expressions are necessary. I do not want 
to disallow them.


I agree so is not possible to mix server side and client side 
expressions.


My point is that a minimal of cross-support is possible.

But I am sceptic so benefit of server side expression is higher than a 
lost of client side expressions.


There is a misunderstanding. I am not against client side expression. I do 
want to allow the same server & client side capabilities suggested by Tom, 
I'm just arguing about the syntax, to avoid a prefix oriented approach.



If we disallow server side expressions, then your examples are only two
lines longer, but the implementation can be more simpler.

SELECT version FROM  app_version
\gset
\if :version >= 2.0
...

Still I don't think so server side expression in \if is good idea.


Ok, so you do not like server-side expression capabities integrated to 
\if. I understood that you were in favor of Tom's proposal.


From a semantical point of view they are not necessary because the same 
effect can be obtained through \gset, at the price of an intermediate 
variable. So the server-side thing is just a syntax convenience. I think 
that independently of whether they are added, Tom's point is that it 
should be possible to add those features later on, hence the discussion 
about a design.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-07 Thread Pavel Stehule
2017-04-07 21:04 GMT+02:00 Fabien COELHO :

>
> Hello Pavel,
>
> I wish I could have an explanation about why the :?varname (or some other
>>> variant) syntax I suggested has a "namespace" issue.
>>>
>>> The advantage that I see is that although it is obviously ugly, it is
>>> ugly
>>> in the continuity of the various :["'?]varname syntaxes already offered
>>> and
>>> it allows to get rid of "defined varname" which does not look like SQL. A
>>> second advantage is that with the "defined" proposal
>>>
>>
>> I don't think so this argument is valid - \if doesn't look like SQL too.
>>
>
> Sure. I'm talking about the expressions after the "\if" which should be as
> close as SQL, I think. At least that is what Tom required about the
> expression syntax in pgbench, and I tend to agree that psql should avoid to
> mix in another language if possible.
>
>\if defined var1 and defined var2 or defined var3 and sqlrt() >= ..
>>>
>>> Would probably never work work, as it cannot be embedded in another
>>> expression, while it would work with
>>>
>>>\if :?var1 and :?var2 or :?var3 and ...
>>>
>>> I don't see any reason why first should not work and second should to
>> work
>>
>
> Because of the mix of client-side and server-side stuff which needs to be
> interpreted. Let us consider:
>
>   \if EXISTS (SELECT * FROM tbl WHERE id=3) AND defined foo
>
> The "exists" is obviously executed server-side, but "defined foo" needs to
> be interpreted client-side, and it means that some parser client side would
> have been able to catch it in the middle of everything else. This example
> also illustrate my "does not look like SQL" point, as the first part is
> clearly SQL and the part after AND is not.
>
> With the second approach, ... "AND :?foo", the ":?foo" reference would be
> substituted directly by psql lexer and replaced on the fly by the answer,
> resulting in "AND TRUE" or "AND FALSE" depending, then the whole result
> (from EXISTS to TRUE/FALSE) could be interpreted server side to get an
> answer.
>
> Basically, catching :?varname seems easier/safer than catching "defined
> varname". I think that Tom's intent is that the defined expressions could
> not be mixed with SQL server side stuff, but I do not see why not, it is
> easy to imagine some use case where it would make sense.
>
> I have a different opinion - the condition expression should not be SQL
>> necessary. This language is oriented on client side operations. What is
>> benefit from server side expression?
>>
>
> Because I think it is legitimate to be able to write things like:
>
>   \if NOT pg_extension_is_loaded('units')
> \echo 'this application requires the great units extension'
> \q
>   \endif
>
>   \if (SELECT version FROM app_version) >= 2.0
> \echo 'application already installed at 2.0'
> \q
>   \endif
>
>
you proposal disallow client side expressions. I agree so is not possible
to mix server side and client side expressions. But I am sceptic so benefit
of server side expression is higher than a lost of client side expressions.
If we disallow server side expressions, then your examples are only two
lines longer, but the implementation can be more simpler.

SELECT version FROM  app_version
\gset
\if :version >= 2.0
 ...

Still I don't think so server side expression in \if is good idea.

Regards

Pavel




> --
> Fabien.
>


Re: [HACKERS] Undefined psql variables

2017-04-07 Thread Fabien COELHO


Hello Pavel,


I wish I could have an explanation about why the :?varname (or some other
variant) syntax I suggested has a "namespace" issue.

The advantage that I see is that although it is obviously ugly, it is ugly
in the continuity of the various :["'?]varname syntaxes already offered and
it allows to get rid of "defined varname" which does not look like SQL. A
second advantage is that with the "defined" proposal


I don't think so this argument is valid - \if doesn't look like SQL too.


Sure. I'm talking about the expressions after the "\if" which should be 
as close as SQL, I think. At least that is what Tom required about the 
expression syntax in pgbench, and I tend to agree that psql should avoid 
to mix in another language if possible.



   \if defined var1 and defined var2 or defined var3 and sqlrt() >= ..

Would probably never work work, as it cannot be embedded in another
expression, while it would work with

   \if :?var1 and :?var2 or :?var3 and ...


I don't see any reason why first should not work and second should to work


Because of the mix of client-side and server-side stuff which needs to be 
interpreted. Let us consider:


  \if EXISTS (SELECT * FROM tbl WHERE id=3) AND defined foo

The "exists" is obviously executed server-side, but "defined foo" needs to 
be interpreted client-side, and it means that some parser client side 
would have been able to catch it in the middle of everything else. This 
example also illustrate my "does not look like SQL" point, as the first 
part is clearly SQL and the part after AND is not.


With the second approach, ... "AND :?foo", the ":?foo" reference would be 
substituted directly by psql lexer and replaced on the fly by the answer, 
resulting in "AND TRUE" or "AND FALSE" depending, then the whole result 
(from EXISTS to TRUE/FALSE) could be interpreted server side to get an 
answer.


Basically, catching :?varname seems easier/safer than catching "defined 
varname". I think that Tom's intent is that the defined expressions could 
not be mixed with SQL server side stuff, but I do not see why not, it is 
easy to imagine some use case where it would make sense.



I have a different opinion - the condition expression should not be SQL
necessary. This language is oriented on client side operations. What is
benefit from server side expression?


Because I think it is legitimate to be able to write things like:

  \if NOT pg_extension_is_loaded('units')
\echo 'this application requires the great units extension'
\q
  \endif

  \if (SELECT version FROM app_version) >= 2.0
\echo 'application already installed at 2.0'
\q
  \endif

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-07 Thread Pavel Stehule
2017-04-07 9:52 GMT+02:00 Fabien COELHO :

>
> Hello Corey,
>
> \if defined varname
 \if sql boolean expression to send to server
 \if compare value operator value

>>>
>>> I'm still thinking:-)
>>>
>>> Independently of the my aethetical complaint against having a pretty
>>> unusual keyword prefix syntax, how would you envision a \set assignment
>>> variant? Would \if have a different expression syntax somehow?
>>>
>>
>> Any further thoughts?
>>
>
> My current opinion:
>
>  - I'm fine if \set stays as it is, i.e. no expression.
>
>  - I agree that some client-side expressions are needed, along the
>semantics suggested by Tom, i.e. definition and comparisons.
>
>  - I'm really against the prefix syntax suggested by Tom
>
>
> I wish I could have an explanation about why the :?varname (or some other
> variant) syntax I suggested has a "namespace" issue.
>
> The advantage that I see is that although it is obviously ugly, it is ugly
> in the continuity of the various :["'?]varname syntaxes already offered and
> it allows to get rid of "defined varname" which does not look like SQL. A
> second advantage is that with the "defined" proposal
>

I don't think so this argument is valid - \if doesn't look like SQL too.


>
>\if defined var1 and defined var2 or defined var3 and sqlrt() >= ..
>
> Would probably never work work, as it cannot be embedded in another
> expression, while it would work with
>
>\if :?var1 and :?var2 or :?var3 and ...
>
>
I don't see any reason why first should not work and second should to work


>
> Moreover, I would like the condition syntax to be basically SQL & psql
> variables, without explicit prefixes, with a transparent decision whether
> it is evaluated client side or server side.
>
> As client-side expressions are pretty simple, ISTM that some regex could
> be used for this purpose, eg for integer and boolean comparisons:
>
>  ^\s*\d+\s*(=|<>|!=|<|<=|>|>=)\s*\d+\s*$
>  ^\s*(bool...)\s*(=|<>|!=)\s*(bool...)\s*$
>  ^\s*(NOT\s*)?(bool...)\s*$
>
> So that one could just write the expressions without having to tell where
> it is executed, eg
>
>  \if :VERSION_NUM < 11
>
> Would lead to
>
>  \if 10 < 11
>
> Caught by the first regex, and evaluated with a few lines of code.


I have a different opinion - the condition expression should not be SQL
necessary. This language is oriented on client side operations. What is
benefit from server side expression?

Regards

Pavel


>
>
> --
> Fabien.
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] Undefined psql variables

2017-04-07 Thread Fabien COELHO


Hello Corey,


\if defined varname
\if sql boolean expression to send to server
\if compare value operator value


I'm still thinking:-)

Independently of the my aethetical complaint against having a pretty
unusual keyword prefix syntax, how would you envision a \set assignment
variant? Would \if have a different expression syntax somehow?


Any further thoughts?


My current opinion:

 - I'm fine if \set stays as it is, i.e. no expression.

 - I agree that some client-side expressions are needed, along the
   semantics suggested by Tom, i.e. definition and comparisons.

 - I'm really against the prefix syntax suggested by Tom


I wish I could have an explanation about why the :?varname (or some other 
variant) syntax I suggested has a "namespace" issue.


The advantage that I see is that although it is obviously ugly, it is ugly 
in the continuity of the various :["'?]varname syntaxes already offered 
and it allows to get rid of "defined varname" which does not look like 
SQL. A second advantage is that with the "defined" proposal


   \if defined var1 and defined var2 or defined var3 and sqlrt() >= ..

Would probably never work work, as it cannot be embedded in another 
expression, while it would work with


   \if :?var1 and :?var2 or :?var3 and ...


Moreover, I would like the condition syntax to be basically SQL & psql 
variables, without explicit prefixes, with a transparent decision whether 
it is evaluated client side or server side.


As client-side expressions are pretty simple, ISTM that some regex could 
be used for this purpose, eg for integer and boolean comparisons:


 ^\s*\d+\s*(=|<>|!=|<|<=|>|>=)\s*\d+\s*$
 ^\s*(bool...)\s*(=|<>|!=)\s*(bool...)\s*$
 ^\s*(NOT\s*)?(bool...)\s*$

So that one could just write the expressions without having to tell where 
it is executed, eg


 \if :VERSION_NUM < 11

Would lead to

 \if 10 < 11

Caught by the first regex, and evaluated with a few lines of code.

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-06 Thread Kyotaro HORIGUCHI
At Thu, 6 Apr 2017 19:21:21 -0400, Corey Huinker  
wrote in 

Re: [HACKERS] Undefined psql variables

2017-04-06 Thread Corey Huinker
On Sun, Apr 2, 2017 at 4:57 PM, Fabien COELHO  wrote:

>
> I'm inclined to suggest that we should require all extensions beyond the
>> boolean-literal case to be set up as a keyword followed by appropriate
>> argument(s); that seems like it's enough to prevent syntax conflicts from
>> future additions.  So you could imagine
>>
>> \if defined varname
>> \if sql boolean expression to send to server
>> \if compare value operator value
>>
>
> I'm still thinking:-)
>
> Independently of the my aethetical complaint against having a pretty
> unusual keyword prefix syntax, how would you envision a \set assignment
> variant? Would \if have a different expression syntax somehow?


Any further thoughts?


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Fabien COELHO



I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions.  So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value


I'm still thinking:-)

Independently of the my aethetical complaint against having a pretty 
unusual keyword prefix syntax, how would you envision a \set assignment 
variant? Would \if have a different expression syntax somehow?


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Fabien COELHO


Hello,


For checking variable definition, I would suggest to extend the variable
access syntax so that there is no exception to the one thing rule between
client side and server side evaluation:



   \if :?variable


Don't like that one bit;


Possibly:-)

This is kind of a shell-like hack ${VAR:?error-message-if-not-defined},
or ${#VAR} to get a length.

They are not likable but they do the job.


you're going to run out of namespace there in no time.


I do not undestand where there would be a namespace issue. Is that under 
the assumption that ":?xxx" is frequently used in SQL?



And you don't have a very good way to say "if not defined", either.


Indeed. I'm afraid that handling "NOT" client-side would be necessary with 
this approach, so the decision would be 1 thing or 2 things where the 
first one is "NOT" would be handled client-side.


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Fabien COELHO


Hello Tom,


I'm just arguing that for pgbench the evaluator needs to be on the client
side, which implies a lexer, parser and executor. For psql, it does not
really matter where the evaluator is, thus relying on the server should be
fine and simpler and also powerful, provided the necessary information can
be transfered from the client, eg through variable expansion, and maybe
back in the form of special variables to test for errors for instance.


I don't really buy this.  Certainly it'd be fine for many use-cases, but 
there will be cases where what you're trying to script around is 
server-side errors. An expression evaluation facility that goes belly-up 
as soon as the server is in an aborted transaction is not going to be 
very useful in that scenario.


"Going belly-up" suggests testing/checking for errors, which could be 
eased through special variables à la errno and more than simplistic 
client-side expression evaluation.



I think that we need just a relatively primitive facility in order
to meet that sort of need, but we do need something.


Hmmm. Yes, I was thinking of that kind of thing. The question is how large 
the necessary "something". I'm arguying for the smallest possible 
solution. Maybe handling direct booleans (as already implemented) and the 
NOT operator could be enough (clear enough to understand for the user, 
would cover needed cases, and would be easy to implement)? i.e.


  \if NOT :IS_CONNECTED
...

  SELECT ... \gset
  \if :SQL_ERROR_OCCURED
...

  \if :CURRENT_TRANSACTION_ABORTED
...


So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it.  Hence my
suggestion of "\if sql ...text to send to server...".  Probably someone
can think of a better keyword than "sql" for that.


That is the kind of (ugly) thing I would really like to avoid, if 
possible. As pavel argued, it should be "intuitive", and having a explicit 
syntactic marker and/or possibly two distinct syntaxes does not strike me 
as a desirable user-experience.


--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Tom Lane
Fabien COELHO  writes:
> For checking variable definition, I would suggest to extend the variable 
> access syntax so that there is no exception to the one thing rule between 
> client side and server side evaluation:

>\if :?variable

Don't like that one bit; you're going to run out of namespace there
in no time.  And you don't have a very good way to say "if not defined",
either.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Tom Lane
Fabien COELHO  writes:
> I'm just arguing that for pgbench the evaluator needs to be on the client 
> side, which implies a lexer, parser and executor. For psql, it does not 
> really matter where the evaluator is, thus relying on the server should be 
> fine and simpler and also powerful, provided the necessary information can 
> be transfered from the client, eg through variable expansion, and maybe 
> back in the form of special variables to test for errors for instance.

I don't really buy this.  Certainly it'd be fine for many use-cases,
but there will be cases where what you're trying to script around
is server-side errors.  An expression evaluation facility that goes
belly-up as soon as the server is in an aborted transaction is not
going to be very useful in that scenario.

I think that we need just a relatively primitive facility in order
to meet that sort of need, but we do need something.

So my view of this is that "send the expression to the server" ought
to be just one option for \if, not the only way to do it.  Hence my
suggestion of "\if sql ...text to send to server...".  Probably someone
can think of a better keyword than "sql" for that.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Fabien COELHO


Hello Pavel,


I have convinced myself that, unlike pgbench, psql does not really need an
advanced client-side-implemented language, so the smaller the better. What
I mean by this is that from psql point of view it is ok that the actual
expression evaluation is performed server-side. From a user experience
point of view it would look similar to pgbench, just the evaluator does not
need to be client-side.


I am sorry - I disagree - I don't expect hard scripting in psql too. But
psql is much more widely used than pgbench - and scripting should be
intuitive.


I am ok with that objective.

I'm just arguing that for pgbench the evaluator needs to be on the client 
side, which implies a lexer, parser and executor. For psql, it does not 
really matter where the evaluator is, thus relying on the server should be 
fine and simpler and also powerful, provided the necessary information can 
be transfered from the client, eg through variable expansion, and maybe 
back in the form of special variables to test for errors for instance.


It would not change anything from a syntactic point of view, i.e. it 
should indeed be intuitive as you put it, i.e. SQL-like, for instance:


  \if current_setting('something') = 'whatever' AND :VERSION_NUM >= 10
...

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Pavel Stehule
2017-04-02 18:56 GMT+02:00 Fabien COELHO :

>
> Hello Tom,
>
> I'm inclined to suggest that we should require all extensions beyond the
>> boolean-literal case to be set up as a keyword followed by appropriate
>> argument(s); that seems like it's enough to prevent syntax conflicts from
>> future additions.  So you could imagine
>>
>> \if defined varname
>> \if sql boolean expression to send to server
>> \if compare value operator value
>>
>> It would be easy to allow "not" in front of any one of these, but
>> it's less clear how to do AND or OR combinations.  You can always
>> fake AND with nested \if's, but OR is a bit more of a problem.
>> Maybe we don't need it.
>>
>> Other ideas about how to design this?
>>
>
> My 0.02 €:
>
> I have convinced myself that, unlike pgbench, psql does not really need an
> advanced client-side-implemented language, so the smaller the better. What
> I mean by this is that from psql point of view it is ok that the actual
> expression evaluation is performed server-side. From a user experience
> point of view it would look similar to pgbench, just the evaluator does not
> need to be client-side.
>

I am sorry - I disagree - I don't expect hard scripting in psql too. But
psql is much more widely used than pgbench - and scripting should be
intuitive.

Regards

Pavel


>
> So I would suggest something close but maybe simpler than what you suggest
> above. If there is just one thing, it is true or false, checked client
> side, well, this is already implemented:-).
>
>   \if something
>
> If there are more than one argument, or maybe if previous true/false
> evaluation failed, then:
>
>   \if sql expression to be evaluated server side
>
> Then the result is checked for true or false client-side. It would be
> equivalent to:
>
>   SELECT sql expression to be evaluted server side AS is_ok \gset
>   \if :is_ok
>
> Finally I would suggest that client to server would only communicate by
> variable substitution, as the backtick patch with external processes.
>
> For checking variable definition, I would suggest to extend the variable
> access syntax so that there is no exception to the one thing rule between
> client side and server side evaluation:
>
>   \if :?variable
>
> the :?... is subsituted by true or false depending on whether the variable
> exists.
>
>   \if NOT :?variable
>
> would work by executing "NOT ..." on the server. No need for "defined"
> which would not look like SQL function calls anyway, no need for any
> operator client side or clumsy rules.
>
> --
> Fabien.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Pavel Stehule
2017-04-02 18:29 GMT+02:00 Tom Lane :

> Corey Huinker  writes:
> > On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane  wrote:
> >> This seems pretty bizarre.  What's the use case?  Why would it not
> >> be better to build the behavior out of other spare parts, along the
> >> lines of COALESCE or perhaps
> >> \if not defined(x)
>
> > In light of the backticks variable expansion thread, I'm reviving this
> > thread in the hopes that a defined()-ish psql function can make it into
> v10.
> > It's something that cannot be solved with a query and \gset, so adding it
> > to psql boolean expressions is the only option I can see.
>
> I'm fairly hesitant to add stuff in advance of having a fairly clear
> sketch of the boolean expression language we want.  I don't mind
> implementing such a language piece-by-piece, but if we just throw in
> one or two features that seem like good ideas, I'm afraid we'll be
> painting ourselves into a corner.
>
> The only thing that seems locked down so far is that "a single argument
> is a simple boolean value".  If we were hot to support expr-style
> comparison behavior, we could define cases with exactly three arguments
> as being "\if value operator value".  But I'm afraid that that would
> cause problems because there would be other desirable behaviors (like
> "\if not defined varname") that would also involve three arguments,
> creating ambiguity.
>
> I'm inclined to suggest that we should require all extensions beyond the
> boolean-literal case to be set up as a keyword followed by appropriate
> argument(s); that seems like it's enough to prevent syntax conflicts from
> future additions.  So you could imagine
>
> \if defined varname
> \if sql boolean expression to send to server
> \if compare value operator value
>

These possibilities looks well.

if defined varname is perfectly intuitive

Maybe it can be shorter - def, undef

\if def var, \if undef var

Regards

Pavel


> It would be easy to allow "not" in front of any one of these, but
> it's less clear how to do AND or OR combinations.  You can always
> fake AND with nested \if's, but OR is a bit more of a problem.
> Maybe we don't need it.
>
> Other ideas about how to design this?
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Fabien COELHO


Hello Tom,


I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions.  So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations.  You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.

Other ideas about how to design this?


My 0.02 €:

I have convinced myself that, unlike pgbench, psql does not really need an 
advanced client-side-implemented language, so the smaller the better. What 
I mean by this is that from psql point of view it is ok that the actual 
expression evaluation is performed server-side. From a user experience 
point of view it would look similar to pgbench, just the evaluator does 
not need to be client-side.


So I would suggest something close but maybe simpler than what you suggest 
above. If there is just one thing, it is true or false, checked client 
side, well, this is already implemented:-).


  \if something

If there are more than one argument, or maybe if previous true/false 
evaluation failed, then:


  \if sql expression to be evaluated server side

Then the result is checked for true or false client-side. It would be 
equivalent to:


  SELECT sql expression to be evaluted server side AS is_ok \gset
  \if :is_ok

Finally I would suggest that client to server would only communicate by 
variable substitution, as the backtick patch with external processes.


For checking variable definition, I would suggest to extend the variable 
access syntax so that there is no exception to the one thing rule between 
client side and server side evaluation:


  \if :?variable

the :?... is subsituted by true or false depending on whether the variable 
exists.


  \if NOT :?variable

would work by executing "NOT ..." on the server. No need for "defined" 
which would not look like SQL function calls anyway, no need for any

operator client side or clumsy rules.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Tom Lane
Corey Huinker  writes:
> On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane  wrote:
>> This seems pretty bizarre.  What's the use case?  Why would it not
>> be better to build the behavior out of other spare parts, along the
>> lines of COALESCE or perhaps
>> \if not defined(x)

> In light of the backticks variable expansion thread, I'm reviving this
> thread in the hopes that a defined()-ish psql function can make it into v10.
> It's something that cannot be solved with a query and \gset, so adding it
> to psql boolean expressions is the only option I can see.

I'm fairly hesitant to add stuff in advance of having a fairly clear
sketch of the boolean expression language we want.  I don't mind
implementing such a language piece-by-piece, but if we just throw in
one or two features that seem like good ideas, I'm afraid we'll be
painting ourselves into a corner.

The only thing that seems locked down so far is that "a single argument
is a simple boolean value".  If we were hot to support expr-style
comparison behavior, we could define cases with exactly three arguments
as being "\if value operator value".  But I'm afraid that that would
cause problems because there would be other desirable behaviors (like
"\if not defined varname") that would also involve three arguments,
creating ambiguity.

I'm inclined to suggest that we should require all extensions beyond the
boolean-literal case to be set up as a keyword followed by appropriate
argument(s); that seems like it's enough to prevent syntax conflicts from
future additions.  So you could imagine

\if defined varname
\if sql boolean expression to send to server
\if compare value operator value

It would be easy to allow "not" in front of any one of these, but
it's less clear how to do AND or OR combinations.  You can always
fake AND with nested \if's, but OR is a bit more of a problem.
Maybe we don't need it.

Other ideas about how to design this?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-04-02 Thread Corey Huinker
On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane  wrote:

> Corey Huinker  writes:
> > I was giving some thought to how psql handles undefined variables.
> > I would like an option where either psql can provide an alternate value
> > when an undefined variable is referenced, or a way to detect that a
> > specific variable is undefined and replace it with a defined variable.
>
> This seems pretty bizarre.  What's the use case?  Why would it not
> be better to build the behavior out of other spare parts, along the
> lines of COALESCE or perhaps
>
>   \if not defined(x)
>   \set x y
>   \fi
>
> Obviously the \if stuff is things we don't have yet either, but
> it seems less likely to have surprising side-effects.
>
> regards, tom lane
>

In light of the backticks variable expansion thread, I'm reviving this
thread in the hopes that a defined()-ish psql function can make it into v10.
It's something that cannot be solved with a query and \gset, so adding it
to psql boolean expressions is the only option I can see.


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Fabien COELHO



Back in the day, PG allowed ":" as a generic operator name, making
this even worse; but I think the only remaining SQL syntax that could
include a colon is array slicing.


Ok, so the behavior of replacing ":unknown" by same cannot be changed.

Some fun:

  \set 1 1
  SELECT ('{1,2,3,4,5,6,7,8,9,10,11,12}'::INT[])[1:1];
  -- yields 11

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Tom Lane
"David G. Johnston"  writes:
> On Mon, Jan 23, 2017 at 11:16 AM, Fabien COELHO  wrote:
>> Currently the value of a non existing psql-variable is... its own
>> reference:-(
>> 
>> psql> \echo :x
>> :x
>> 
>> I'm not sure of the rational, apart from the probable lexer implementation
>> point of view. Maybe an empty string or 0 or some configurable value would
>> provide better alternative.

> The fundamental problem is that:
> SELECT 'testing' AS ":tablename"
> is perfectly valid SQL code.

Yeah, but psql does know not to try to resolve :something inside a quoted
literal or identifier.  The actual problem is with constructs like

SELECT somearray[lower:upper] FROM ...

If the user is thinking that's an array subscript not a variable
reference, we don't want to break their query when we don't even have
a useful thing to contribute.

Back in the day, PG allowed ":" as a generic operator name, making
this even worse; but I think the only remaining SQL syntax that could
include a colon is array slicing.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread David G. Johnston
On Mon, Jan 23, 2017 at 11:16 AM, Fabien COELHO  wrote:

>
> [...] Obviously the \if stuff is things we don't have yet either, but it
>>> seems less likely to have surprising side-effects.
>>>
>>
> I agree, a more generic solution seems better than an ad-hoc one.
>
> Currently the value of a non existing psql-variable is... its own
> reference:-(
>
>   psql> \echo :x
>   > :x
>
> I'm not sure of the rational, apart from the probable lexer implementation
> point of view. Maybe an empty string or 0 or some configurable value would
> provide better alternative.


​The fundamental problem is that:

SELECT 'testing' AS ":tablename"

is perfectly valid SQL code.  psql, by design, attempts to resolve all
strings of the form <:['"]?\w> in a purely textual manner.

David J.


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Fabien COELHO


[...] Obviously the \if stuff is things we don't have yet either, but 
it seems less likely to have surprising side-effects.


I agree, a more generic solution seems better than an ad-hoc one.

Currently the value of a non existing psql-variable is... its own 
reference:-(


  psql> \echo :x
  > :x

I'm not sure of the rational, apart from the probable lexer implementation 
point of view. Maybe an empty string or 0 or some configurable value would 
provide better alternative.



PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ...
--set varN=n


How are the variable typically used in the script?

--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Pavel Stehule
2017-01-23 18:53 GMT+01:00 Tom Lane :

> Corey Huinker  writes:
> > I was giving some thought to how psql handles undefined variables.
> > I would like an option where either psql can provide an alternate value
> > when an undefined variable is referenced, or a way to detect that a
> > specific variable is undefined and replace it with a defined variable.
>
> This seems pretty bizarre.  What's the use case?  Why would it not
> be better to build the behavior out of other spare parts, along the
> lines of COALESCE or perhaps
>
>   \if not defined(x)
>   \set x y
>   \fi
>
> Obviously the \if stuff is things we don't have yet either, but
> it seems less likely to have surprising side-effects.
>

Some form of DEFINED has more sense than COALESCE in this case. We can
introduce some predefined client side functions available only in \if
\elsif statements.

Regards

Pavel

>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Corey Huinker
On Mon, Jan 23, 2017 at 12:53 PM, Tom Lane  wrote:

> Corey Huinker  writes:
> > I was giving some thought to how psql handles undefined variables.
> > I would like an option where either psql can provide an alternate value
> > when an undefined variable is referenced, or a way to detect that a
> > specific variable is undefined and replace it with a defined variable.
>
> This seems pretty bizarre.  What's the use case?  Why would it not
> be better to build the behavior out of other spare parts, along the
> lines of COALESCE or perhaps
>
>   \if not defined(x)
>   \set x y
>   \fi
>
> Obviously the \if stuff is things we don't have yet either, but
> it seems less likely to have surprising side-effects.
>
> regards, tom lane
>

That'd work too, if \if and defined(psql_var_name) come to fruition.

A use case we have now is where a script has several values that are almost
always a default value.

Instead of

PGSERVICE=foo psql -f script.sql --set var1=a --set var2=b --set var3=d ...
--set varN=n

I can just specify the non-default values:


PGSERVCE=foo psql -f script.sql --set var3=unusual_value


and the code then shows the unusual needle in the default haystack.


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Tom Lane
Corey Huinker  writes:
> I was giving some thought to how psql handles undefined variables.
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.

This seems pretty bizarre.  What's the use case?  Why would it not
be better to build the behavior out of other spare parts, along the
lines of COALESCE or perhaps

  \if not defined(x)
  \set x y
  \fi

Obviously the \if stuff is things we don't have yet either, but
it seems less likely to have surprising side-effects.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread Corey Huinker
On Mon, Jan 23, 2017 at 12:49 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> I have followed the "\if" thread that closely but IIRC that was part of
> the discussion there.
>
>

Yes, I'm trying to split some of those side-thoughts into their own threads.


Re: [HACKERS] Undefined psql variables

2017-01-23 Thread David G. Johnston
On Mon, Jan 23, 2017 at 10:34 AM, Corey Huinker 
wrote:

> I was giving some thought to how psql handles undefined variables.
>
> I would like an option where either psql can provide an alternate value
> when an undefined variable is referenced, or a way to detect that a
> specific variable is undefined and replace it with a defined variable.
>
> My first thought thought was to have a
>
>
> \set_if_undefined var_name 'default_value'
>
>
​\coalesce var_name [maybe other var names?] 'default_value' --sets the
value of var_name to itself or the first defined/non-null result of the
subsequent items​.  Probably only useful if you allow expressions.  I have
followed the "\if" thread that closely but IIRC that was part of the
discussion there.


>
> Another idea adding a \pset parameter that would return a specific value
> when an undefined psql variable is referenced instead of raising an error.
> Like this:
>
> # select :'x' as value_of_x;
> ERROR:  syntax error at or near ":"
> LINE 1: select :'x' as value_of_x;
>^
>
> # \pset variable_default ''
> analytics=# select :'x' as value_of_x;
>  value_of_x
> 
>
> (1 row)
>
>
​-1​

I unconvinced that setting this in the global environment is a good thing.

David J.