Re: [HACKERS] Missing array support
Joe, do you need a TODO added for this?
---
Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <[EMAIL PROTECTED]> writes:
> >
> >>So array[] should produce '{}' of (an array) type determined by the
> >>context? OK -- seems easy enough.
> >
> > Is it? I think we'd decided that this could only reasonably be handled
> > by creating a datatype representing array-of-UNKNOWN. I'm afraid to do
> > that because I think it might allow the parser's type resolution
> > algorithms to follow paths we will not like. Perhaps it can be made to
> > work, but I think it will require some careful study.
>
> I took a closer look -- yeah, without array-of-UNKNOWN I don't think we
> can make this work.
>
> I got something working by forcing the element type to UNKNOWN when the
> elements list is empty in transformExpr(), but then select_common_type()
> turns around and turns UNKNOWN into TEXT, so you wind up with an empty
> text[].
>
> I won't bother sending that patch in because I *know* it will get
> rejected ;-)
>
> I guess we should put array-of-UNKNOWN on the list of things to look at
> for 7.5.
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
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 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Bruce Momjian wrote: I guess we should put array-of-UNKNOWN on the list of things to look at for 7.5. Yeah; maybe something like this? Delay resolution of array expression type as long as possible so that assignment coercion can be performed on empty array expressions. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Missing array support
Added. --- Joe Conway wrote: > Bruce Momjian wrote: > >>I guess we should put array-of-UNKNOWN on the list of things to look at > >>for 7.5. > >> > > Yeah; maybe something like this? > > Delay resolution of array expression type as long as possible so that > assignment coercion can be performed on empty array expressions. > > Joe > > -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing array support
Peter Eisentraut wrote: * Using an array as a table source using UNNEST, something like: select * from unnest(test.b); (Check the exact spec to be sure; clause 7.6.) select * from unnest(array['a','b']); ?column? -- a b select * from unnest(array['a','b']) WITH ORDINALITY; ?column? | ?column? --+-- 1| a 2| b select * from unnest(array['a','b']) as t(f1, f2) WITH ORDINALITY; f1 | f2 + 1 | a 2 | b The WITH ORDINALITY goes before the AS clause. The reason it is defined in terms of the LATERAL clause is that that allows you to refer to column aliases defined in FROM items to its left. This is the way variable arguments of function calls as table sources can be resolved. (At least this is my interpretation. I found some examples on the web a few months ago about this.) If I can get this done *without* supporting LATERAL by the end of the evening (i.e. just implement the examples), would it possibly be accepted? Or should UNNEST wait until we get LATERAL? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
So array[] should produce '{}' of (an array) type determined by the
context? OK -- seems easy enough.
Is it? I think we'd decided that this could only reasonably be handled
by creating a datatype representing array-of-UNKNOWN. I'm afraid to do
that because I think it might allow the parser's type resolution
algorithms to follow paths we will not like. Perhaps it can be made to
work, but I think it will require some careful study.
I took a closer look -- yeah, without array-of-UNKNOWN I don't think we
can make this work.
I got something working by forcing the element type to UNKNOWN when the
elements list is empty in transformExpr(), but then select_common_type()
turns around and turns UNKNOWN into TEXT, so you wind up with an empty
text[].
I won't bother sending that patch in because I *know* it will get
rejected ;-)
I guess we should put array-of-UNKNOWN on the list of things to look at
for 7.5.
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: CVS tip compile failure (was Re: [HACKERS] Missing array support)
Peter Eisentraut wrote: I tried readding the files now, I seems it got them now. Possibly cvs was confused because those files already existed in the 7.3 branch so it found "dead revisions" in the head branch. Thanks, this fixed it for me. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: CVS tip compile failure (was Re: [HACKERS] Missing array support)
I tried readding the files now, I seems it got them now. Possibly cvs was confused because those files already existed in the 7.3 branch so it found "dead revisions" in the head branch. Joe Conway writes: > Hmmm, I just updated to cvs tip (so I could try this), did `configure`, > `make clean`, and `make all` and I'm getting this failure: > > make[2]: Leaving directory `/opt/src/pgsql/src/port' > make -C backend all > make[2]: Entering directory `/opt/src/pgsql/src/backend' > msgfmt -o po/cs.mo po/cs.po > msgfmt -o po/de.mo po/de.po > msgfmt -o po/es.mo po/es.po > make[2]: *** No rule to make target `po/hr.po', needed by `po/hr.mo'. Stop. > make[2]: Leaving directory `/opt/src/pgsql/src/backend' > make[1]: *** [all] Error 2 > make[1]: Leaving directory `/opt/src/pgsql/src' > make: *** [all] Error 2 > > Any ideas? > > Joe > -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: CVS tip compile failure (was Re: [HACKERS] Missing array support)
On Sat, 28 Jun 2003, Joe Conway wrote: > > Do I need to do something to get new language files? > > causing me problems. Did a "cvs add" get missed somewhere, or am I doing > something wrong? Yes, a couple of cvs add was forgotten. Peter made an update with the comment "Merge PO file updates from 7.3 branch.". I checked out a new copy with tag REL7_3_2 and there are the missing files (at least the one I checked, but probably the rest also). -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Missing array support
Peter Eisentraut wrote:
It doesn't say anything specifically about multidimensional arrays, but
the grammar clearly allows declaring arrays of arrays.
::=
|
|
|
|
::=
::=
::=
ARRAY
Yeah, I noticed that after I replied. So
means something like this is valid
integer ARRAY[3] ARRAY[4] ARRAY[5]
?
Is this the same then as our syntax?
integer [3][4][5]
This also has some consequences for the cardinality function. In order to
get the cardinality of the second dimension, you'd need to call
cardinality(a[1]). (I suppose it allows different cardinalities at
various positions, so the array does not need to be an n-dimensional
rectangle.)
Hmmm. So this implies that if arr is a 2D array, we need to treat:
arr as a 2D array
arr[n] as a 1D array
arr[n][m] as a scalar
If that's true, we have a good bit of work left to do to be compliant; e.g.:
regression=# select f from z;
f
---
{{{1,1},{1,1},{1,1}},{{1,1},{1,1},{1,1}},{{1,1},{1,1},{1,1}},{{1,1},{1,1},{1,1}}}
(1 row)
regression=# select f[1][1] from z;
f
---
(1 row)
regression=# select f[1][1][1] from z;
f
---
1
(1 row)
Based on the above, "select f[1][1] from z;" ought to result in "{1,1}"?
select * from unnest(array['a','b']) as t(f1, f2) WITH ORDINALITY;
f1 | f2
+
1 | a
2 | b
The WITH ORDINALITY goes before the AS clause.
OK
The reason it is defined in terms of the LATERAL clause is that that
allows you to refer to column aliases defined in FROM items to its left.
This is the way variable arguments of function calls as table sources can
be resolved. (At least this is my interpretation. I found some examples
on the web a few months ago about this.)
Thanks for explaining that. I've never seen a LATERAL clause, and I was
wondering just what this part meant. So this applies to the discussion
we had a while back about set returning functions in the targetlist?
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: CVS tip compile failure (was Re: [HACKERS] Missing array support)
Joe Conway wrote: FWIW, I find that if I remove "hr" and "tr" from this line in /opt/src/pgsql/src/backend/nls.mk, everything goes fine: AVAIL_LANGUAGES := cs de es hu ru sv zh_CN zh_TW Do I need to do something to get new language files? Replying to myself again ;-) I was a bit too quick to say "everything goes fine". I got several more nls related failures. Attached is the patch I used to back out the ones causing me problems. Did a "cvs add" get missed somewhere, or am I doing something wrong? Thanks, Joe Index: src/backend/nls.mk === RCS file: /opt/src/cvs/pgsql-server/src/backend/nls.mk,v retrieving revision 1.4 diff -c -r1.4 nls.mk *** src/backend/nls.mk 28 Jun 2003 22:30:59 - 1.4 --- src/backend/nls.mk 29 Jun 2003 01:09:29 - *** *** 1,6 # $Header: /opt/src/cvs/pgsql-server/src/backend/nls.mk,v 1.4 2003/06/28 22:30:59 petere Exp $ CATALOG_NAME := postgres ! AVAIL_LANGUAGES := cs de es hr hu ru sv tr zh_CN zh_TW GETTEXT_FILES := + gettext-files GETTEXT_TRIGGERS:= elog:2 postmaster_error yyerror --- 1,6 # $Header: /opt/src/cvs/pgsql-server/src/backend/nls.mk,v 1.4 2003/06/28 22:30:59 petere Exp $ CATALOG_NAME := postgres ! AVAIL_LANGUAGES := cs de es hu ru sv zh_CN zh_TW GETTEXT_FILES := + gettext-files GETTEXT_TRIGGERS:= elog:2 postmaster_error yyerror Index: src/bin/pg_controldata/nls.mk === RCS file: /opt/src/cvs/pgsql-server/src/bin/pg_controldata/nls.mk,v retrieving revision 1.4 diff -c -r1.4 nls.mk *** src/bin/pg_controldata/nls.mk 28 Jun 2003 22:31:00 - 1.4 --- src/bin/pg_controldata/nls.mk 29 Jun 2003 01:19:00 - *** *** 1,5 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_controldata/nls.mk,v 1.4 2003/06/28 22:31:00 petere Exp $ CATALOG_NAME := pg_controldata ! AVAIL_LANGUAGES := de es fr hu pt_BR ru sv zh_CN GETTEXT_FILES := pg_controldata.c GETTEXT_TRIGGERS:= _ --- 1,5 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_controldata/nls.mk,v 1.4 2003/06/28 22:31:00 petere Exp $ CATALOG_NAME := pg_controldata ! AVAIL_LANGUAGES := de es pt_BR ru sv zh_CN GETTEXT_FILES := pg_controldata.c GETTEXT_TRIGGERS:= _ Index: src/bin/pg_dump/nls.mk === RCS file: /opt/src/cvs/pgsql-server/src/bin/pg_dump/nls.mk,v retrieving revision 1.8 diff -c -r1.8 nls.mk *** src/bin/pg_dump/nls.mk 28 Jun 2003 22:31:01 - 1.8 --- src/bin/pg_dump/nls.mk 29 Jun 2003 01:16:32 - *** *** 1,6 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_dump/nls.mk,v 1.8 2003/06/28 22:31:01 petere Exp $ CATALOG_NAME := pg_dump ! AVAIL_LANGUAGES := cs de pt_BR ru sv zh_CN zh_TW GETTEXT_FILES := pg_dump.c common.c pg_backup_archiver.c pg_backup_custom.c \ pg_backup_db.c pg_backup_files.c pg_backup_null.c \ pg_backup_tar.c pg_restore.c pg_dumpall.c --- 1,6 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_dump/nls.mk,v 1.8 2003/06/28 22:31:01 petere Exp $ CATALOG_NAME := pg_dump ! AVAIL_LANGUAGES := cs de ru sv zh_CN zh_TW GETTEXT_FILES := pg_dump.c common.c pg_backup_archiver.c pg_backup_custom.c \ pg_backup_db.c pg_backup_files.c pg_backup_null.c \ pg_backup_tar.c pg_restore.c pg_dumpall.c Index: src/bin/pg_resetxlog/nls.mk === RCS file: /opt/src/cvs/pgsql-server/src/bin/pg_resetxlog/nls.mk,v retrieving revision 1.5 diff -c -r1.5 nls.mk *** src/bin/pg_resetxlog/nls.mk 28 Jun 2003 22:31:01 - 1.5 --- src/bin/pg_resetxlog/nls.mk 29 Jun 2003 01:19:26 - *** *** 1,5 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_resetxlog/nls.mk,v 1.5 2003/06/28 22:31:01 petere Exp $ CATALOG_NAME := pg_resetxlog ! AVAIL_LANGUAGES := de hu pt_BR ru sv zh_CN GETTEXT_FILES := pg_resetxlog.c GETTEXT_TRIGGERS:= _ --- 1,5 # $Header: /opt/src/cvs/pgsql-server/src/bin/pg_resetxlog/nls.mk,v 1.5 2003/06/28 22:31:01 petere Exp $ CATALOG_NAME := pg_resetxlog ! AVAIL_LANGUAGES := de pt_BR ru sv zh_CN GETTEXT_FILES := pg_resetxlog.c GETTEXT_TRIGGERS:= _ Index: src/bin/psql/nls.mk === RCS file: /opt/src/cvs/pgsql-server/src/bin/psql/nls.mk,v retrieving revision 1.8 diff -c -r1.8 nls.mk *** src/bin/psql/nls.mk 28 Jun 2003 22:31:01 - 1.8 --- src/bin/psql/nls.mk 29 Jun 2003 01:17:31 - *** *** 1,6 # $Header: /opt/src/cvs/pgsql-server/src/bin/psql/nls.mk,v 1.8 2003/06/28 22:31:01 petere Exp $ CATALOG_NAME := psql ! AVAIL_LANGUAGES := cs de fr hu ru sv zh_CN zh_TW GETTEXT_FILES := command.c common.c copy.c help.c input.c large_obj.c \
Re: CVS tip compile failure (was Re: [HACKERS] Missing array support)
Joe Conway wrote: Hmmm, I just updated to cvs tip (so I could try this), did `configure`, `make clean`, and `make all` and I'm getting this failure: make[2]: Leaving directory `/opt/src/pgsql/src/port' make -C backend all make[2]: Entering directory `/opt/src/pgsql/src/backend' msgfmt -o po/cs.mo po/cs.po msgfmt -o po/de.mo po/de.po msgfmt -o po/es.mo po/es.po make[2]: *** No rule to make target `po/hr.po', needed by `po/hr.mo'. Stop. FWIW, I find that if I remove "hr" and "tr" from this line in /opt/src/pgsql/src/backend/nls.mk, everything goes fine: AVAIL_LANGUAGES := cs de es hu ru sv zh_CN zh_TW Do I need to do something to get new language files? Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
CVS tip compile failure (was Re: [HACKERS] Missing array support)
Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: Btw., it would be really nice if some limited form of this could get done, so I could finish the information schema views pertaining to group privileges. I'd just need a way to find out what users are in what groups. As of a few minutes ago, SELECT g.groname FROM pg_user u, pg_group g WHERE u.usesysid = ANY (g.grolist) AND u.usename = current_user; Hmmm, I just updated to cvs tip (so I could try this), did `configure`, `make clean`, and `make all` and I'm getting this failure: make[2]: Leaving directory `/opt/src/pgsql/src/port' make -C backend all make[2]: Entering directory `/opt/src/pgsql/src/backend' msgfmt -o po/cs.mo po/cs.po msgfmt -o po/de.mo po/de.po msgfmt -o po/es.mo po/es.po make[2]: *** No rule to make target `po/hr.po', needed by `po/hr.mo'. Stop. make[2]: Leaving directory `/opt/src/pgsql/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/opt/src/pgsql/src' make: *** [all] Error 2 Any ideas? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Missing array support
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Btw., it would be really nice if some limited form of this could get done, > so I could finish the information schema views pertaining to group > privileges. I'd just need a way to find out what users are in what > groups. As of a few minutes ago, SELECT g.groname FROM pg_user u, pg_group g WHERE u.usesysid = ANY (g.grolist) AND u.usename = current_user; regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Peter Eisentraut wrote: Btw., it would be really nice if some limited form of this could get done, so I could finish the information schema views pertaining to group privileges. I'd just need a way to find out what users are in what groups. If unnest() would work for locally constant arguments, I think it could be done like SELECT g.groname FROM pg_user u, pg_group g WHERE u.usesysid IN (SELECT * FROM UNNEST((SELECT grolist FROM pg_group WHERE grosysid = g.grosysid))) AND u.usename = current_user; Or is there some other way to do this now? It isn't in CVS yet, but hopefully before Monday evening you'll be able to do this: regression=# create user u1; CREATE USER regression=# create user u2; CREATE USER regression=# create user u3; CREATE USER regression=# create group g1 with user u1,u2; CREATE GROUP regression=# create group g2 with user u1,u2,u3; CREATE GROUP regression=# \c - u1 You are now connected as new user u1. regression=> SELECT g.groname FROM pg_group g, pg_user u WHERE u.usename = current_user AND u.usesysid = ANY (g.grolist); groname - g1 g2 (2 rows) Joe ---(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] Missing array support
I wrote: > * Using an array as a table source using UNNEST, something like: > > select * from unnest(test.b); Btw., it would be really nice if some limited form of this could get done, so I could finish the information schema views pertaining to group privileges. I'd just need a way to find out what users are in what groups. If unnest() would work for locally constant arguments, I think it could be done like SELECT g.groname FROM pg_user u, pg_group g WHERE u.usesysid IN (SELECT * FROM UNNEST((SELECT grolist FROM pg_group WHERE grosysid = g.grosysid))) AND u.usename = current_user; Or is there some other way to do this now? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Joe Conway writes: > I don't see anything about multidimensional arrays at all. I take it > this is SQL99 (ISO/IEC 9075-2:1999 (E))? Can you point to a more > specific paragraph? It doesn't say anything specifically about multidimensional arrays, but the grammar clearly allows declaring arrays of arrays. ::= | | | | ::= ::= ::= ARRAY This also has some consequences for the cardinality function. In order to get the cardinality of the second dimension, you'd need to call cardinality(a[1]). (I suppose it allows different cardinalities at various positions, so the array does not need to be an n-dimensional rectangle.) > > * Using an array as a table source using UNNEST, something like: > > > > select * from unnest(test.b); > > (Check the exact spec to be sure; clause 7.6.) > Whew! Anyone care to help me interpret that! At it's most basic level, I > think these are valid: > > select * from unnest(array['a','b']); > ?column? > -- > a > b > > select * from unnest(array['a','b']) WITH ORDINALITY; > ?column? | ?column? > --+-- > 1| a > 2| b Yes. > select * from unnest(array['a','b']) as t(f1, f2) WITH ORDINALITY; > f1 | f2 > + > 1 | a > 2 | b The WITH ORDINALITY goes before the AS clause. The reason it is defined in terms of the LATERAL clause is that that allows you to refer to column aliases defined in FROM items to its left. This is the way variable arguments of function calls as table sources can be resolved. (At least this is my interpretation. I found some examples on the web a few months ago about this.) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Missing array support
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
So array[] should produce '{}' of (an array) type determined by the
context? OK -- seems easy enough.
Is it? I think we'd decided that this could only reasonably be handled
by creating a datatype representing array-of-UNKNOWN. I'm afraid to do
that because I think it might allow the parser's type resolution
algorithms to follow paths we will not like. Perhaps it can be made to
work, but I think it will require some careful study.
But see the spec wording:
1) The declared type DT of an ES is ET ARRAY[0],
where the element type ET is determined by the context in which ES
appears. ES is effectively replaced by CAST ( ES AS DT ).
NOTE 69 – In every such context, ES is uniquely associated with some
expression or site of declared type DT, which thereby becomes the
declared type of ES.
I took that to mean that this sould only work in contexts where the data
type is known.
Come to think of it, I guess in most cases of ARRAY[elem1,elem2,elem3]
we derive the data type using the elements in the array expression, so
in practice there may be few places where this would work. We should be
able to come up with a data type for inserts and updates though,
shouldn't we?
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Missing array support
Joe Conway <[EMAIL PROTECTED]> writes:
> So array[] should produce '{}' of (an array) type determined by the
> context? OK -- seems easy enough.
Is it? I think we'd decided that this could only reasonably be handled
by creating a datatype representing array-of-UNKNOWN. I'm afraid to do
that because I think it might allow the parser's type resolution
algorithms to follow paths we will not like. Perhaps it can be made to
work, but I think it will require some careful study.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Tom Lane wrote: It's just a matter of staking out what's considered an implemented feature. The ARRAY[] syntax is definitely in, so if it needs a few adjustments around the edges to make it more spec-compliant, no one will blink at doing that during beta. OK, but some of what Peter requested were new features too. If I were you I'd file this on the to-fix-later list and concentrate on polymorphic aggregates during the next couple days. If that's not done by Tuesday it will be a tough sell to put in during beta. Agreed. I'm planning (in principle at least) concentrate on this stuff between now and Monday evening, and I'm taking Monday off work, so hopefully I can get a fair amount done. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing array support
Bruce Momjian <[EMAIL PROTECTED]> writes: > Joe Conway wrote: >> Yeah, but isn't feature freeze July 1? > Yes, but once the "feature" is in, you can adjust it if it isn't > working. > I am just pointing out that beating the system is a popular hacker > passtime during beta. :-) It's just a matter of staking out what's considered an implemented feature. The ARRAY[] syntax is definitely in, so if it needs a few adjustments around the edges to make it more spec-compliant, no one will blink at doing that during beta. If I were you I'd file this on the to-fix-later list and concentrate on polymorphic aggregates during the next couple days. If that's not done by Tuesday it will be a tough sell to put in during beta. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Missing array support
Joe Conway wrote: > Bruce Momjian wrote: > >>None of this is very difficult. I'll try to fit it in between now and > >>Monday evening, but if not it's very doable for 7.5. > > > > Joe, you have to get in the swing of things --- beta isn't until July > > 15, and even after that, you can fix bugs, so once it is in, you can > > fiddle with it for months. :-) > > > > :-) > > Yeah, but isn't feature freeze July 1? Yes, but once the "feature" is in, you can adjust it if it isn't working. It might not apply to your item, though, because anything that requires system catalog adjustments is frowned on during beta. I am just pointing out that beating the system is a popular hacker passtime during beta. :-) -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Missing array support
Bruce Momjian wrote: None of this is very difficult. I'll try to fit it in between now and Monday evening, but if not it's very doable for 7.5. Joe, you have to get in the swing of things --- beta isn't until July 15, and even after that, you can fix bugs, so once it is in, you can fiddle with it for months. :-) :-) Yeah, but isn't feature freeze July 1? Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing array support
> > * Some information schema work (doing that now...) > > > So I take it I need not worry about that? > > > None of this is very difficult. I'll try to fit it in between now and > Monday evening, but if not it's very doable for 7.5. Joe, you have to get in the swing of things --- beta isn't until July 15, and even after that, you can fix bugs, so once it is in, you can fiddle with it for months. :-) -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Missing array support
Peter Eisentraut wrote:
Some nice advances to SQL standard array support were made, but there are
a few things that don't work yet in the sense of feature S091 "Basic array
support". Joe, do you want to take on some of these? They should be
pretty easy (for you).
* Declaration of multidimensional arrays (see clause 6.1):
create table test2 (a int, b text array[5] array[6]);
ERROR: syntax error at or near "array" at character 44
I don't see anything about multidimensional arrays at all. I take it
this is SQL99 (ISO/IEC 9075-2:1999 (E))? Can you point to a more
specific paragraph?
* Empty arrays (see clause 6.4):
insert into test values (1, array[]);
ERROR: syntax error at or near "]" at character 35
I saw this, but interpreted it as a data type specification, not an
expression. Here's what SQL200x says:
::=
ARRAY
Syntax Rules
1) The declared type DT of an ES is ET ARRAY[0],
where the element type ET is determined by the context in which ES
appears. ES is effectively replaced by CAST ( ES AS DT ).
NOTE 69 – In every such context, ES is uniquely associated with some
expression or site of declared type DT, which thereby becomes the
declared type of ES.
So array[] should produce '{}' of (an array) type determined by the
context? OK -- seems easy enough.
* Cardinality function (returns array dimensions, see clause 6.17).
::=
CARDINALITY
6) If is specified, then the declared type of
the result is exact numeric with implementation-defined precision and
scale 0 (zero).
8) The result of is the number of elements of
the result of the .
Seems easy.
* Using an array as a table source using UNNEST, something like:
select * from unnest(test.b);
(Check the exact spec to be sure; clause 7.6.)
Interesting. I already wrote (essentially) this function, but it was
rejected months ago when we were discussing its limitations. I didn't
realize there was a spec compliant way to do it:
::=
::= [ AS ]
[]
::=
UNNEST
[ WITH ORDINALITY ]
1) If a TR specifies a CDT,
then let C be the immediately contained in
CDT, let CN be the immediately contained in TR, and
let TEMP be an that is not equivalent to CN nor to any
other contained in TR.
a) Case:
i) If TR specifies a DCL, then
Case:
1) If CDT specifies WITH ORDINALITY, then DCL shall contain 2
s. Let N1 and N2 be respectively the first and
second of those s.
2) Otherwise, DCL shall contain 1 (one) ; let N1 be
that . Let N2 be a that is not
equivalent to N1, CN, TEMP, or any other
contained in TR.
ii) Otherwise, let N1 and N2 be two s that are not
equivalent to one another nor to CN, TEMP, or any other
contained in TR.
b) Let RECQP be:
WITH RECURSIVE TEMP(N1, N2) AS ( SELECT C[1] AS N1, 1 AS N2
FROM (VALUES(1)) AS CN WHERE 0 < CARDINALITY(C)
UNION
SELECT C[N2+1] AS N1, N2+1 AS N2 FROM TEMP
WHERE N2 < CARDINALITY(C))
c) Case:
i) If TR specifies a DCL, then let PDCLP be
( DCL )
ii) Otherwise, let PDCLP be a zero-length string.
d) Case:
i) If CDT specifies WITH ORDINALITY, then let ELDT be:
LATERAL ( RECQP SELECT * FROM TEMP AS CN PDCLP )
ii) Otherwise, let ELDT be:
LATERAL ( RECQP SELECT N1 FROM TEMP AS CN PDCLP )
e) CDT is equivalent to the ELDT.
14) A is not updatable.
Whew! Anyone care to help me interpret that! At it's most basic level, I
think these are valid:
select * from unnest(array['a','b']);
?column?
--
a
b
select * from unnest(array['a','b']) WITH ORDINALITY;
?column? | ?column?
--+--
1| a
2| b
select * from unnest(array['a','b']) as t(f1, f2) WITH ORDINALITY;
f1 | f2
+
1 | a
2 | b
Does this look correct? Again, shouldn't be too hard as most of the work
is already done. I'd just need to do some grammar modifications.
* Some information schema work (doing that now...)
So I take it I need not worry about that?
None of this is very difficult. I'll try to fit it in between now and
Monday evening, but if not it's very doable for 7.5.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Missing array support
Some nice advances to SQL standard array support were made, but there are a few things that don't work yet in the sense of feature S091 "Basic array support". Joe, do you want to take on some of these? They should be pretty easy (for you). * Declaration of multidimensional arrays (see clause 6.1): create table test2 (a int, b text array[5] array[6]); ERROR: syntax error at or near "array" at character 44 * Empty arrays (see clause 6.4): insert into test values (1, array[]); ERROR: syntax error at or near "]" at character 35 * Cardinality function (returns array dimensions, see clause 6.17). * Using an array as a table source using UNNEST, something like: select * from unnest(test.b); (Check the exact spec to be sure; clause 7.6.) * Some information schema work (doing that now...) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
