Re: Can the string literal syntax for function definitions please be dropped ?

Hello

Can the string literal syntax for the function body in a CREATE FUNCTION
statement please,
please be dropped ?

http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html

It is so annoying and not ISO/ANSI and not compatible with other DBMSs...

I have written a mail about SQL conformance on a list like this once before,
and I promptly got
a detailed negative response back!

Now I can understand that the standard may be unrealistically demanding for
someone actually
trying to build and implement a DBMS (although I am yet to read or hear this
actually), but for
features already present in PostgreSQL (binary objects, SQL functions), some
effort to also
make the syntax conforming to the standards should be worthy ...

Thank you,
Timothy Madden

Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> Can the string literal syntax for the function body in a CREATE FUNCTION
> statement please,
> please be dropped ?

No. Since the function's language might be anything, there's no way to
identify the end of the function body otherwise.

regards, tom lane

On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> > Can the string literal syntax for the function body in a CREATE FUNCTION
> > statement please,
> > please be dropped ?
>
> No. Since the function's language might be anything, there's no way to
> identify the end of the function body otherwise.
>

There is a SQL standard for this, and other DBMS look like they found a way
...

How come it can not be done ?

On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote:
> On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> > > Can the string literal syntax for the function body in a CREATE
> > > FUNCTION statement please,
> > > please be dropped ?
> >
> > No. Since the function's language might be anything, there's no way to
> > identify the end of the function body otherwise.
>
> There is a SQL standard for this, and other DBMS look like they found a way
> ...
>
> How come it can not be done ?

I am trying to determine the problem you are trying to solve. Even if the string
literal syntax goes away functions created for Postgres make use of Postgres
specific syntax and extensions. So there is going to be a translation step
involved irregardless of the string issue. So just out of curiosty what problem
does the string syntax cause?

--
Adrian Klaver
aklaver(at)comcast(dot)net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> There is a SQL standard for this, and other DBMS look like they found a way
>
> How come it can not be done ?

It *can* be done, but it won't be done. At least not by default. You might
get better traction if you perhaps argue for a flag to pg_dump to
exhibit the behavior you want. It has a small chance of being accepted,
but a much greater chance than changing the default behavior.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200910251638
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkrkt2oACgkQvJuQZxSWSshyYwCcCRozshAfS22KtJJqoLmJdOsx
X9IAmwYedkEyw819R6P0FZXVgskefaR4
=4oy5
-----END PGP SIGNATURE-----

On Sun, Oct 25, 2009 at 8:49 PM, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:

> On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote:
> > On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> > > > Can the string literal syntax for the function body in a CREATE
> > > > FUNCTION statement please,
> > > > please be dropped ?
> > >
> > > No. Since the function's language might be anything, there's no way to
> > > identify the end of the function body otherwise.
> >
> > There is a SQL standard for this, and other DBMS look like they found a
> way
> > ...
> >
> > How come it can not be done ?
>
> I am trying to determine the problem you are trying to solve. Even if the
> string
> literal syntax goes away functions created for Postgres make use of
> Postgres
> specific syntax and extensions. So there is going to be a translation step
> involved irregardless of the string issue. So just out of curiosty what
> problem
> does the string syntax cause?
>

Just like when I write C++ applications I use standards-conforming C++, when
I write SQL
applications I would like to use standard-conforming SQL.

I would normally write standard-conforming C++ code even when porting is not
actually a
stated requirement in my project, just because portable code is the right
code. Should my
project need some specific function or library, at least the
platform-specific code should
be grouped in a separate module/directory. I think there are many, many
other developers
that agree with me in this regard. After all PostgreSql is open-source and
portable.

For SQL, at the current conformance and compatibility level among DBMS
providers in use
today, one could rightly say there is no such thing as conforming or
portable SQL application
in real-world. However my intent is still the same, to write conforming
(SQL) code. Or at least
try, as much as it is possible. One day the world of DBMS providers will
eventually get better
in this regard.

