Thread: Naming functions with reserved words

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?

Scott

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