
> testdb=> \set singlestep on
> testdb=> \set sql_interpol '#'
> testdb=> \set foo 'pg_class'
> testdb=> select * from #foo#;

This is great, but may I object to the syntax ?
The standard sql way to use host variables seems to be:
select * from :foo where id = :id

There will always be the problem with conflicting operators,
and this one syntax, already needed by ecpg, it is hard enough.

Andreas

Mon, 22 Nov 1999 18:06:46 +0100
Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at>
AW: [HACKERS] Getting OID in psql of recent insert 
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> > testdb=> \set singlestep on
> > testdb=> \set sql_interpol '#'
> > testdb=> \set foo 'pg_class'
> > testdb=> select * from #foo#;
> 
> This is great, but may I object to the syntax ?
> The standard sql way to use host variables seems to be:
> select * from :foo where id = :id
> 
> There will always be the problem with conflicting operators,
> and this one syntax, already needed by ecpg, it is hard enough.

Agreed.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 22 Nov 1999 12:47:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: AW: [HACKERS] Getting OID in psql of recent insert
Hi, 

> > testdb=> \set singlestep on
> > testdb=> \set sql_interpol '#'
> > testdb=> \set foo 'pg_class'
> > testdb=> select * from #foo#;

  In order to solve these problems,  I have adopted a approach 
which is different from psql.  It is 'pgbash'. The pgbash is 
the system which offers the direct SQL/embedded SQL interface 
for PostgreSQL by being included in the BASH(Version 2) shell. 
Please refer to the next URL.
  http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

 ex.)
pgbash> exec_sql "insert into test values(111,'aaa','bbb')"
pgabsh> rOID=$SQLOID     <---- OID of recent insert
pgbash> exec_sql "begin"
pgbash> exec_sql "declare cur cursor fr select * from test 
>  where oid >= $rOID2 and oid <= $rOID"
pgbash> exec_sql "fetch in cur into :NUM1, :NAME1"
pgbash> exec_sql "fetch in cur into :NUM2, :NAME2" 
pgbash> NUM=$(( $NUM1+$NUM2 ))
pgbash> echo $NUM, $NAME1, $NAME2
pgbash> exec_sql "end"

  Now, pgbash version is 1.2.3 and this version needs 'exec_sql'
to execute SQL.  However, I have changed a parser of BASH-2.03, 
and pgbash becomes BASH itself in the next version. It does not 
need to describe 'exec_sql' in order to execute SQL.

ex.)
pgbash> insert into test values(111,'aaa','bbb'); 
pgbash> rOID = $SQLOID
pgbash> select * from test where oid=$rOID; &> /tmp/work.dat

  'SQL;' becomes one command of BASH shell. Therefore, it is 
possible to use ridirection/pipe with SQL. By this, pgbash has 
the operability equal to psql and it will also have many functions 
which are higher than psql.  

  I think this approach useful. Comments?

--
Regards,
SAKAIDA Masaaki <sakaida@psn.co.jp>
Personal Software, Inc. Osaka, Japan

Tue, 23 Nov 1999 18:24:52 +0900
SAKAIDA Masaaki <sakaida@psn.co.jp>
Re: [HACKERS] Getting OID in psql of recent insert 
On 1999-11-22, Zeugswetter Andreas SEV mentioned:

> > testdb=> \set singlestep on
> > testdb=> \set sql_interpol '#'
> > testdb=> \set foo 'pg_class'
> > testdb=> select * from #foo#;
> 
> This is great, but may I object to the syntax ?
> The standard sql way to use host variables seems to be:
> select * from :foo where id = :id
> 
> There will always be the problem with conflicting operators,
> and this one syntax, already needed by ecpg, it is hard enough.

I just pulled that syntax out of my hat, since it was the most
non-interfering way to go for now, but thanks for the tip, I'll be on the
task in a second. Of course, since the SQL standard is such a widely
available document, I should have found that myself ;)

Is there also a rule on what those variables can contain? I mean currently
they act like C macros, they can contain unbalances quotes, incomplete
backslash commands, everything. Or should they be restricted to SQL?