So I would expect any open source, state-of-the-art DBMS system to have
standards-compliance as one of its goals (if not already one of its
features). I am
happy to say PosgreSQL rates pretty well, if not the best, when compared
with
other (commercial and non-commercial) DBMSs by standards-conformance.
Maybe this is also why I have these high expectations ...

Thank you,
Timothy Madden

On Sun, Oct 25, 2009 at 10:38 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com>wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
> > There is a SQL standard for this, and other DBMS look like they found a
> way
> >
> > How come it can not be done ?
>
> It *can* be done, but it won't be done. At least not by default. You might
> get better traction if you perhaps argue for a flag to pg_dump to
> exhibit the behavior you want. It has a small chance of being accepted,
> but a much greater chance than changing the default behavior.
>
> What I want is compatible with existing code and the current default
behavior.
Just look for a LANGUAGE SQL declaration in the function header (before the
body).

If found expect the in-place definition of the function body to follow.
If not found expect a string literal that holds the function body to follow,
with the
LANGUAGE declaration after (default behavior).

I am interested in the functions I write by hand as an application
developer; pg_dump
may dump the functions any way it finds suitable (although I would still
prefer the
conforming form).

Thank you,
Timothy Madden

On Sun, Oct 25, 2009 at 2:43 PM, Timothy Madden <terminatorul(at)gmail(dot)com> wrote:
>
>
> On Sun, Oct 25, 2009 at 8:49 PM, Adrian Klaver <aklaver(at)comcast(dot)net> wrote:
>>
>> On Sunday 25 October 2009 9:17:04 am Timothy Madden wrote:
>> > On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > > Timothy Madden <terminatorul(at)gmail(dot)com> writes:
>> > > > Can the string literal syntax for the function body in a CREATE
>> > > > FUNCTION statement please,
>> > > > please be dropped ?
>> > >
>> > > No.  Since the function's language might be anything, there's no way
>> > > to
>> > > identify the end of the function body otherwise.
>> >
>> > There is a SQL standard for this, and other DBMS look like they found a
>> > way
>> > ...
>> >
>> > How come it can not be done ?
>>
>> I am trying to determine the problem you are trying to solve. Even if the
>> string
>> literal syntax goes away functions created for Postgres make use of
>> Postgres
>> specific syntax and extensions. So there is going to be a translation step
>> involved irregardless of the string issue. So just out of curiosty what
>> problem
>> does the string syntax cause?
>
> Just like when I write C++ applications I use standards-conforming C++, when
> I write SQL
> applications I would like to use standard-conforming SQL.

But as soon as the rubber hits the road, not two C or C++ compilers
are really 100% compatible as are no two SQL implementations.

Simply wanting things to be the same across all DBs seems kind of
naive as a reason to change pg's behaviour.

> I would normally write standard-conforming C++ code even when porting is not
> actually a
> stated requirement in my project, just because portable code is the right
> code.

So your argument is more philosophical than logical? Not that
philosophy doesn't have its place, but a logical reason would carry
far more weight here.

> Should my
> project need some specific function or library, at least the
> platform-specific code should
> be grouped in a separate module/directory. I think there are many, many
> other developers
> that agree with me in this regard. After all PostgreSql is open-source and
> portable.

I haven't seen them on this list really. I'm entirely against it, but
if it breaks stuff I've already got that works and works well then I
have no real need for it, especially if it's ONLY for the purpose of
being SQL standard compliant and not for meeting some real world need.

> For SQL, at the current conformance and compatibility level among DBMS
> providers in use
> today, one could rightly say there is no such thing as conforming or
> portable SQL application
> in real-world.

A large part of the reason for this is that parts of the SQL spec are
just plain strange and weird and implementing them gains us little or
nothing. The SQL spec is far more open to interpretation than the C
or C++ specs, and has changed a LOT more in the last ten years than
those as well. It's a moving target in many ways, and while many
parts of it make perfect sense to be implemented as written, a
noticeable minority of it doesn't warrant implementation / changes to
comply.

