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

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

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@turnstep.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@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

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

On Sun, Oct 25, 2009 at 2:43 PM, Timothy Madden <terminatorul@gmail.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@commandprompt.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@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

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

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@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)
=======================================================================