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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2014-06-23 18:00 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

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.

I am strongly against - it is murder of ANSI SQL

Regards

Pavel

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2014-06-23 19:22 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

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

There is a fork of PostgreSQL http://www.tpostgres.org/se/ what can do it
better this task. We doesn't support a special syntax for Oracle more, for
DB2 and I don't see any reason, why we should to do for T-SQL.

More - usually this is most simple part in migration from Sybase family to
PostgreSQL - there is totally different concept of stored procedures, temp
tables, and other so there is not possible simple migration without
relative hard changes in PostgreSQL parser.

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.

yes.

Regards

Pavel