Thread: BUG #5136: Please drop the string literal syntax for CREATE FUNCTION ...

The following bug has been logged online:

Bug reference:      5136
Logged by:          Timothy Madden
Email address:      terminatorul@gmail.com
PostgreSQL version: 8.4.1
Operating system:   Ubuntu
Description:        Please drop the string literal syntax for CREATE
FUNCTION ...
Details:

Can the string literal syntax for CREATE FUNCTION please, please be dropped
... ?

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

It is not ANSI/ISO and so annoying for compatibility.

Thank you,
Timothy Madden

On lör, 2009-10-24 at 14:01 +0000, Timothy Madden wrote:
> Can the string literal syntax for CREATE FUNCTION please, please be dropped
> ... ?
>
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-structure.html
>
> It is not ANSI/ISO and so annoying for compatibility.

Whatever is inside the string literal is also not ANSI/ISO, so why would
it make a difference?

On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

> On l=F6r, 2009-10-24 at 14:01 +0000, Timothy Madden wrote:
> > Can the string literal syntax for CREATE FUNCTION please, please be
> dropped
> > ... ?
>
[...]

> > It is not ANSI/ISO and so annoying for compatibility.
>
> Whatever is inside the string literal is also not ANSI/ISO, so why would
> it make a difference?
>
> At least the function declaration syntax would be right. I think it would
be an important difference; this string literal idea is so strange and
unnatural ...

And why the function body would not be standard ? Do you mean it can never
be for some reason ? How about a simple function ?

Thank you,
Timothy Madden

2009/10/25 Timothy Madden <terminatorul@gmail.com>:
>
>
> On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut <peter_e@gmx.net> wrot=
e:
>>
>> On l=C3=B6r, 2009-10-24 at 14:01 +0000, Timothy Madden wrote:
>> > Can the string literal syntax for CREATE FUNCTION please, please be
>> > dropped
>> > ... ?
>
> [...]
>>
>> > It is not ANSI/ISO and so annoying for compatibility.
>>
>> Whatever is inside the string literal is also not ANSI/ISO, so why would
>> it make a difference?
>>
> At least the function declaration syntax would be right. I think it would=
 be
> an important difference; this string literal idea is so strange and
> unnatural ...
>
> And why the function body would not be standard ?