> However my intent is still the same, to write conforming
> (SQL) code. Or at least
> try, as much as it is possible. One day the world of DBMS providers will
> eventually get better
> in this regard.

They've been doing that very thing for the last 20 or so years. But I
think that differences in implementation and philosophy will always
result in some divergence of SQL interface.

Timothy Madden escribió:

> Just like when I write C++ applications I use standards-conforming
> C++, when I write SQL applications I would like to use
> standard-conforming SQL.

Sadly, we don't have standards-conformant SQL/PSM. Right now, we have a
lot of different languages for functions, none of them mandated by SQL,
and there is no reason to create a syntax exception for any of them.

I am sure that when we get SQL/PSM support, the interest in getting
standards-conformant procedure creation statements is going to get a lot
higher.

PL/pgSQL is not SQL/PSM.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> What I want is compatible with existing code and the current default
> behavior. Just look for a LANGUAGE SQL declaration in the function
> header (before the body).

> If found expect the in-place definition of the function body to follow.
> If not found expect a string literal that holds the function body to follow,
> with the LANGUAGE declaration after (default behavior).

This proposal is unfortunately complete nonsense, because it fails to
address the question of how you figure out where the function body *ends*.
We have to have a simple and not-language-specific rule for that. Even
if the backend could be made smart enough to handle a variety of cases,
we could hardly expect client-side code (like psql) to track all the
cases. And psql does need to understand where the CREATE FUNCTION
command ends, so that it can tell when to ship the command off to the
backend.

regards, tom lane

On Sun, Oct 25, 2009 at 11:33 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Sun, Oct 25, 2009 at 2:43 PM, Timothy Madden <terminatorul(at)gmail(dot)com>
> wrote:
> > Just like when I write C++ applications I use standards-conforming C++,
> when
> > I write SQL
> > applications I would like to use standard-conforming SQL.
>
> But as soon as the rubber hits the road, not two C or C++ compilers
> are really 100% compatible as are no two SQL implementations.
>
> > For SQL, at the current conformance and compatibility level among DBMS
> > providers in use
> > today, one could rightly say there is no such thing as conforming or
> > portable SQL application
> > in real-world.
>
> A large part of the reason for this is that parts of the SQL spec are
> just plain strange and weird and implementing them gains us little or
> nothing. The SQL spec is far more open to interpretation than the C
> or C++ specs, and has changed a LOT more in the last ten years than
> those as well. It's a moving target in many ways, and while many
> parts of it make perfect sense to be implemented as written, a
> noticeable minority of it doesn't warrant implementation / changes to
> comply.
>
>
I am only talking about conforming syntax for features PostgreSql already
has.
That could gain something, right ?

And there are C/C++ applications that compile on many systems, like
Postgres is, despite the fact that no two C++ compilers are 100% compatible.

Thank you,
Timothy Madden

On Sun, Oct 25, 2009 at 11:40 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> wrote:

> Timothy Madden escribió:
>
> > Just like when I write C++ applications I use standards-conforming
> > C++, when I write SQL applications I would like to use
> > standard-conforming SQL.
>
> Sadly, we don't have standards-conformant SQL/PSM. Right now, we have a
> lot of different languages for functions, none of them mandated by SQL,
> and there is no reason to create a syntax exception for any of them.
>
> I am sure that when we get SQL/PSM support, the interest in getting
> standards-conformant procedure creation statements is going to get a lot
> higher.
>
> PL/pgSQL is not SQL/PSM.
>
>
Anyway Posgres offers a CREATE FUNCTION statement that resembles or should
resemble that in the standard, and that is what I am talking about. I just
want the
Postgres version of the statement to look more like the standard one.

Would you detail the differences you talk about that you see here ?

Thank you,
Timothy Madden

