Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane [EMAIL PROTECTED] writes: UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; ... Of course this syntax isn't standard either ... but we already have it. Did this patch ever make it in? It's not documented in the 8.0 documentation for UPDATE at: http://www.postgresql.org/docs/8.0/interactive/dml-update.html -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] request for sql3 compliance for the update command
Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; ... Of course this syntax isn't standard either ... but we already have it. Did this patch ever make it in? It's not documented in the 8.0 documentation for UPDATE at: http://www.postgresql.org/docs/8.0/interactive/dml-update.html It is documented only in the UPDATE manual page because it is fairly exotic: http://www.postgresql.org/docs/8.0/interactive/sql-update.html -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas E, 17.03.2003 kell 20:49: With no one replying on how to do correlated subqueries in FROM for UPDATE, Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug, so the way to do correlated subqueries in FROM for UPDATE would be to fix this bug ;) All common sense tells me that if I can update set col1=col2 and *not* get the value from the first col2 to all col1's then the same should be true for this hannu=# creatre table updtarget( hannu(# id int, val text); ERROR: parser: parse error at or near creatre at character 1 hannu=# create table updtarget(id int, val text); CREATE TABLE hannu=# create table updsource(id int, val text); CREATE TABLE hannu=# insert into updtarget(id) values (1); INSERT 16995 1 hannu=# insert into updtarget(id) values (2); INSERT 16996 1 hannu=# insert into updsource(id,val) values (1,'one'); INSERT 16997 1 hannu=# insert into updsource(id,val) values (2,'two'); INSERT 16998 1 hannu=# update updtarget set val = src.val hannu-# from (select s.val from updsource s hannu-#where s.id=updtarget.id) as src hannu-# ; NOTICE: Adding missing FROM-clause entry in subquery for table updtarget UPDATE 2 hannu=# select * from updtarget; id | val +- 1 | one 2 | one (2 rows) there should be no need to add missing FROM-clause entry and the result *should* be: hannu=# select * from updtarget; id | val +- 1 | one 2 | two (2 rows) Hannu ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane kirjutas K, 19.03.2003 kell 16:46: Bruce Momjian [EMAIL PROTECTED] writes: I wasn't sure it made logical sense to allow correlated subqueries in FROM because the FROM is processed before the WHERE. It doesn't; in fact it violates the whole semantic model of SQL, as far as I can see. Sub-selects in FROM are (in principle) evaluated separately and then joined. They can't have cross-references. Makes sense. What I was describing would have been akin to updatable queries where you first do all the joining and then update one of the underlying tables. the more accurate (nonstandard) syntax could have been SELECT src.val, tgt.val FROM updatesrc as src FOR UPDATE, updatetgd as tgt WHERE src.id = tgt.id SET src.val = tgt.val ; I think there is some weird construct in SQL99 that alters this behavior, though. You probably mean WITH, which acts like FROM but has lexically previous (or all in case of WITH RECURSIVE) sub-selects in its namespace. Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Dave, Justin, I have several Informix clients who will be moving to a Postgresql/Aubit4gl solution at some point. The Informix line is, for them, a dead end. One way or another the backend will become Postgresql. Because of the number of SQL statements, I would encourage support where possible and reasonable. Jordan - Original Message - From: Dave Cramer [EMAIL PROTECTED] To: Justin Clift [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; Peter Eisentraut [EMAIL PROTECTED]; Pgsql Hackers [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 10:18 PM Subject: Re: [HACKERS] request for sql3 compliance for the update command Justin, This is certainly the case here. I think IBM is deprecating informix, and many informix users are being forced to make a change, and they are seriously considering postgres as an alternative. It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before making this decision as well. I believe the aubit project has the potential to move postgres forward considerably as well. Dave On Wed, 2003-02-19 at 21:08, Justin Clift wrote: Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. As a thought, will it add significant maintenance penalties or be detrimental? There seem to be quite a lot of Informix people moving to PostgreSQL these days, moreso than Oracle shops. Might have been brought on by IBM's purchase of Informix. Wondering if this one change be a significant improvement in regards to making it easier to migrate, or just a minor thing? Regards and best wishes, Justin Clift regards, tom lane -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
While I can see a subquery in UPDATE as working in most cases:
UPDATE tab
SET col - t.col
FROM (SELECT col from xx) AS t
WHERE ...
but I don't see that working for correlated subqueries, where you want
to set a column based on a value you are updating. (Many use correlated
subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
subqueries? I can't see how they would because you don't have a row
being processed at the FROM stage of the query.
I did look at the SQL99 standards and ROW does appear there:
update statement: positioned ::=
UPDATE target table
SET set clause list
WHERE CURRENT OF cursor name
set clause list ::=
set clause [ { comma set clause }... ]
set clause ::=
update target equals operator update source
| mutated set clause equals operator update source
update target ::=
object column
-- | ROW
| object column
left bracket or trigraph simple value specification right
bracket or trigraph
and later it says:
a) If update target specifies ROW, then let CL be the set of
all columns of T.
The TODO item would be:
Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
This also gets into that weird Informix syntax where you have to
double-paren when you want to use a subquery. Basically, this thing
keeps getting wierder and wierder.
---
Dave Cramer wrote:
Given that the direction of the spec seems to be headed towards the
desired syntax, can we put this on the TODO list?
Dave
On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
Scott,
I can't find page 858 in that document, is it the right one?
also the link s/b ?
ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
On Thu, 20 Feb 2003, Tom Lane wrote:
Hannu Krosing [EMAIL PROTECTED] writes:
Are you against it just on grounds of cleanliness and ANSI compliance,
or do you see more serious problems in letting it in ?
At this point it seems there are two different things being tossed
about. I originally understood Dave to be asking for parens to be
allowed around individual target column names, which seems a useless
frammish to me. What Bruce has pointed out is that a syntax that lets
you assign multiple columns from a single rowsource would be an actual
improvement in functionality, or at least in convenience and efficiency.
(It would also be a substantial bit of work, which is why I think this
isn't what Dave was offering a quick patch to do...) What I'd like to
know right now is which interpretation Informix actually implements.
I don't like adding nonstandard syntaxes that add no functionality ---
but if Informix has done what Bruce is talking about, that's a different
matter altogether.
Tom, I was purusing the wild and wonderfully exciting new SQL
(found here:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
ANSI TC NCITS H2
ISO/IEC JTC 1/SC 32/WG 3
Database
document to see what it had to say, and on this subject, and it looks like
update is going to be supporing this same style we're discussing here.
Look on or around p. 858 in that doc.)
--
Dave Cramer [EMAIL PROTECTED]
Cramer Consulting
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
Given that the direction of the spec seems to be headed towards the desired syntax, can we put this on the TODO list? Dave On Thu, 2003-02-20 at 11:49, Dave Cramer wrote: Scott, I can't find page 858 in that document, is it the right one? also the link s/b ? ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: On Thu, 20 Feb 2003, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
-Original Message- From: Mike Aubury [mailto:[EMAIL PROTECTED]] Sent: 20 February 2003 19:10 To: Dave Page; Tom Lane; Hannu Krosing Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers Subject: Re: [HACKERS] request for sql3 compliance for the update command Informix supports 2 different styles for the update - your one would have to be written : UPDATE djp SET(col1, col2) = ((SELECT col1,col2 FROM some_other_table)) Notice the double brackets ! The first signifies a list of values - the second is the brackets around the subquery... (NB If you try to reference the same table in the Update - you'll get an error) Ahh, of course. I tried double brackets 'cos I figured I might need one pair to indicate the set and one to indicate the subselect, but I didn't think to try a different table. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
On Thu, 20 Feb 2003, Kevin Brown wrote: Tom Lane wrote: UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; As long as any individual item that you can express in the parenthesized (Informix) syntax can also be expressed as an element in a SELECT, then the above is equivalent in every way to the Informix syntax. And since SELECT allows subselects, it seems to me that the PG syntax is complete. My question is whether or not there's likely to be an approved standard way of accomplishing what either syntax does. Is there anything in the current draft that addresses this? Yes there is. I've posted the URL on the hackers list a while back, but here it is again: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf pp 851 to 862, in particular, p 858 defines the the set clause list as supporting multiple column assignment as supporting something like: (target1, target2, target3) = (value1, value2, value3) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 20 February 2003 14:31 To: Hannu Krosing Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers Subject: Re: [HACKERS] request for sql3 compliance for the update command Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Informix SE allows me to do: CREATE TABLE djp(col1 INTEGER, col2 INTEGER) INSERT INTO djp VALUES(1, 2) UPDATE djp SET(col1, col2) = (3, 4) However UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp) Results in a syntax error. I don't have Informix IDS so I don't know if that can do it. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane wrote: UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; As long as any individual item that you can express in the parenthesized (Informix) syntax can also be expressed as an element in a SELECT, then the above is equivalent in every way to the Informix syntax. And since SELECT allows subselects, it seems to me that the PG syntax is complete. My question is whether or not there's likely to be an approved standard way of accomplishing what either syntax does. Is there anything in the current draft that addresses this? -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane [EMAIL PROTECTED] writes: UPDATE totals SET xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), ... but that is awfully tedious and will be inefficiently implemented. This is what Bruce is worried about. On the other hand, one could argue that this is a wrongheaded way to go about it anyway, and the correct way is UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; ... Of course this syntax isn't standard either ... but we already have it. This is nice, but I could see it being a big pain if the join clause wasn't so neat and tidy as a groupid column that you can group by. The Informix syntax has some appeal -- speaking from the point of view of someone who has had to write some awkward update statements like this in the past. (In Oracle where the best syntax is to create an updatable inline view which is pretty much equivalent in expressiveness to the Postgres syntax.) Consider how awkward this query would be if the iterations in the original query overlapped for example. You would have to introduce a another table to the select just to drive the join artificially. For example consider a hypothetical case: UPDATE networks set num_hosts = (select count(*) from hosts where addr netblock) Where some hosts are on multiple nested netblocks. The only way I see to convert that to Postgres's syntax would be to join against the networks table again and then group by the primary key of the networks table. Ick. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
On Wed, Feb 19, 2003 at 12:29:12PM -0500, Tom Lane wrote: SQL99. Looks like the parens got lost again by the time of the final spec. I don't think the parens really matter. It's just the different ordering of columns and values. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
On Wed, Feb 19, 2003 at 04:37:33PM +0100, Peter Eisentraut wrote: That's not what my copy says. Strange. I just looked at all the docs I have and all have it listed the way Dave wrote. So I seem to have to update my docs. Peter, could you send me a copy? Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane kirjutas K, 19.02.2003 kell 21:12: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: Agreed folks are going to have bigger problems from Informix than just this, and in fact I used Informix for years and didn't know they allowed this. However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. I think it makes sense to add it only if it adds functionality. It makes it easier (less keystrokes) to write as well as similar in appearance to INSERT, so the same code can be used to generate the queries. If we were at adding functionality then IMHO making VALUES(x,y,z) a proper rowsource would be a more worthy effort. --- Hannu ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: Agreed folks are going to have bigger problems from Informix than just this, and in fact I used Informix for years and didn't know they allowed this. However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. hannu=# create table target (id serial, a int, b int, c int); NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for SERIAL column 'target.id' CREATE TABLE hannu=# insert into target(a,b,c) values (0,0,0); INSERT 16983 1 hannu=# insert into target(a,b,c) values (1,1,1); INSERT 16984 1 hannu=# update target set hannu-# a = source.a1, b=source.a2, c=source.a3 hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source hannu-# where id = 1 hannu-# ; UPDATE 1 hannu=# select * from target; id | a | b | c +---+---+--- 2 | 1 | 1 | 1 1 | 1 | 2 | 3 (2 rows) hannu=# -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Hannu Krosing [EMAIL PROTECTED] writes: Bruce Momjian kirjutas N, 20.02.2003 kell 06:16: However, what solution do we have for UPDATE (coll...) = (select val...) for folks? It is awkward to repeat a query multiple times in an UPDATE. hannu=# update target set hannu-# a = source.a1, b=source.a2, c=source.a3 hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source hannu-# where id = 1 hannu-# ; I've been trying to think of a case that can't be handled by transposing the sub-select into FROM. I'm not sure there are any. I thought for a minute that grouped aggregates would be an issue. For example, suppose table totals has one row for each distinct value of groupid appearing in table details, and you use it to store group aggregate values. You can do UPDATE totals SET xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid), xmin = (SELECT min(x) FROM details WHERE groupid = totals.groupid), ymax = (SELECT max(y) FROM details WHERE groupid = totals.groupid), ymin = (SELECT min(y) FROM details WHERE groupid = totals.groupid), ... but that is awfully tedious and will be inefficiently implemented. This is what Bruce is worried about. On the other hand, one could argue that this is a wrongheaded way to go about it anyway, and the correct way is UPDATE totals SET xmax = ss.xmax, xmin = ss.xmin, ... FROM (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss WHERE groupid = ss.groupid; If there is indeed a row in totals for every groupid, then this will certainly beat out the first approach that has to run a separate query for each groupid, even if we avoid a separate query for each aggregate. (It could maybe lose if you only wanted to update the totals for a few groupids; but even then you could probably push the WHERE conditions restricting the groups into the sub-select.) Of course this syntax isn't standard either ... but we already have it. Right now I'm not convinced there is a functionality argument for supporting the Informix-style syntax, even with multiple columns. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
sorry, it's the -02 document. just change the last 01 to 02 and you'll get the right one. On 20 Feb 2003, Dave Cramer wrote: Scott, I can't find page 858 in that document, is it the right one? also the link s/b ? ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: On Thu, 20 Feb 2003, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
On Thu, Feb 20, 2003 at 09:31:21AM -0500, Tom Lane wrote: about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. Informix syntax is listed on http://www-3.ibm.com/software/data/informix/pubs/library/visionary/infoshelf/sqls/01start.fm.html#156200 It's more than just parens IMO. :-) Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
The right URL (I'll get it eventually) is ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf That time I exactly copied the URL. sorry for the wrong one previously. On 20 Feb 2003, Dave Cramer wrote: Scott, Thanks for the reference, I think the actual document is ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf and it is in section 14.12 on or about page 839 Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: On Thu, 20 Feb 2003, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
On Thu, 20 Feb 2003, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
Scott, I can't find page 858 in that document, is it the right one? also the link s/b ? ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf Dave On Thu, 2003-02-20 at 11:20, scott.marlowe wrote: On Thu, 20 Feb 2003, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Are you against it just on grounds of cleanliness and ANSI compliance, or do you see more serious problems in letting it in ? At this point it seems there are two different things being tossed about. I originally understood Dave to be asking for parens to be allowed around individual target column names, which seems a useless frammish to me. What Bruce has pointed out is that a syntax that lets you assign multiple columns from a single rowsource would be an actual improvement in functionality, or at least in convenience and efficiency. (It would also be a substantial bit of work, which is why I think this isn't what Dave was offering a quick patch to do...) What I'd like to know right now is which interpretation Informix actually implements. I don't like adding nonstandard syntaxes that add no functionality --- but if Informix has done what Bruce is talking about, that's a different matter altogether. Tom, I was purusing the wild and wonderfully exciting new SQL (found here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf) ANSI TC NCITS H2 ISO/IEC JTC 1/SC 32/WG 3 Database document to see what it had to say, and on this subject, and it looks like update is going to be supporing this same style we're discussing here. Look on or around p. 858 in that doc.) -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
Informix supports 2 different styles for the update - your one would have to be written : UPDATE djp SET(col1, col2) = ((SELECT col1,col2 FROM some_other_table)) Notice the double brackets ! The first signifies a list of values - the second is the brackets around the subquery... (NB If you try to reference the same table in the Update - you'll get an error) For single columns you could still write : UPDATE djp SET col1 = (SELECT col2 FROM some_other_table) Notice - one more set of brackets on the right as on the left UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] request for sql3 compliance for the update command
I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant set clause ::= update target equals operator row value designator update target ::= object column | left paren object column list right paren or can someone think of another way? -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] request for sql3 compliance for the update command
Bruce, Can you chime in with your support here? Dave I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant set clause ::= update target equals operator row value designator update target ::= object column | left paren object column list right paren or can someone think of another way? -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
On Wed, Feb 19, 2003 at 07:31:35AM -0500, Dave Cramer wrote: Bruce, Can you chime in with your support here? Dave I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant set clause ::= update target equals operator row value designator update target ::= object column | left paren object column list right paren or can someone think of another way? I don't understand the original problem. What does informix give you? A text file full of update table set ()=() which you then try to feed into postgres? In that case, why not pass said text file through a sed or perl script first? Cheers, Patrick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
Patrick, No, they support the syntax: update table set (col1, col2, col3) = ( val1, val2, val3 ) I have a customer with a rather large application which uses this syntax, because they were using informix. There is also a rather interesting 4GL project called aubit which is on sourceforge. They would also like to see this supported for the same reasons. Dave On Wed, 2003-02-19 at 15:02, Patrick Welche wrote: I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant set clause ::= update target equals operator row value designator update target ::= object column | left paren object column list right paren or can someone think of another way? I don't understand the original problem. What does informix give you? A text file full of update table set ()=() which you then try to feed into postgres? In that case, why not pass said text file through a sed or perl script first? Cheers, Patrick -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] request for sql3 compliance for the update command
On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote: I have a customer with a rather large application which uses this syntax, because they were using informix. There is also a rather interesting 4GL project called aubit which is on sourceforge. They would also like to see this supported for the same reasons. Hey - I was going to say that... For the curious: Quick URL - http://aubit4gl.sourceforge.net/ Its a 'clone' of the Informix 4GL tool, a nice 'clean' language specifically designed for writing database applications, with both curses GTK, support for multiple database types and a bunch of other things... We're about to release version 0.30 - and I was going to wait until then ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
While I don't see the syntax of: update table set (col...) = ( val...) as valuable compared to separate col=val assignments, I do see a value in allowing subqueries in such assignments: update table set (col...) = ( select val ..) Without it, you have to do separate subquery statements, and if they are complex, that is a waste. I assume that was the motivation for the feature. --- Dave Cramer wrote: Patrick, No, they support the syntax: update table set (col1, col2, col3) = ( val1, val2, val3 ) I have a customer with a rather large application which uses this syntax, because they were using informix. There is also a rather interesting 4GL project called aubit which is on sourceforge. They would also like to see this supported for the same reasons. Dave On Wed, 2003-02-19 at 15:02, Patrick Welche wrote: I have a large customer who is converting from informix to postgres and they have made extensive use of update table set (col...) = ( val...) as a first pass would it be possible to translate this in the parser to update table set col=val It would appear that this is SQL3 compliant set clause ::= update target equals operator row value designator update target ::= object column | left paren object column list right paren or can someone think of another way? I don't understand the original problem. What does informix give you? A text file full of update table set ()=() which you then try to feed into postgres? In that case, why not pass said text file through a sed or perl script first? Cheers, Patrick -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
After a long battle with technology,[EMAIL PROTECTED] (Mike Aubury), an earthling, wrote: On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote: I have a customer with a rather large application which uses this syntax, because they were using informix. There is also a rather interesting 4GL project called aubit which is on sourceforge. They would also like to see this supported for the same reasons. Hey - I was going to say that... For the curious: Quick URL - http://aubit4gl.sourceforge.net/ Its a 'clone' of the Informix 4GL tool, a nice 'clean' language specifically designed for writing database applications, with both curses GTK, support for multiple database types and a bunch of other things... We're about to release version 0.30 - and I was going to wait until then I tried it out a while back; couldn't get it to compile, probably due to there being a bit too much 'bleeding' to the 'bleeding edge.' It looks as though it could be pretty interesting, if PG support matures (which can certainly be a two way street!). How's the cross-platform support? Aubit would be an easier sell, to be sure, if it is readily deployable on Those Other Platforms, too... -- (concatenate 'string cbbrowne @acm.org) http://www.ntlug.org/~cbbrowne/multiplexor.html MSDOS didn't get as bad as it is overnight -- it took over ten years of careful development. -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
On 19 Feb 2003, Dave Cramer wrote: Yes, the company in question is more than evaluating it; this request is a result of a project to port their application to postgres. Ahh. I thought you were referring to IBM. That is, that IBM was evaluating Postgres... Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] request for sql3 compliance for the update command
On 19 Feb 2003, Dave Cramer wrote: Justin, This is certainly the case here. I think IBM is deprecating informix, and many informix users are being forced to make a change, and they are seriously considering postgres as an alternative. Do you have any evidence that they are evaluating it? Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
Justin Clift [EMAIL PROTECTED] writes: As a thought, will it add significant maintenance penalties or be detrimental? Well, yes it will if you look at the big picture. In the past we've generally regretted it when we put in nonstandard features just to be compatible with some other database. (Tatsuo already pointed out the foo = NULL fiasco.) And we get ragged on regularly for the non-SQL- standard features we've inherited from Berkeley Postgres (eg, the implicit-FROM frammish that was under discussion yesterday). I don't think we're really doing the users any favor either. If they want to move to some other database after Postgres, are they likely to get that other database to insert a not-very-useful nonstandard syntax? Sooner or later they're going to have to bite this bullet, and it may as well be sooner. (I can hardly believe that this is the worst compatibility issue an ex-Informix user would face, anyhow.) This is an Informix-ism. It should stay that way. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
Dave Cramer writes:
update table set (col...) = ( val...)
It would appear that this is SQL3 compliant
set clause ::=
update target equals operator row value designator
update target ::=
object column
| left paren object column list right paren
That's not what my copy says.
set clause list ::=
set clause [ { comma set clause }... ]
set clause ::=
update target equals operator update source
| mutated set clause equals operator update source
update target ::=
object column
| ROW
| object column
left bracket or trigraph simple value specification right
bracket or trigraph
object column ::= column name
mutated set clause ::=
mutated target period method name
mutated target ::=
object column
| mutated set clause
update source ::=
value expression
| contextually typed value specification
(And I'm pretty sure I have the right version of the standard.)
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] request for sql3 compliance for the update command
Dave Cramer [EMAIL PROTECTED] writes: Referring to http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt the following grammar exists is the reference above valid? Sep 93? That would be an extremely early draft of what eventually became SQL99. Looks like the parens got lost again by the time of the final spec. Given that there's no visible functionality gain from allowing parens here, I'm not surprised that the spec authors decided it wasn't such a hot idea after all... too bad Informix didn't get the word :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? Dave On Wed, 2003-02-19 at 12:29, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Referring to http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt the following grammar exists is the reference above valid? Sep 93? That would be an extremely early draft of what eventually became SQL99. Looks like the parens got lost again by the time of the final spec. Given that there's no visible functionality gain from allowing parens here, I'm not surprised that the spec authors decided it wasn't such a hot idea after all... too bad Informix didn't get the word :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] request for sql3 compliance for the update command
While I don't see the syntax of: update table set (col...) = ( val...) as valuable compared to separate col=val assignments, I do see a value in allowing subqueries in such assignments: update table set (col...) = ( select val ..) Without it, you have to do separate subquery statements, and if they are complex, that is a waste. I assume that was the motivation for the feature. The number of times I've needed this feature... :) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. As a thought, will it add significant maintenance penalties or be detrimental? There seem to be quite a lot of Informix people moving to PostgreSQL these days, moreso than Oracle shops. Might have been brought on by IBM's purchase of Informix. Wondering if this one change be a significant improvement in regards to making it easier to migrate, or just a minor thing? Regards and best wishes, Justin Clift regards, tom lane -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] request for sql3 compliance for the update command
Justin, This is certainly the case here. I think IBM is deprecating informix, and many informix users are being forced to make a change, and they are seriously considering postgres as an alternative. It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before making this decision as well. I believe the aubit project has the potential to move postgres forward considerably as well. Dave On Wed, 2003-02-19 at 21:08, Justin Clift wrote: Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Ok, if a patch were submitted to the parser to allow the syntax in question would it be considered? I would vote against it ... but that's only one vote. As a thought, will it add significant maintenance penalties or be detrimental? There seem to be quite a lot of Informix people moving to PostgreSQL these days, moreso than Oracle shops. Might have been brought on by IBM's purchase of Informix. Wondering if this one change be a significant improvement in regards to making it easier to migrate, or just a minor thing? Regards and best wishes, Justin Clift regards, tom lane -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] request for sql3 compliance for the update command
BTW, looking at the SQL99 standard, I see that you can do UPDATE table SET ROW = foo WHERE ... where foo is supposed to yield a row of the same rowtype as table --- I didn't dig through the spec in detail, but I imagine foo can be a sub-select. I don't care a whole lot for that, though, since it would be a real pain in the neck if you're not updating all the columns. You'd have to go UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo) How is the Informix syntax any better? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] request for sql3 compliance for the update command
Christopher Kings-Lynne wrote: BTW, looking at the SQL99 standard, I see that you can do UPDATE table SET ROW = foo WHERE ... where foo is supposed to yield a row of the same rowtype as table --- I didn't dig through the spec in detail, but I imagine foo can be a sub-select. I don't care a whole lot for that, though, since it would be a real pain in the neck if you're not updating all the columns. You'd have to go UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo) How is the Informix syntax any better? With Informix, you specify the columns you want updated in parens, rather than saying ROW. Does the spec allow a list of columns after ROW? That would be nice, like Informix. I doubt many folks update all the columns. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
