Thread: How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

From

rohtodeveloper

Date:

02 November 2014, 13:10:22

<div dir="ltr"><div style="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft
YaHei',宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);">Dear</div><div
style="line-height:21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri,
sans-serif;font-size: 15px; background-color: rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color:
rgb(68,68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family:
'MicrosoftYaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255,
255);">InSQLServer, there'are two functions to converte an expression of one data type to another.</div><div
style="line-height:21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri,
sans-serif;font-size: 15px; background-color: rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color:
rgb(68,68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);">1. CAST ( expression AS data_type [ ( length ) ] )</div><div style="line-height:
21px;color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size:
15px;background-color: rgb(255, 255, 255);">2. CONVERT ( data_type [ ( length ) ] , expression )</div><div
style="line-height:21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri,
sans-serif;font-size: 15px; background-color: rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color:
rgb(68,68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);">However, In PostgreSQL, there's only the CAST ( expression AS data_type [ (
length) ] ) function. I have tried the following two ways to implenting the CONVERT ( data_type [ ( length ) ] ,
expression) function, but both are failed.</div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family:
'MicrosoftYaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255,
255);"><br/></div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft
YaHei',宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);">1. CREATE FUNCTION
..... </div><divstyle="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei',
宋体,Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);">The function's arguments can only be
expressionsbut not data_type . </div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft
YaHeiUI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);">2.
Modifyingthe gram.y .....</div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei
UI','Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);">The CONVERT (
data_type[ ( length ) ] , expression ) is in grammer conflict with the PostgreSQL self's
convert(data,src_encoding_name,dest_encoding_name)function. And the PostgreSQL self's
convert(data,src_encoding_name,dest_encoding_name)function cannot be used.</div><div style="line-height: 21px; color:
rgb(68,68, 68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family:
'MicrosoftYaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255,
255);">Iwonder whether there's a better way to solve this problem. </div><div style="line-height: 21px; color: rgb(68,
68,68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);">Any help will be appreciated.</div><div style="line-height: 21px; color: rgb(68,
68,68); font-family: 'Microsoft YaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px;
background-color:rgb(255, 255, 255);"><br /></div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family:
'MicrosoftYaHei UI', 'Microsoft YaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255,
255);">BestRegards</div><div style="line-height: 21px; color: rgb(68, 68, 68); font-family: 'Microsoft YaHei UI',
'MicrosoftYaHei', 宋体, Calibri, sans-serif; font-size: 15px; background-color: rgb(255, 255,
255);">Rohtodeveloper</div></div>

Re: How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

From

Atri Sharma

Date:

02 November 2014, 13:30:22

On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper@outlook.com> wrote:

Dear

In SQLServer, there'are two functions to converte an expression of one data type to another.

1. CAST ( expression AS data_type [ ( length ) ] )
2. CONVERT ( data_type [ ( length ) ] , expression )

However, In PostgreSQL, there's only the CAST ( expression AS data_type [ ( length ) ] ) function. I have tried the following two ways to implenting the CONVERT ( data_type [ ( length ) ] , expression ) function, but both are failed.

1. CREATE FUNCTION .....
The function's arguments can only be expressions but not data_type .
2. Modifying the gram.y .....
The CONVERT ( data_type [ ( length ) ] , expression ) is in grammer conflict with the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function. And the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function cannot be used.

I wonder whether there's a better way to solve this problem.
Any help will be appreciated.
Please do not cross post to multiple lists.

Please do not cross post to various lists.

For the options you suggested:

1) Pass in datatype as string and deparse and process in the function.

2) Are you referring to pg_convert here?

IMO I do not understand why you need the convert function in the first place. You may want to refer to http://www.postgresql.org/docs/9.3/static/typeconv.html

Re: How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

From

rohtodeveloper

Date:

02 November 2014, 15:22:34

I need the convert function because that Our application will be switched from SQL Server to PostgreSQL.

>For the options you suggested:

>1) Pass in datatype as string and deparse and process in the function.

>2) Are you referring to pg_convert here?

1) is yes. but I want to use the CONVERT ( data_type [ ( length ) ] , expression ) just as same as in the SQLServer, SO, that doesn't work.

2) I mean modifying the 'src\backend\parser\gram.y' file. There will be a grammer conflict with the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function.

Date: Sun, 2 Nov 2014 16:00:14 +05
Subject: Re: [HACKERS] How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL
From: atri.jiit@gmail.com
To: rohtodeveloper@outlook.com
CC: pgsql-hackers@postgresql.org

On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper@outlook.com> wrote:

Dear

In SQLServer, there'are two functions to converte an expression of one data type to another.