On Mon, Oct 26, 2009 at 12:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> > What I want is compatible with existing code and the current default
> > behavior. Just look for a LANGUAGE SQL declaration in the function
> > header (before the body).
>
> > If found expect the in-place definition of the function body to follow.
> > If not found expect a string literal that holds the function body to
> follow,
> > with the LANGUAGE declaration after (default behavior).
>
> This proposal is unfortunately complete nonsense, because it fails to
> address the question of how you figure out where the function body *ends*.
> We have to have a simple and not-language-specific rule for that. Even
> if the backend could be made smart enough to handle a variety of cases,
> we could hardly expect client-side code (like psql) to track all the
> cases. And psql does need to understand where the CREATE FUNCTION
> command ends, so that it can tell when to ship the command off to the
> backend.
>

By the standard the routine body is a <SQL procedure statement> and the
question of how to figure out where the function body ends should be
answered
as such.

I am talking about two cases, the one psql already handles, and the one
where
the body is (and ends as) a <SQL procedure statement>, which statement
again psql should already understand and which is signaled by the
LANGUAGE SQL declaration in the function header.

Thank you,
Timothy Madden

Timothy Madden escribió:

> Anyway Posgres offers a CREATE FUNCTION statement that resembles or should
> resemble that in the standard, and that is what I am talking about.

"Should" being the operative word in that sentence. If you want to
submit a patch to move us closer towards the SQL/PSM goal, I'm sure it
will be welcome.

> I just want the Postgres version of the statement to look more like
> the standard one.

Sure. If we weren't all pointing in that general direction, we would
probably have CONNECT BY instead of WITH RECURSIVE.

> Would you detail the differences you talk about that you see here ?

I'm not that familiar with SQL/PSM, sorry. Pavel Stehule is da man.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> wrote:

> Timothy Madden escribió:
>
> > Anyway Posgres offers a CREATE FUNCTION statement that resembles or
> should
> > resemble that in the standard, and that is what I am talking about.
>
> "Should" being the operative word in that sentence. If you want to
> submit a patch to move us closer towards the SQL/PSM goal, I'm sure it
> will be welcome.
>

You know that takes quite some effort to invest. How are you sure a patch
for this will be
welcome when people here mostly disagree with me ?

>
>
> I just want the Postgres version of the statement to look more like
> > the standard one.
>
> Sure. If we weren't all pointing in that general direction, we would
> probably have CONNECT BY instead of WITH RECURSIVE.
>

I don't understand, what CONNECT BY or WITH RECURSIVE ?

Thank you,
Timothy Madden

On Sunday 25 October 2009 3:20:51 pm Timothy Madden wrote:
> On Mon, Oct 26, 2009 at 12:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Timothy Madden <terminatorul(at)gmail(dot)com> writes:
> > > What I want is compatible with existing code and the current default
> > > behavior. Just look for a LANGUAGE SQL declaration in the function
> > > header (before the body).
> > >
> > > If found expect the in-place definition of the function body to follow.
> > > If not found expect a string literal that holds the function body to
> >
> > follow,
> >
> > > with the LANGUAGE declaration after (default behavior).
> >
> > This proposal is unfortunately complete nonsense, because it fails to
> > address the question of how you figure out where the function body
> > *ends*. We have to have a simple and not-language-specific rule for that.
> > Even if the backend could be made smart enough to handle a variety of
> > cases, we could hardly expect client-side code (like psql) to track all
> > the cases. And psql does need to understand where the CREATE FUNCTION
> > command ends, so that it can tell when to ship the command off to the
> > backend.
>
> By the standard the routine body is a <SQL procedure statement> and the
> question of how to figure out where the function body ends should be
> answered
> as such.
>
> I am talking about two cases, the one psql already handles, and the one
> where
> the body is (and ends as) a <SQL procedure statement>, which statement
> again psql should already understand and which is signaled by the
> LANGUAGE SQL declaration in the function header.
>
> Thank you,
> Timothy Madden

You mean something like this ?:
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

I am not seeing that as an improvement.

--
Adrian Klaver
aklaver(at)comcast(dot)net

