Re: [HACKERS] 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
[HACKERS] Re: [HACKERS] please review source(SQLServer compatible)
Andrew Dunstan and...@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
[HACKERS] Re: [HACKERS] Re: [HACKERS] please review source(SQLServer compatible)
2014-06-23 18:00 GMT+02:00 Kevin Grittner kgri...@ymail.com:
Andrew Dunstan and...@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
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
Re: [HACKERS] please review source(SQLServer compatible)
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
[HACKERS] Re: [HACKERS] please review source(SQLServer compatible)
Vik Fearing vik.fear...@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
[HACKERS] Re: [HACKERS] Re: [HACKERS] please review source(SQLServer compatible)
2014-06-23 19:22 GMT+02:00 Kevin Grittner kgri...@ymail.com: Vik Fearing vik.fear...@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 -- 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
