Re: Updated CREATE FUNCTION syntax

As per earlier vague hint, I'm bringing the CREATE FUNCTION syntax in line
with SQL99. Everything is fully backward compatible. Here is the new
synopsis:

CREATE [OR REPLACE] FUNCTION name (args) RETURNS type
option [ option... ] [WITH (...)];

where option is any of these in any order:

AS string [,string]
LANGUAGE name
IMMUTABLE
STABLE
VOLATILE
CALLED ON NULL INPUT -- SQL spelling of not "strict"
RETURNS NULL ON NULL INPUT -- SQL spelling of "strict"
STRICT
[EXTERNAL] SECURITY DEFINER -- SQL spelling of "setuid"
[EXTERNAL] SECURITY INVOKER -- SQL spelling of not "setuid"
IMPLICIT CAST

(The SECURITY options are noops right now, but I'm planning to implement
them next.)

The WITH (...) options are still there, but sort of less encouraged, I
guess.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Peter Eisentraut
> Sent: Thursday, May 16, 2002 1:22 PM
> To: PostgreSQL Development
> Subject: [HACKERS] Updated CREATE FUNCTION syntax
>
>
> As per earlier vague hint, I'm bringing the CREATE FUNCTION syntax in line
> with SQL99. Everything is fully backward compatible. Here is the new
> synopsis:
>
> CREATE [OR REPLACE] FUNCTION name (args) RETURNS type
> option [ option... ] [WITH (...)];
>
> where option is any of these in any order:
>
> AS string [,string]
> LANGUAGE name
> IMMUTABLE
> STABLE
> VOLATILE
> CALLED ON NULL INPUT -- SQL spelling of not "strict"
> RETURNS NULL ON NULL INPUT -- SQL spelling of "strict"
> STRICT
> [EXTERNAL] SECURITY DEFINER -- SQL spelling of "setuid"
> [EXTERNAL] SECURITY INVOKER -- SQL spelling of not "setuid"
> IMPLICIT CAST
>
> (The SECURITY options are noops right now, but I'm planning to implement
> them next.)
>
> The WITH (...) options are still there, but sort of less encouraged, I
> guess.

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 '.

- J.

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

Joel Burton wrote:
>
> > As per earlier vague hint, I'm bringing the CREATE FUNCTION syntax in line
> > with SQL99. Everything is fully backward compatible. Here is the new
> > synopsis:
> >
> > CREATE [OR REPLACE] FUNCTION name (args) RETURNS type
> > option [ option... ] [WITH (...)];
> >
> > where option is any of these in any order:
> >
> > AS string [,string]
> > LANGUAGE name
> > IMMUTABLE
> > STABLE
> > VOLATILE
> > CALLED ON NULL INPUT -- SQL spelling of not "strict"
> > RETURNS NULL ON NULL INPUT -- SQL spelling of "strict"
> > STRICT
> > [EXTERNAL] SECURITY DEFINER -- SQL spelling of "setuid"
> > [EXTERNAL] SECURITY INVOKER -- SQL spelling of not "setuid"
> > IMPLICIT CAST
> >
> > (The SECURITY options are noops right now, but I'm planning to implement
> > them next.)
> >
> > The WITH (...) options are still there, but sort of less encouraged, I
> > guess.
>
> 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 '.

That would be great! The quoting makes pl/pgsql a major pain. It, and
dependency tracking. Of course, with PL/SQL, Oracle doesn't even require
a delimiter:

CREATE PROCEDURE foo(x INTEGER) AS
...
END;

Somehow they manage to get that past their parser, even if the procedure
has "Compilation Errors". It would be sweet...

Mike Mascari
mascarm(at)mascari(dot)com

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.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> -----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

On Fri, 17 May 2002 09:57:39 -0400, "Joel Burton"
<joel(at)joelburton(dot)com> wrote:
>> -----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
>>
>> 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.

