Re: [HACKERS] please review source(SQLServer compatible)‏ - Mailing list pgsql-hackers

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