Naming functions with reserved words

On Wed, Jun 17, 2009 at 01:46:13AM -0700, Scott Bailey wrote:

I noticed in the temporal project they used reserved words for their
functions (union, intersect, etc)

But when I try to create a function like that I get an error and I have
to quote it both when creating the function and when calling it. The
only difference I can see is they their functions are written in C and
mine are in SQL. But that doesn't make sense why you could do it in one
language and not in another.

What am I missing? And if it can be done, is there a strong preference
in the community about whether it should be the reserved word or follow
PostGIS's tack and prepend something to all of the functions like
ts_union and ts_intersect?

As you've observed above, it's a really, really bad idea to name any
database object with a reserved word. Descriptive names are better.

That said, you *can* do it by double-quoting each.

SELECT "SELECT" FROM "FROM" WHERE "WHERE" = "=";

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Scott Bailey <artacus@comcast.net> writes:

I noticed in the temporal project they used reserved words for their
functions (union, intersect, etc)

Uh, what project is that exactly, and was it even working within
Postgres?

But when I try to create a function like that I get an error and I have
to quote it both when creating the function and when calling it. The
only difference I can see is they their functions are written in C and
mine are in SQL. But that doesn't make sense why you could do it in one
language and not in another.

You can't do it in any language in Postgres. UNION and INTERSECT are
fully reserved keywords, and there just isn't any sane way to avoid
making them so given the restrictions of a LALR(1) parser.

regards, tom lane

Uh, what project is that exactly, and was it even working within Postgres?

The project is http://pgfoundry.org/projects/temporal/

But it looks like I'm just stupid or confused (or confused and stupid). I'm working on porting temporal extensions I wrote originally for Oracle to Postgres. When I was half way there, I discovered the temporal project. So I was working on compatibility between the two. All of my functions were prefixed and none of theirs were. So I guess I mistakenly read their functions as intersect() and union(). But going back to the source code, I see that they prefixed those two functions period_intersect() and period_union(). I appologize for being a doofus.

Scott