Actually it's the end-of-command delimiter, called terminator in
Interbase speech. And it doesn`t have to be a single character, e.g.

SET TERM !! ;

>SELECT * FROM SOMETHING;
>
>SET EOL TO &;
>
>CREATE FUNCTION() RETURNS ... AS
> BEGIN;
> END;
> LANGUAGE plpgsql &

You could even enter any number of commands here, each terminated by
the current terminator:
SELECT * FROM MYTABLE &
DROP TABLE MYTABLE &
SET TERM ! &
SELECT * FROM ANOTHERTABLE !

... before you eventually return to the standard terminator:
SET TERM ; !
SELECT * FROM WHATEVER ;

Servus
Manfred

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> 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.

Seems like the only way to do that in the backend would be to find a way
of slipping the function text past the lexer/parser entirely. While I
can imagine ways of doing that, I think it'd be a *whole* lot cleaner
to fix things on the client side.

How do you feel about a psql hack that provides a "function definition"
mode? More generally it could be a mode to enter random text and have
it be converted to an SQL literal string. Perhaps

psql=> create function foo (int) returns int as
psql-> \beginliteral
psql-LIT> begin
psql-LIT> x := $1;
psql-LIT> ...
psql-LIT> end;
psql-LIT> \endliteral
psql-> language plpgsql;

Essentially, \beginliteral and \endliteral each convert to a quote
mark, and everywhere in between quotes and backslashes get doubled.
We might want to specify that the leading and trailing newlines get
dropped, too, though for function-definition applications that would
not matter.

regards, tom lane

"Joel Burton" <joel(at)joelburton(dot)com> writes:
> Given that 98% of my function defining is done is psql, this would be
> fine for me and solve my frustrations. It wouldn't help people that
> build functions in scripting languages or non-psql environments,
> however, but I don't know how common this is.

True, but I'm thinking that other development environments could provide
equivalent features. (I seem to recall that pgAdmin already does, for
example.)

ISTM the reason we've not addressed this for so long is that no one
could think of a reasonable way to solve it on the backend side.
Maybe we just have to shift our focus.

Another point worth considering is that because psql has its own
smarts about locating query boundaries, it'd be very difficult to
build a function-definition mode without making psql changes, anyway.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> said:

> Seems like the only way to do that in the backend would be to find a way
> of slipping the function text past the lexer/parser entirely. While I
> can imagine ways of doing that, I think it'd be a *whole* lot cleaner
> to fix things on the client side.
>
> How do you feel about a psql hack that provides a "function definition"
> mode? More generally it could be a mode to enter random text and have
> it be converted to an SQL literal string. Perhaps
>
> psql=> create function foo (int) returns int as
> psql-> \beginliteral
> psql-LIT> begin
> psql-LIT> x := $1;
> psql-LIT> ...
> psql-LIT> end;
> psql-LIT> \endliteral
> psql-> language plpgsql;
>
> Essentially, \beginliteral and \endliteral each convert to a quote
> mark, and everywhere in between quotes and backslashes get doubled.
> We might want to specify that the leading and trailing newlines get
> dropped, too, though for function-definition applications that would
> not matter.

Tom --

Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is.

What do others think?

Thanks!
--

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

"Joel Burton" <joel(at)joelburton(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> said:
>> ISTM the reason we've not addressed this for so long is that no one
>> could think of a reasonable way to solve it on the backend side.
>> Maybe we just have to shift our focus.

> Out of curiosity, Tom, why the preference for a solution like this
> rather than allowing for a much-less-common-than-' delimiter for the
> create function syntax? (Such as the "[[" and "]]" I suggested a few
> posts ago?)

That's not a solution for psql, unless you also teach psql about these
delimiters --- else it'll still terminate the query shipped to the
backend too soon. That being the case, you might as well just implement
the delimiters in psql. Seems like [[ and ]] are isomorphic to what I
suggested. I'd have a preference for \[ and \] though.

Note that I did not mean to suggest that "\beginliteral" and
"\endliteral" were actually the names I'd want to use; that was just
for clarity of exposition. Something shorter would be more practical.
It might be reasonable to use \' for example, or if that seems a little
too brief, \lit and \eol (end literal), or \lit ... \til if you remember
Algol68.

> That would have the advantage of being consistent as users switched
> from writing functions in psql to writing function-writing functions,
> to writing functions in other environments, etc.

I would expect script-ish environments to follow psql's lead. For
GUI-ish environments this is probably a complete nonissue; I'd pretty
much expect the function body to pop up in a separate editing window
to start with, so that the user really has no need to think about
separating the function body from the rest of the CREATE FUNCTION
command.

In any case I do not think it's likely that client-side programming
environments would be able to take advantage of such a feature without
rework, just as psql couldn't. Any backend-side solution we might put
in would really amount to a protocol change, whether you wanted to call
it one or not. So the notion of "fix it once in the backend, not once
per client" seems illusory to me for this particular problem.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> said:

> "Joel Burton" <joel(at)joelburton(dot)com> writes:
> > Given that 98% of my function defining is done is psql, this would be
> > fine for me and solve my frustrations. It wouldn't help people that
> > build functions in scripting languages or non-psql environments,
> > however, but I don't know how common this is.
>
> True, but I'm thinking that other development environments could provide
> equivalent features. (I seem to recall that pgAdmin already does, for
> example.)
>
> ISTM the reason we've not addressed this for so long is that no one
> could think of a reasonable way to solve it on the backend side.
> Maybe we just have to shift our focus.

Out of curiosity, Tom, why the preference for a solution like this rather than allowing for a much-less-common-than-' delimiter for the create function syntax? (Such as the "[[" and "]]" I suggested a few posts ago?) This would seem like something that wouldn't seem too difficult to do, and would work in all environments.

That would have the advantage of being consistent as users switched from writing functions in psql to writing function-writing functions, to writing functions in other environments, etc.

Thanks,

- J.

--

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