Because standard knows only SQL/PSM language for SQL procedures.
Others databases support only one language for sql procedures (PL/SQL,
T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql
procedures - and I am sure, so standard doesnt calculate with
multilangual environments.

Regards
Pavel Stehule

Do you mean it can never
> be for some reason ? How about a simple function ?
>
> Thank you,
> Timothy Madden
>

On Sun, Oct 25, 2009 at 6:17 PM, Pavel Stehule <pavel.stehule@gmail.com>wro=
te:

> 2009/10/25 Timothy Madden <terminatorul@gmail.com>:
> >
> >
> > On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut <peter_e@gmx.net>
> wrote:
> >>
> >> On l=F6r, 2009-10-24 at 14:01 +0000, Timothy Madden wrote:
> >> > Can the string literal syntax for CREATE FUNCTION please, please be
> >> > dropped
> >> > ... ?
> >
> > [...]
> >>
> >> > It is not ANSI/ISO and so annoying for compatibility.
> >>
> >> Whatever is inside the string literal is also not ANSI/ISO, so why wou=
ld
> >> it make a difference?
> >>
> > At least the function declaration syntax would be right. I think it wou=
ld
> be
> > an important difference; this string literal idea is so strange and
> > unnatural ...
> >
> > And why the function body would not be standard ?
>
> Because standard knows only SQL/PSM language for SQL procedures.
> Others databases support only one language for sql procedures (PL/SQL,
> T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql
> procedures - and I am sure, so standard doesnt calculate with
> multilangual environments.
>


Ok I get it. So Posgres also offers perl and python in addition to SQL.
But at least for SQL, which is included and defined in the standard, could
the syntax be made conforming ? As an alternative to the one already
used (to offer the additional langauges) ?

So that the additional languages are provided as before, and the
standard-definded one still follows the standard ?


Thank you,
Timothy Madden


P.S. :
As a note, actually the standard provides for other languages, with the
procedures only referenced as external (pre-compiled) instead of
defined/scripted in line.

If anyone is interested you have below the CREATE FUNCTION and
CREATE PROCEDURE syntax definition from SQL-3
 (SQL-99, Part 2 - Foundation).

<SQL-invoked procedure> ::=3D
         PROCEDURE <schema qualified routine name>  <SQL parameter
declaration list>
        <routine characteristics>
        <routine body>

<SQL-invoked function> ::=3D
        { <function specification> | <method specification designator> }
         <routine body>

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

<routine characteristic> ::=3D
         <language clause>
       | <parameter style clause>
       | SPECIFIC <specific name>
       | <deterministic characteristic>
       | <SQL-data access indication>
       | <null-call clause>
       | <transform group specification>
       | <dynamic result sets characteristic>

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

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

<external body reference> ::=3D
       EXTERNAL [ NAME <external routine name> ]
          [ <parameter style clause> ]
          [ <external security clause> ]

<language clause> ::=3D
          LANGUAGE <language name>

<language name> ::=3D
          ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | PLI | SQL

<dynamic result sets characteristic> ::=3D
          DYNAMIC RESULT SETS <maximum dynamic result sets>

<parameter style clause> ::=3D
         PARAMETER STYLE <parameter style>

<dispatch clause> ::=3D STATIC DISPATCH
<returns clause> ::=3D RETURNS <returns data type> [ <result cast> ]
<result cast> ::=3D CAST FROM <result cast from type>

<result cast from type> ::=3D
          <data type> [ <locator indication> ]

<returns data type> ::=3D <data type> [ <locator indication> ]

<parameter style> ::=3D
         SQL
       | GENERAL

<deterministic characteristic> ::=3D
       DETERMINISTIC
     | NOT DETERMINISTIC

<SQL-data access indication> ::=3D
        NO SQL
      | CONTAINS SQL
      | READS SQL DATA
      | MODIFIES SQL DATA

Timothy Madden <terminatorul@gmail.com> writes:
> Ok I get it. So Posgres also offers perl and python in addition to SQL.
> But at least for SQL, which is included and defined in the standard, could
> the syntax be made conforming ?

I think you still haven't got the point: there is *no* function language
that we offer that exactly matches what the spec has got.  Not using
string-literal syntax, difficult as that is already, would probably
represent about 1% of the work needed to implement what the spec
suggests.

            regards, tom lane

On Mon, Oct 26, 2009 at 12:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Timothy Madden <terminatorul@gmail.com> writes:
> > Ok I get it. So Posgres also offers perl and python in addition to SQL.
> > But at least for SQL, which is included and defined in the standard,
> could
> > the syntax be made conforming ?
>
> I think you still haven't got the point: there is *no* function language
> that we offer that exactly matches what the spec has got.  Not using
> string-literal syntax, difficult as that is already, would probably
> represent about 1% of the work needed to implement what the spec
> suggests.
>


I would like to put to good use the statements and expression that do match
(SELECT/INSERT/UPDATE/ ...), and the very function declaration syntax is
getting in the way, making even those matches now useless ...

Thank you,
Timothy Madden

2009/10/25 Timothy Madden <terminatorul@gmail.com>:
>
> On Sun, Oct 25, 2009 at 6:17 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 2009/10/25 Timothy Madden <terminatorul@gmail.com>:
>> >
>> >
>> > On Sun, Oct 25, 2009 at 12:42 PM, Peter Eisentraut <peter_e@gmx.net>
>> > wrote:
>> >>
>> >> On l=C3=B6r, 2009-10-24 at 14:01 +0000, Timothy Madden wrote:
>> >> > Can the string literal syntax for CREATE FUNCTION please, please be
>> >> > dropped
>> >> > ... ?
>> >
>> > [...]
>> >>
>> >> > It is not ANSI/ISO and so annoying for compatibility.
>> >>
>> >> Whatever is inside the string literal is also not ANSI/ISO, so why
>> >> would
>> >> it make a difference?
>> >>
>> > At least the function declaration syntax would be right. I think it
>> > would be
>> > an important difference; this string literal idea is so strange and
>> > unnatural ...
>> >
>> > And why the function body would not be standard ?
>>
>> Because standard knows only SQL/PSM language for SQL procedures.
>> Others databases support only one language for sql procedures (PL/SQL,
>> T-SQL). But PostgreSQL supports plpgsql, plperl, plpython as sql
>> procedures - and I am sure, so standard doesnt calculate with
>> multilangual environments.
>
>
> Ok I get it. So Posgres also offers perl and python in addition to SQL.
> But at least for SQL, which is included and defined in the standard, could
> the syntax be made conforming ? As an alternative to the one already
> used (to offer the additional langauges) ?
>

look on plsqlpsm - it is implementation of SQL/PSM.

> So that the additional languages are provided as before, and the
> standard-definded one still follows the=C2=A0standard ?
>
>
> Thank you,
> Timothy Madden
>
>
> P.S. :
> As a note, actually the standard provides for other languages, with the
> procedures only referenced as external (pre-compiled) instead of
> defined/scripted in line.

this is external languages. It is different.

>
> If anyone is interested you have below the CREATE FUNCTION and
> CREATE PROCEDURE syntax definition from SQL-3
> =C2=A0(SQL-99, Part 2 - Foundation).
>
> <SQL-invoked procedure> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PROCEDURE <schema qualif=
ied routine name>=C2=A0 <SQL parameter
> declaration list>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <routine characteristics>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <routine body>
>
> <SQL-invoked function> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 { <function specification> | <=
method specification designator> }
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <routine body>
>
> <function specification> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FUNCTION <schema qualified rou=
tine name> <SQL parameter declaration
> list>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <returns clause>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <routine characteristics>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 [ <dispatch clause> ]
>
> <routine characteristic> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <language clause>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <parameter style clause>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | SPECIFIC <specific name>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <deterministic characteristic>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <SQL-data access indication>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <null-call clause>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <transform group specification>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <dynamic result sets characteristi=
c>
>
> <routine body> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <SQL routine body>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | <external body reference>
>
> <SQL routine body> ::=3D <SQL procedure statement>
>
> <external body reference> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 EXTERNAL [ NAME <external routine na=
me> ]
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 [ <parameter style=
 clause> ]
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 [ <external securi=
ty clause> ]
>
> <language clause> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 LANGUAGE <language=
 name>
>
> <language name> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ADA | C | COBOL | =
FORTRAN | MUMPS | PASCAL | PLI | SQL
>
> <dynamic result sets characteristic> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 DYNAMIC RESULT SET=
S <maximum dynamic result sets>
>
> <parameter style clause> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PARAMETER STYLE <paramet=
er style>
>
> <dispatch clause> ::=3D STATIC DISPATCH
> <returns clause> ::=3D RETURNS <returns data type> [ <result cast> ]
> <result cast> ::=3D CAST FROM <result cast from type>
>
> <result cast from type> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <data type> [ <loc=
ator indication> ]
>
> <returns data type> ::=3D <data type> [ <locator indication> ]
>
> <parameter style> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SQL
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | GENERAL
>
> <deterministic characteristic> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 DETERMINISTIC
> =C2=A0=C2=A0=C2=A0=C2=A0 | NOT DETERMINISTIC
>
> <SQL-data access indication> ::=3D
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 NO SQL
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | CONTAINS SQL
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | READS SQL DATA
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | MODIFIES SQL DATA
>

2009/10/25 Timothy Madden <terminatorul@gmail.com>:
>
>
> On Mon, Oct 26, 2009 at 12:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Timothy Madden <terminatorul@gmail.com> writes:
>> > Ok I get it. So Posgres also offers perl and python in addition to SQL.
>> > But at least for SQL, which is included and defined in the standard,
>> > could
>> > the syntax be made conforming ?
>>
>> I think you still haven't got the point: there is *no* function language
>> that we offer that exactly matches what the spec has got. =C2=A0Not using
>> string-literal syntax, difficult as that is already, would probably
>> represent about 1% of the work needed to implement what the spec
>> suggests.
>
>
> I would like to put to good use the statements and expression that do mat=
ch
> (SELECT/INSERT/UPDATE/ ...), and the very function declaration syntax is
> getting in the way, making even those matches now useless ...

???

Pavel
>
> Thank you,
> Timothy Madden
>

On Mon, Oct 26, 2009 at 6:44 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

> 2009/10/25 Timothy Madden <terminatorul@gmail.com>:
> >
> [...]
> > I would like to put to good use the statements and expression that do
> match
> > (SELECT/INSERT/UPDATE/ ...), and the very function declaration syntax is
> > getting in the way, making even those matches now useless ...
>
> ???
>
>
It is a way of saying I see this as the first 1% to be done towards SQL/PSM,
so I
can then use the other statements in a function.

Thank you,
Timothy Madden