Thread: AW: [HACKERS] Getting OID in psql of recent insert
> 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
[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, Pennsylvania19026
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
> > > Good point, but (AFAIK) you could only use it for tables that you were > > > sure no other client was updating in parallel. Otherwise you might be > > > updating a just-obsoleted tuple. Or is there a solution for that? > > > > > > > Is someone still working on the xid access ? > > > > > > I think we have the ability to refer to CTID in WHERE now, but not yet an > > > access method that actually makes it fast... > > > > Hiroshi supplied a patch to allow it in the executor, and I applied it. > > > > Bruce,could you apply my attached patch ? > I have to add 3 new files but couldn't do 'cvs add' > the files on my machine. > Am I mistaken ? > I couldn't understand the reason now. Applied. No idea why the add didn't work there. It worked here. -- 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, Pennsylvania19026
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
> 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, Pennsylvania19026
> > > > > Good point, but (AFAIK) you could only use it for tables > that you were > > > > sure no other client was updating in parallel. Otherwise > you might be > > > > updating a just-obsoleted tuple. Or is there a solution for that? > > > > > > > > > Is someone still working on the xid access ? > > > > > > > > I think we have the ability to refer to CTID in WHERE now, > but not yet an > > > > access method that actually makes it fast... > > > > > > Hiroshi supplied a patch to allow it in the executor, and I > applied it. > > > > > > > Bruce,could you apply my attached patch ? > > I have to add 3 new files but couldn't do 'cvs add' > > the files on my machine. > > Am I mistaken ? > > I couldn't understand the reason now. > > Applied. No idea why the add didn't work there. It worked here. > Thanks a lot. I don't know CVS well. Could someone teach me ? It seems that 'cvs add' on my current machine connects to postgresql.org. Is it right ? Isn't 'cvs add' local ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
> > > the files on my machine. > > > Am I mistaken ? > > > I couldn't understand the reason now. > > > > Applied. No idea why the add didn't work there. It worked here. > > > > Thanks a lot. > > I don't know CVS well. > Could someone teach me ? > > It seems that 'cvs add' on my current machine connects to > postgresql.org. > Is it right ? > Isn't 'cvs add' local ? > It is local, but has to register it with the server. -- 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, Pennsylvania19026
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
> 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
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