Обсуждение: Can the string literal syntax for function definitions please be dropped ?

Timothy Madden <terminatorul@gmail.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 Sunday 25 October 2009 9:17:04 am Timothy Madden wrote:
> On Sat, Oct 24, 2009 at 5:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Timothy Madden <terminatorul@gmail.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@comcast.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@turnstep.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 2:43 PM, Timothy Madden <terminatorul@gmail.com> wrote:
>
>
> On Sun, Oct 25, 2009 at 8:49 PM, Adrian Klaver <aklaver@comcast.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@sss.pgh.pa.us> wrote:
>> > > Timothy Madden <terminatorul@gmail.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@gmail.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

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 Sunday 25 October 2009 3:20:51 pm Timothy Madden wrote:
> On Mon, Oct 26, 2009 at 12:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Timothy Madden <terminatorul@gmail.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@comcast.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@commandprompt.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@comcast.net

Timothy Madden escribió:
> On Mon, Oct 26, 2009 at 12:42 AM, Alvaro Herrera <alvherre@commandprompt.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@commandprompt.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.w.noon@ntlworld.com (David W Noon)
=======================================================================

2009/10/26 Tom Lane <tgl@sss.pgh.pa.us>:
> Alvaro Herrera <alvherre@commandprompt.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>