Also, looking for possible conflicts here, it seems that there is an
operator ':' for exponentiation, which is of course extremely mnemonic.
This reminds me of the ';' operator for logarithms, which I also use all
the time in mathematical writing. Are those operators actually standard or
just somebody's personal idea?

For example, what does this mean:
play=> select value, :value from test;
 value |       ?column?
-------+----------------------
     5 |     148.413159102577
     6 |     403.428793492735
    99 | 9.88903031934695e+42
(3 rows)

Similarly for the ';' operator, where there are obvious problems.
Rationale anybody? Are they from PostQUEL times?

Um, okay, this goes even further. There seem to be functions called log
and dlog1 as well as exp and dexp with essentially the same functionality;
the one with the 'd' goes with float8 arguments, the other one with
numeric. Is this making sense to anybody?

	-Peter

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Wed, 24 Nov 1999 01:56:43 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: AW: [HACKERS] Getting OID in psql of recent insert 
> Is there also a rule on what those variables can contain? I mean currently
> they act like C macros, they can contain unbalances quotes, incomplete
> backslash commands, everything. Or should they be restricted to SQL?
> 
> Also, looking for possible conflicts here, it seems that there is an
> operator ':' for exponentiation, which is of course extremely mnemonic.
> This reminds me of the ';' operator for logarithms, which I also use all
> the time in mathematical writing. Are those operators actually standard or
> just somebody's personal idea?

I recommend we rename : and ; to something else.  : should be used for
variables, and ; is too much like the trailing semicolon.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 23 Nov 1999 20:31:45 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: AW: [HACKERS] Getting OID in psql of recent insert
Peter Eisentraut <peter_e@gmx.net> writes:
> Also, looking for possible conflicts here, it seems that there is an
> operator ':' for exponentiation, which is of course extremely mnemonic.
> This reminds me of the ';' operator for logarithms, which I also use all
> the time in mathematical writing. Are those operators actually standard or
> just somebody's personal idea?

My guess is that someone back in the Berkeley days put them in as a tour
de force in parser-writing.  An impressive show of skill it is, too;
I'm continually astonished that we are not having severe grammar ambiguity
problems from the fact that ';' can be an operator as well as a
statement terminator.  ':' is only marginally safer.  Yet, by golly,
they parse, and have continued to parse despite extensive later changes
to the pgsql grammar.  Whoever that was knew his stuff.

Sooner or later, however, they'll probably break.

I'd not object if we removed these operators and instead provided
functions with the standard names log() and exp() for all the
non-integral numeric types.  Comments?

			regards, tom lane

Wed, 24 Nov 1999 01:29:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: [HACKERS] Getting OID in psql of recent insert 
> I'd not object if we removed these operators and instead provided
> functions with the standard names log() and exp() for all the
> non-integral numeric types.  Comments?

I have no great fondness for ";" and ":" as operators, but would like
to see some operators assigned to these functions. Certainly the carat
"^" could work for exp() (or maybe "**" to make those old Fortran
programmers feel better about themselves ;), and perhaps "!^" for
log()? Any other ideas for appropriate symbols for these operators??

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Mon, 29 Nov 1999 08:21:09 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: AW: [HACKERS] Getting OID in psql of recent insert
On Mon, 29 Nov 1999, Thomas Lockhart wrote:

> > I'd not object if we removed these operators and instead provided
> > functions with the standard names log() and exp() for all the
> > non-integral numeric types.  Comments?
> 
> I have no great fondness for ";" and ":" as operators, but would like
> to see some operators assigned to these functions. Certainly the carat
> "^" could work for exp() (or maybe "**" to make those old Fortran
> programmers feel better about themselves ;), and perhaps "!^" for
> log()? Any other ideas for appropriate symbols for these operators??

I wasn't aware of any Obfuscated SQL Contest ...

I personally think that the greatest possible benefit can only be derived
if all of this looks as much as possible like actual mathematical writing.
Thus I could agree with a power operator '^' and perhaps even a unary '^'
exponential operator, although that's already questionable. But by
inventing non-standard operators for every function under the sun, just to
have one, you're not doing anyone (including yourself) a favour.

That's just me though. As you said yourself, good ideas will stand the
test of an extended discussion ;)

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Mon, 29 Nov 1999 12:58:22 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: AW: [HACKERS] Getting OID in psql of recent insert