Обсуждение: Re: [GENERAL] Allowing SYSDATE to Work
Redirecting from -general.
> > I'd like SYSDATE to work syntactically and semantically the same as
> > CURRENT_TIMESTAMP
>
> current_time and the like are hardcoded in the grammar. You'd have to
> do the same for sysdate.
Okay, I patched. The patch follows. Please comment. In particular,
I've just copied the CURRENT_TIMESTAMP code block in gram.y. Is this
the best approach? I saw similar code copying between a couple of the
other time-related functions in gram.y. Can't keywords share code
blocks in bison?
I found it interesting that gram.c and parse.h already supported SYSDATE.
I patched only gram.y and keywords.c
> I'd question the hassle of having to patch all the Postgres
> installations you're going to want to run your code on.
Yeah, and I don't expect that they'll be a rush to commit this to head
anytime soon. I'll be happy enough tracking this locally. I think it's
a win for my situation.
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.568
diff -c -r2.568 gram.y
*** gram.y 5 Nov 2006 22:42:09 -0000 2.568
--- gram.y 17 Nov 2006 23:36:35 -0000
***************
*** 419,425 **** SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SOME STABLE
STARTSTATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSID SYSTEM_P
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM
TRUE_P
--- 419,425 ---- SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SOME STABLE
STARTSTATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P SUBSTRING SUPERUSER_P SYMMETRIC
! SYSDATE SYSID SYSTEM_P
TABLE TABLESPACE TEMP TEMPLATE TEMPORARY THEN TIME TIMESTAMP TO TRAILING TRANSACTION TREAT TRIGGER TRIM
TRUE_P
***************
*** 7540,7545 ****
--- 7540,7559 ---- n->location = @1; $$ =
(Node*)n; }
+ | SYSDATE
+ {
+ /*
+ * Translate as "now()", since we have a function that
+ * does exactly what is needed.
+ */
+ FuncCall *n = makeNode(FuncCall);
+ n->funcname = SystemFuncName("now");
+ n->args = NIL;
+ n->agg_star = FALSE;
+ n->agg_distinct = FALSE;
+ n->location = @1;
+ $$ = (Node *)n;
+ } | CURRENT_TIMESTAMP '(' Iconst ')'
{ /*
***************
*** 8893,8898 ****
--- 8907,8913 ---- | SESSION_USER | SOME | SYMMETRIC
+ | SYSDATE | TABLE | THEN |
TO
Index: keywords.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.177
diff -c -r1.177 keywords.c
*** keywords.c 7 Oct 2006 21:51:02 -0000 1.177
--- keywords.c 17 Nov 2006 23:36:35 -0000
***************
*** 324,329 ****
--- 324,330 ---- {"substring", SUBSTRING}, {"superuser", SUPERUSER_P}, {"symmetric", SYMMETRIC},
+ {"sysdate", SYSDATE}, {"sysid", SYSID}, {"system", SYSTEM_P}, {"table", TABLE},
Matt, > > > I'd like SYSDATE to work syntactically and semantically the same as > > > CURRENT_TIMESTAMP Huh? Is SYSDATE part of the standard somewhere? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Matt Miller wrote:
> Can't keywords share code
Code blocks belong to productions. the way to do what you want I think is
like this:
foo: bar_or_baz { code block } ;
bar_or_baz: bar | baz ;
cheers
andrew
"Matt Miller" <pgsql@mattmillersf.fastmail.fm> writes:
> I found it interesting that gram.c and parse.h already supported SYSDATE.
Only after you ran bison ;-). They're derived files.
regards, tom lane
Matt Miller wrote: > Yeah, and I don't expect that they'll be a rush to commit this to head > anytime soon. I'll be happy enough tracking this locally. I think it's > a win for my situation. > Why should we add this Oraclism to PostgreSQL? I doesn't add any new feature. I suggest you to contribute this kind of code to orafce project [1] because it's not standardized. [1] http://pgfoundry.org/projects/orafce/ -- Euler Taveira de Oliveira http://www.timbira.com/
Euler Taveira de Oliveira wrote: > Matt Miller wrote: > > Yeah, and I don't expect that they'll be a rush to commit this to > > head anytime soon. I'll be happy enough tracking this locally. I > > think it's a win for my situation. > > Why should we add this Oraclism to PostgreSQL? I doesn't add any new > feature. Certainly, this feature falls well within the class of completely gratuitous proprietary extensions that we typically reject. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> > Can't keywords share code
>
> the way to do what you want I think is
> like this:
>
> foo: bar_or_baz
> { code block }
> ;
>
> bar_or_baz: bar | baz ;
I'll try that, thanks.
> I suggest you to contribute this kind of code to orafce project [1] Thanks, I'll go play over there for a while.
> > I found it interesting that gram.c and parse.h already supported SYSDATE. > > Only after you ran bison ;-). They're derived files. Well, so much for my conspiracy theory. Thanks for the bison lesson.
> > Why should we add this Oraclism to PostgreSQL? I doesn't add any new > > feature. > > Certainly, this feature falls well within the class of completely > gratuitous proprietary extensions that we typically reject. I now agree completely. My purpose is to migrate Oracle databases to Posgres, and I had thought that Oracle didn't support CURRENT_DATE, CURRENT_TIMESTAMP, and so on. However, I've just learned otherwise. So, I think the proper migration process for a production database would be to first change the Oracle DB to use CURRENT_DATE (or some other standard psuedo column), since that will work properly under both Oracle and Postgres.
Matt, > I now agree completely. My purpose is to migrate Oracle databases to > Posgres, and I had thought that Oracle didn't support CURRENT_DATE, > CURRENT_TIMESTAMP, and so on. However, I've just learned otherwise. So, > I think the proper migration process for a production database would be > to first change the Oracle DB to use CURRENT_DATE (or some other > standard psuedo column), since that will work properly under both Oracle > and Postgres. Yep, or use the Orafce project. We're happy to support compatibility syntax in completely separate add-in projects. Just not in the core code. -- Josh Berkus PostgreSQL @ Sun San Francisco
Josh Berkus wrote: > Matt, > > > I now agree completely. My purpose is to migrate Oracle databases to > > Posgres, and I had thought that Oracle didn't support CURRENT_DATE, > > CURRENT_TIMESTAMP, and so on. However, I've just learned otherwise. So, > > I think the proper migration process for a production database would be > > to first change the Oracle DB to use CURRENT_DATE (or some other > > standard psuedo column), since that will work properly under both Oracle > > and Postgres. > > Yep, or use the Orafce project. We're happy to support compatibility syntax > in completely separate add-in projects. Just not in the core code. How does Orafce allow for grammar extensions like what would be needed for SYSDATE to work? (Note no parens) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 11/19/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > How does Orafce allow for grammar extensions like what would be needed > for SYSDATE to work? (Note no parens) IIRC, it doesn't handle SYSDATE as that would require a change to the grammar. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On 11/18/06, Matt Miller <pgsql@mattmillersf.fastmail.fm> wrote:
Correct approach. BTW, have you given EnterpriseDB a try?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
So,
I think the proper migration process for a production database would be
to first change the Oracle DB to use CURRENT_DATE (or some other
standard psuedo column), since that will work properly under both Oracle
and Postgres.
Correct approach. BTW, have you given EnterpriseDB a try?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
> BTW, have you given EnterpriseDB a try? I looked at it a year or more ago, but I decided it wasn't the type of approach I wanted. I want to focus on getting Oracle DBs migrated to Postgres proper. If I have to hack Postgres to ease the transition I'd rather do that than migrate to another commercial offering. Thanks for the reminder, though, I'll probably take another look, just to see what they're up to.
On Nov 21, 2006, at 10:59 AM, Matt Miller wrote: >> BTW, have you given EnterpriseDB a try? > > I looked at it a year or more ago, but I decided it wasn't the type of > approach I wanted. I want to focus on getting Oracle DBs migrated to > Postgres proper. If I have to hack Postgres to ease the transition > I'd > rather do that than migrate to another commercial offering. > > Thanks for the reminder, though, I'll probably take another look, just > to see what they're up to. Well, EnterpriseDB supports both PostgreSQL and Oracle syntax, so it does ease migration since you don't have to migrate every last piece of code at once. It'd probably be worth your time to download it and give it a try, but then again I'm biased. :) -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)