Re: [GENERAL] 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_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 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(at)mattmillersf(dot)fastmail(dot)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(at)commandprompt(dot)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(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

On 11/18/06, Matt Miller <pgsql(at)mattmillersf(dot)fastmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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(dot)nasby(at)enterprisedb(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)