1. CAST ( expression AS data_type [ ( length ) ] )
2. CONVERT ( data_type [ ( length ) ] , expression )

However, In PostgreSQL, there's only the CAST ( expression AS data_type [ ( length ) ] ) function. I have tried the following two ways to implenting the CONVERT ( data_type [ ( length ) ] , expression ) function, but both are failed.

1. CREATE FUNCTION .....
The function's arguments can only be expressions but not data_type .
2. Modifying the gram.y .....
The CONVERT ( data_type [ ( length ) ] , expression ) is in grammer conflict with the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function. And the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function cannot be used.

I wonder whether there's a better way to solve this problem.
Any help will be appreciated.
Please do not cross post to multiple lists.

Please do not cross post to various lists.

For the options you suggested:

1) Pass in datatype as string and deparse and process in the function.

2) Are you referring to pg_convert here?

IMO I do not understand why you need the convert function in the first place. You may want to refer to http://www.postgresql.org/docs/9.3/static/typeconv.html

Re: How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

From

Pavel Stehule

Date:

02 November 2014, 15:48:44

2014-11-02 13:22 GMT+01:00 rohtodeveloper <rohtodeveloper@outlook.com>:

I need the convert function because that Our application will be switched from SQL Server to PostgreSQL.

>For the options you suggested:

>1) Pass in datatype as string and deparse and process in the function.
>2) Are you referring to pg_convert here?

1) is yes. but I want to use the CONVERT ( data_type [ ( length ) ] , expression ) just as same as in the SQLServer, SO, that doesn't work.

usually is more simple fix the application than database.

2) I mean modifying the 'src\backend\parser\gram.y' file. There will be a grammer conflict with the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function.

you can look on http://www.tpostgres.org/se/ .. there maybe is your problem solved

Regards

Pavel

Date: Sun, 2 Nov 2014 16:00:14 +05
Subject: Re: [HACKERS] How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL
From: atri.jiit@gmail.com
To: rohtodeveloper@outlook.com
CC: pgsql-hackers@postgresql.org

On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper@outlook.com> wrote:

Dear

In SQLServer, there'are two functions to converte an expression of one data type to another.

1. CAST ( expression AS data_type [ ( length ) ] )
2. CONVERT ( data_type [ ( length ) ] , expression )

However, In PostgreSQL, there's only the CAST ( expression AS data_type [ ( length ) ] ) function. I have tried the following two ways to implenting the CONVERT ( data_type [ ( length ) ] , expression ) function, but both are failed.

1. CREATE FUNCTION .....
The function's arguments can only be expressions but not data_type .
2. Modifying the gram.y .....
The CONVERT ( data_type [ ( length ) ] , expression ) is in grammer conflict with the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function. And the PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function cannot be used.

I wonder whether there's a better way to solve this problem.
Any help will be appreciated.
Please do not cross post to multiple lists.

Please do not cross post to various lists.

For the options you suggested:

1) Pass in datatype as string and deparse and process in the function.
2) Are you referring to pg_convert here?

IMO I do not understand why you need the convert function in the first place. You may want to refer to http://www.postgresql.org/docs/9.3/static/typeconv.html

Re: How to implent CONVERT ( data_type [ ( length ) ] , expression ) function in postgreSQL

From

Tom Lane

Date:

02 November 2014, 19:28:49

rohtodeveloper <rohtodeveloper@outlook.com> writes:
> I  need the convert function because that Our application will be switched from SQL Server to PostgreSQL.
>> For the options you suggested:

>> 1) Pass in datatype as string and deparse and process in the function.
>> 2) Are you referring to pg_convert here?

> 1) is yes. but I want to use the CONVERT ( data_type [ ( length ) ] , expression ) just as same as in the SQLServer,
SO,that doesn't  work.2)  I mean modifying the 'src\backend\parser\gram.y'  file.  There will be a grammer conflict
withthe PostgreSQL self's convert(data,src_encoding_name,dest_encoding_name) function.
 

So what?  Presumably your SQL-Server-based app doesn't use that function.

You could probably make it work anyway by introducing two new productions,
one that implements the CAST-equivalent syntax and one that defines
extract() with a regular func_arg_list argument list.  But I'm not sure
I see the point if you're building a private fork.

On the whole I agree with the other commenters suggesting that fixing your
app to use SQL-standard syntax would be a better answer in the long run.
It's quite unlikely that you're going to be able to hack Postgres to be
bug-compatible with SQL Server in every last respect, so trying to run
your app totally unmodified from its present state seems like a fool's
errand.  Anyplace where you can dodge the problem by switching to
spec-mandated syntax that both DBMSes understand, you're way ahead of
the game if you fix it that way.
        regards, tom lane