Allowing SYSDATE to Work
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMP (or CURRENT_TIME, or whatever). I can create a
function called "sysdate" that does the trick, but then it seems I have
to reference the function as "sysdate ()," but I want to be able to get
away with just "sysdate." It seems that CURRENT_TIMESTAMP and their
friends are magic functions that can be referenced without an explicit
empty argument list.
I have much Oracle-specific code that references sysdate, and porting
would be easier if that syntax could work unchanged in Postgres.
Matt Miller wrote:
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMP (or CURRENT_TIME, or whatever). I can create a
function called "sysdate" that does the trick, but then it seems I have
to reference the function as "sysdate ()," but I want to be able to get
away with just "sysdate." It seems that CURRENT_TIMESTAMP and their
friends are magic functions that can be referenced without an explicit
empty argument list.
current_time and the like are hardcoded in the grammar. You'd have to
do the same for sysdate. It's not hard, but then I'd question the
hassle of having to patch all the Postgres installations you're going to
want to run your code on.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 11/17/06 16:31, Alvaro Herrera wrote:
Matt Miller wrote:
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMP (or CURRENT_TIME, or whatever). I can create a
function called "sysdate" that does the trick, but then it seems I have
to reference the function as "sysdate ()," but I want to be able to get
away with just "sysdate." It seems that CURRENT_TIMESTAMP and their
friends are magic functions that can be referenced without an explicit
empty argument list.current_time and the like are hardcoded in the grammar. You'd have to
do the same for sysdate. It's not hard, but then I'd question the
hassle of having to patch all the Postgres installations you're going to
want to run your code on.
Or is he asking that this feature be added to PG?
- --
Ron Johnson, Jr.
Jefferson LA USA
Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
iD8DBQFFXkvcS9HxQb37XmcRAtwHAJ9+GBgAQHI9FoUhjGPmMgImb2cNfQCcC3zZ
2jk+k6ObhXKOZf+HV4j/OY8=
=5bRZ
-----END PGP SIGNATURE-----
Redirecting from -general.
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMPcurrent_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 START STATEMENT
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 START STATEMENT
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 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,
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" <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]http://pgfoundry.org/projects/orafce/
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:
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 17, 2006, at 4:26 PM, Matt Miller wrote:
I'd like SYSDATE to work syntactically and semantically the same as
CURRENT_TIMESTAMP (or CURRENT_TIME, or whatever). I can create a
function called "sysdate" that does the trick, but then it seems I
have
to reference the function as "sysdate ()," but I want to be able to
get
away with just "sysdate." It seems that CURRENT_TIMESTAMP and their
friends are magic functions that can be referenced without an explicit
empty argument list.I have much Oracle-specific code that references sysdate, and porting
would be easier if that syntax could work unchanged in Postgres.
If you've got a lot of Oracle-specific code you might want to
consider using EnterpriseDB.
--
Jim Nasby jim.nasby@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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)