On Sunday 25 October 2009 4:06:33 pm Timothy Madden wrote:
> On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com
>
> > wrote:
> >
> > Timothy Madden escribió:
> > > Anyway Posgres offers a CREATE FUNCTION statement that resembles or
> >
> > should
> >
> > > resemble that in the standard, and that is what I am talking about.
> >
> > "Should" being the operative word in that sentence. If you want to
> > submit a patch to move us closer towards the SQL/PSM goal, I'm sure it
> > will be welcome.
>
> You know that takes quite some effort to invest. How are you sure a patch
> for this will be
> welcome when people here mostly disagree with me ?

Since we are getting philosophical, I did not realize agreement was necessary to
get things done:) I do agree with the effort assessment. To justify the effort
though, it would seem you only need to convince yourself of the merits.
Basically "A Field of Dreams" scenario. You build it and see who shows up.

>
>
>
> Thank you,
> Timothy Madden

--
Adrian Klaver
aklaver(at)comcast(dot)net

Timothy Madden escribió:
> On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)com
> > wrote:
>
> > Timothy Madden escribió:
> >
> > > Anyway Posgres offers a CREATE FUNCTION statement that resembles
> > > or should resemble that in the standard, and that is what I am
> > > talking about.
> >
> > "Should" being the operative word in that sentence. If you want to
> > submit a patch to move us closer towards the SQL/PSM goal, I'm sure it
> > will be welcome.
>
> You know that takes quite some effort to invest. How are you sure a patch
> for this will be welcome when people here mostly disagree with me ?

Because you're wielding the wrong argument :-)

> > I just want the Postgres version of the statement to look more like
> > the standard one.
> >
> > Sure. If we weren't all pointing in that general direction, we would
> > probably have CONNECT BY instead of WITH RECURSIVE.
>
> I don't understand, what CONNECT BY or WITH RECURSIVE ?

CONNECT BY is Oracle's way of implementing recursive queries. We had a
patch for that for years, but it was rejected over and over on various
grounds, one of which was that it was not the standard's spelling of the
feature. We only got recursive queries when somebody was willing to
bite the bullet and write it in WITH RECURSIVE form.

My point here was: we definitely support the standard. We don't do the
string literal bit for functions just because we like to be different.
We do it because our extensibility features require it. Of course,
SQL/PSM is a different beast than all the rest of the PLs, because it is
standard, so I am sure that we will want to implement the standard
syntax (no string literal) when we have SQL/PSM. But implementing no-
string-literals before we get full SQL/PSM support would be pointless,
because there are so many other things that are not standard in that
area. Simply removing the quotes (which is what you are requesting)
would not take our standards compliance much further.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> SQL/PSM is a different beast than all the rest of the PLs, because it is
> standard, so I am sure that we will want to implement the standard
> syntax (no string literal) when we have SQL/PSM. But implementing no-
> string-literals before we get full SQL/PSM support would be pointless,
> because there are so many other things that are not standard in that
> area. Simply removing the quotes (which is what you are requesting)
> would not take our standards compliance much further.

[ after re-reading the spec a little bit ... ]

One interesting point here is that I don't think the spec suggests
that SQL/PSM can be written in-line in the CREATE FUNCTION statement
at all. What I see (at least in SQL99) is

<schema function> ::=
CREATE <SQL-invoked function>

<SQL-invoked function> ::=
{ <function specification> | <method specification designator> }

<routine body>

<function specification> ::=
FUNCTION <schema qualified routine name>
<SQL parameter declaration list>
<returns clause>
<routine characteristics>
[ <dispatch clause> ]

<routine body> ::=
<SQL routine body>
| <external body reference>

<SQL routine body> ::= <SQL procedure statement>

and <SQL procedure statement> seems to allow one (count em, one) SQL DDL
or DML statement. So per spec, essentially every interesting case
requires an <external body reference>. We could possibly support the
single-SQL-statement case without any quotes --- at least, it doesn't
obviously break clients to do that; handling it inside the backend still
seems nontrivial. But it's not clear to me that that case is useful
enough to be worth the trouble.

