Re: Updated CREATE FUNCTION syntax

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e(at)gmx(dot)net]
> Sent: Friday, May 17, 2002 9:37 AM
> To: Joel Burton
> Cc: PostgreSQL Development
> Subject: RE: [HACKERS] Updated CREATE FUNCTION syntax
>
>
> Joel Burton writes:
>
> > Is there any standardized way of handling the single-quotes
> within function
> > definition? Rather than doubling them up (which can make for
> very messy code
> > when your scripting language uses single quotes!), allowing
> another symbol
> > to be used, with that symbol be declared in the CREATE FUNCTION line?
> > Interbase uses a system like this: you can set the delimiter to
> anything you
> > want and use that instead of '.
>
> I think we need something like that. How exactly does Interbase "set" the
> delimiter? Keep in mind that our lexer and parser are static.

Actually, now that I've thought about it for a moment, Interbase doesn't use
a different delimiter, it allows a different end-of-line character.

I've forgotten the exact syntax, but it's something like (Interbase doesn't
allow functions like this, it uses these for stored procedures, but the
basic idea is here):

SELECT * FROM SOMETHING;

SET EOL TO &;

CREATE FUNCTION() RETURNS ... AS
BEGIN;
END;
LANGUAGE plpgsql &

SET EOL TO ;&

SELECT * FROM SOMETHING;

So that it's legal to use ; in the function, since the parser is looking for
a different character to end the complete statement.

I think it would be more straightforward to see something like:

CREATE FUNCTION XXX() RETURNS ... AS #
BEGIN;
END; #
LANGUAGE plpgsql DELIMITER #;

But, with a static lexer/parser, that would be tricky, wouldn't it?

Would it work to allow, rather than free choice of delimiters, to allow
something other than single quote? Probably 95% of functions contain single
quotes (and many scripting languages/development environments treat them
specially), guaranteeing that you'll almost always have to double (or quad-
or oct- or whatever!) your single quotes.

If it's not too offensive, would something like

CREATE FUNCTION XXX() RETURNS AS [[
BEGIN;
END; ]]
LANGUAGE plpgsql DELIMITED BY BRACES;

work? Without the "delimited by braces", the functions would be parsed the
same (single quotes), with this, it would allow [[ and ]]. Someone who used
[[ or ]] in their functions (perhaps as a custom operator or in a text
string) would have to quote these (\[\[ and \]\]), but this would be
__much__ less frequent than having to deal with single quotes. Nothing
should break, since they have to choose to use the 'delimited by braces'
option.

It's not as nice as getting to choose your own delimiter, but it would solve
the problem for most of us just fine and wouldn't seem too hard to
implement.

Functions are in SQL99, aren't they? Does the standard suggest anything
here?

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant