Thread: please review source(SQLServer compatible)‏

On 06/23/2014 10:51 AM, rohtodeveloper wrote:
> Dear all，
>
> Our application will be switched from SQL Server to PostgreSQL.
> However, a few functions are not supported yet. So we decided to
> extend it.
>
> The functions are as following:
>
> 1.SQL statement support
>  INSERT statement without INTO keyword
>  DELETE statement without FROM keywork
> 2.Build-in function
>  SQUARE
>  CHAR
>  CHARINDEX
>  LEN
>  REPLICATE
>  SPACE
>  STR
>  STUFF
>  CONVERT
>  DATALENGTH
>  DATEADD
>  DATEDIFF
>  DATEPART
>  DAY
>  MONTH
>  YEAR
>  EOMONTH
>  GETDATE
>  SYSDATETIME
> 3.Operator
>  operator !< (Not Less Than)
>  operator !> (Not Greater Than)
>  operator + (String Concatenation)
> 4.Other
>  DataType support(smalldatetime,datetime,datatime2,uniqueidentifer)
>  Date, Time, and Timestamp Escape Sequences ODBC Scalar Functions
>  OCTET_LENGTH
>  CURRENT_DATE
>  CURRENT_TIME
>
> The extended functions are almost completed but your opinion is very
> important to us.
> Would you please help us to review the extended source?
>
> The attachments is the diff source.
>
> Thank you very much.
>
>


I think this effort is fundamentally misguided. It will mean a
maintenance nightmare for you. You would be much better off migrating
your app to rid it of these SQLServerisms, especially those that require
backend changes. If you have layered your application correctly, so that
the places it calls SQL are relatively confined, then this should not be
terribly difficult. If you have not, then you have bigger problems than
these anyway.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> wrote:
> On 06/23/2014 10:51 AM, rohtodeveloper wrote:

>> Our application will be switched from SQL Server to PostgreSQL.
>> However, a few functions are not supported yet. So we decided to
>> extend it.
>>
>> The functions are as following:
>>
>> 1.SQL statement support
>>   INSERT statement without INTO keyword
>>   DELETE statement without FROM keywork

Those would be pretty trivial to do in core; the question is
whether the community would agree that a few extra lines in the
parser (and compatibility sections of the docs) is worth it for
portability from SQL Server and Sybase.

>> 2.Build-in function
>>   SQUARE
>>   CHAR
>>   CHARINDEX
>>   LEN
>>   REPLICATE
>>   SPACE
>>   STR
>>   STUFF
>>   CONVERT
>>   DATALENGTH
>>   DATEADD
>>   DATEDIFF
>>   DATEPART
>>   DAY
>>   MONTH
>>   YEAR
>>   EOMONTH
>>   GETDATE
>>   SYSDATETIME
>> 3.Operator
>>   operator !< (Not Less Than)
>>   operator !> (Not Greater Than)
>>   operator + (String Concatenation)

It seems likely that you could write an extension to add these
(using the CREATE EXTENSION feature) and submit them to
http://pgxn.org if you wanted to.  Is there some reason you're not
going this route?

>> 4.Other
>>   DataType support(smalldatetime,datetime,datatime2,uniqueidentifer)
>>   Date, Time, and Timestamp Escape Sequences ODBC Scalar Functions
>>   OCTET_LENGTH
>>   CURRENT_DATE
>>   CURRENT_TIME

You can add data types (including within extensions), and some of
those are things which seem to be implemented in some form.

test=# select current_date;
    date   
------------
 2014-06-23
(1 row)

test=# select current_time;
       timetz      
--------------------
 10:44:36.958967-05
(1 row)

test=# select octet_length('abcd');
 octet_length
--------------
            4
(1 row)

test=# select octet_length('π');
 octet_length
--------------
            2
(1 row)

If the point is that you want to change the semantics of existing
valid PostgreSQL statements, that's probably not a good idea.

>> The extended functions are almost completed but your opinion is very
>> important to us.
>> Would you please help us to review the extended source?

http://wiki.postgresql.org/wiki/Submitting_a_Patch

>> The attachments is the diff source.

I think if you want someone to look at this, you really need to
provide a single file with a unified or context diff of the entire
source trees.  And you may have trouble finding anyone willing to
review it for free unless you are explicitly looking to share the
code for free.

> I think this effort is fundamentally misguided. It will mean a
> maintenance nightmare for you. You would be much better off migrating
> your app to rid it of these SQLServerisms, especially those that require
> backend changes. If you have layered your application correctly, so that
> the places it calls SQL are relatively confined, then this should not be
> terribly difficult. If you have not, then you have bigger problems than
> these anyway.

There is certainly something to that point of view, but
implementing compatibility shims can reduce the effort of
migration, and isn't always a bad idea.  One thing which is just
going to need to be fixed in the application code is any instance
of UPDATE with a FROM clause.  SQL Server and PostgreSQL both have
non-standard extensions which support such syntax, but with
different semantics, so such a statement written for SQL Server
will probably run without throwing an error under PostgreSQL, but
will not do what it did under SQL Server.  In many cases it will
run for a *very* long time, and if allowed to finish will probably
update rows which were not intended.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06/23/2014 04:51 PM, rohtodeveloper wrote:
> 1.SQL statement support
>  INSERT statement without INTO keyword
>  DELETE statement without FROM keywork

Why would we want this?
--
Vik

Vik Fearing <vik.fearing@dalibo.com> wrote:
> On 06/23/2014 04:51 PM, rohtodeveloper wrote:
>> 1.SQL statement support
>>   INSERT statement without INTO keyword
>>   DELETE statement without FROM keywork
>
> Why would we want this?

I'm pretty sure that the only argument for it is to ease migration
of software from other DBMS products which allow that non-standard
syntax for people who have chosen to use the non-standard form of
the statement instead of the standard syntax (which is also
available in all cases that I know of).

If the SQL standard were static, I would actually lean toward
allowing it, to make it easier for people to switch to PostgreSQL.
The biggest down side I see is the possibility that some future
version of the standard might implement some new syntax which is
more difficult to implement if we need to also support this
non-standard variation.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company