regards, tom lane

On Sun, 25 Oct 2009 20:17:22 -0400, Tom Lane wrote about Re: [GENERAL]
Can the string literal syntax for function definitions please be
dropped ?:

[snip]
> <routine body> ::=
> <SQL routine body>
> | <external body reference>
>
> <SQL routine body> ::= <SQL procedure statement>
>
>and <SQL procedure statement> seems to allow one (count em, one) SQL
>DDL or DML statement. So per spec, essentially every interesting case
>requires an <external body reference>.

This explains the evolution of DB2's support for user-defined
functions: initially they (UDFs) had to be written in some host language
(COBOL, PL/I, C, etc.), and linked in by external reference; later, a
single SQL statement(*) was permitted instead; finally, a compound SQL
statement was permitted, with BEGIN and END bracketing an arbitrary
collection of other SQL statements.

(*) Since all UDFs must return a value, the single statement was
almost invariably a RETURN with some query providing the value.
--
Regards,

Dave [RLU #314465]
=======================================================================
david(dot)w(dot)noon(at)ntlworld(dot)com (David W Noon)
=======================================================================

2009/10/26 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> SQL/PSM is a different beast than all the rest of the PLs, because it is
>> standard, so I am sure that we will want to implement the standard
>> syntax (no string literal) when we have SQL/PSM.  But implementing no-
>> string-literals before we get full SQL/PSM support would be pointless,
>> because there are so many other things that are not standard in that
>> area.  Simply removing the quotes (which is what you are requesting)
>> would not take our standards compliance much further.
>
> [ after re-reading the spec a little bit ... ]
>
> One interesting point here is that I don't think the spec suggests
> that SQL/PSM can be written in-line in the CREATE FUNCTION statement
> at all.  What I see (at least in SQL99) is
>
>         <schema function> ::=
>              CREATE <SQL-invoked function>
>
>         <SQL-invoked function> ::=
>              { <function specification> | <method specification designator> }
>
>                <routine body>
>
>         <function specification> ::=
>              FUNCTION <schema qualified routine name>
>                <SQL parameter declaration list>
>                <returns clause>
>                <routine characteristics>
>                [ <dispatch clause> ]
>
>         <routine body> ::=
>                <SQL routine body>
>              | <external body reference>
>
>         <SQL routine body> ::= <SQL procedure statement>
>
> and <SQL procedure statement> seems to allow one (count em, one) SQL DDL
> or DML statement.  So per spec, essentially every interesting case
> requires an <external body reference>.  We could possibly support the
> single-SQL-statement case without any quotes --- at least, it doesn't
> obviously break clients to do that; handling it inside the backend still
> seems nontrivial.  But it's not clear to me that that case is useful
> enough to be worth the trouble.
>

it is not correct. When you would to use more statements, then you can
to use BEGIN ... END;

so CREATE FUNCTION foo(...)
RETURNS int AS
BEGIN
DECLARE x int;
SET x = 10;
RETURN x;
END;

is correct.

CREATE FUNCTION foo(...)
RETURNS int AS
RETURN x;

is correct too. The block is optional in SQL/PSM.

http://www.postgres.cz/index.php/SQL/PSM_Manual

What I known, other DBMS have to solve this complications too. Next
possibility is using some special symbol for ending parser like
DELIMITER.

Actually we have a independent parsers for SQL and PL languages. It
has some adventages:
a) we could to support more PL languages
b) PL parser are relative small, SQL parser is relative clean

If we integrate some language to main parser, then we have to able to
block some parts of parser dynamicky - because some functionality
should be invisible from some PL - SQL/PSM FOR statement has different
syntax than PL/pgSQL FOR statement. I thing, so this is possible - but
it uglify parser. If somebody found way how to do extendable parser in
bison, then we could to go far, but actually I don't advantages change
anything on current syntax.

Regards
Pavel Stehule

>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mon, Oct 26, 2009 at 6:37 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 2009/10/26 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >> SQL/PSM is a different beast than all the rest of the PLs, because it is
> >> standard, so I am sure that we will want to implement the standard
> >> syntax (no string literal) when we have SQL/PSM. But implementing no-
> >> string-literals before we get full SQL/PSM support would be pointless,
> >> because there are so many other things that are not standard in that
> >> area. Simply removing the quotes (which is what you are requesting)
> >> would not take our standards compliance much further.
> >
> > [ after re-reading the spec a little bit ... ]
> >
> > One interesting point here is that I don't think the spec suggests
> > that SQL/PSM can be written in-line in the CREATE FUNCTION statement
> > at all. What I see (at least in SQL99) is
> >
> > <schema function> ::=
> > CREATE <SQL-invoked function>
> >
> > <SQL-invoked function> ::=
> > { <function specification> | <method specification
> designator> }
> >
> > <routine body>
> >
> > <function specification> ::=
> > FUNCTION <schema qualified routine name>
> > <SQL parameter declaration list>
> > <returns clause>
> > <routine characteristics>
> > [ <dispatch clause> ]
> >
> > <routine body> ::=
> > <SQL routine body>
> > | <external body reference>
> >
> > <SQL routine body> ::= <SQL procedure statement>
> >
> > and <SQL procedure statement> seems to allow one (count em, one) SQL DDL
> > or DML statement. So per spec, essentially every interesting case
> > requires an <external body reference>. We could possibly support the
> > single-SQL-statement case without any quotes --- at least, it doesn't
> > obviously break clients to do that; handling it inside the backend still
> > seems nontrivial. But it's not clear to me that that case is useful
> > enough to be worth the trouble.
> >
>
> it is not correct. When you would to use more statements, then you can
> to use BEGIN ... END;
>
> so CREATE FUNCTION foo(...)
> RETURNS int AS
> BEGIN
> DECLARE x int;
> SET x = 10;
> RETURN x;
> END;
>
> is correct.
>
> CREATE FUNCTION foo(...)
> RETURNS int AS
> RETURN x;
>
> is correct too. The block is optional in SQL/PSM.
>
> http://www.postgres.cz/index.php/SQL/PSM_Manual
>
> What I known, other DBMS have to solve this complications too. Next
> possibility is using some special symbol for ending parser like
> DELIMITER.
>
> Actually we have a independent parsers for SQL and PL languages. It
> has some adventages:
> a) we could to support more PL languages
> b) PL parser are relative small, SQL parser is relative clean
>
> If we integrate some language to main parser, then we have to able to
> block some parts of parser dynamicky - because some functionality
> should be invisible from some PL - SQL/PSM FOR statement has different
> syntax than PL/pgSQL FOR statement. I thing, so this is possible - but
> it uglify parser. If somebody found way how to do extendable parser in
> bison, then we could to go far, but actually I don't advantages change
> anything on current syntax.
>
> Regards
> Pavel Stehule
>
>

Thank you all for your considerate replies.

Why am I wielding the wrong argument ? My argument is standards conformance.
Because there are many other non-standard expressions in the current syntax
?
So my issue is just one more on the list ...

Full SQL/PSM support seems pretty far; why is it needed in order to also
consider
the non string-literal function definitions for language SQL functions ? I
rather see
removing quotes as the first step towards SQL/PSM, then the last ...

We are getting philosophical, but agreement is still necessary for a patch
from what
I know, especially that the effort to understand the project is
comprehensive. That
is why I was concerned about agreement.

The DELIMITER artefact is also misplaced in my opinion; what is the use for
it ?
The BEGIN ... END syntax is pretty clear ...
DELIMITER is just to keep the client-side parsing / input simple ?

For other languages the string literal syntax is ok, my issue only concerns
LANGUAGE SQL functions ....

Thank you,
Timothy Madden