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

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.
Best RegardsRohtodeveloper

On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper(at)outlook(dot)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

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(dot)jiit(at)gmail(dot)com
To: rohtodeveloper(at)outlook(dot)com
CC: pgsql-hackers(at)postgresql(dot)org

On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper(at)outlook(dot)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

2014-11-02 13:22 GMT+01:00 rohtodeveloper <rohtodeveloper(at)outlook(dot)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(dot)jiit(at)gmail(dot)com
> To: rohtodeveloper(at)outlook(dot)com
> CC: pgsql-hackers(at)postgresql(dot)org
>
>
>
>
> On Sun, Nov 2, 2014 at 3:40 PM, rohtodeveloper <rohtodeveloper(at)outlook(dot)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
>
>
>

rohtodeveloper <rohtodeveloper(at)outlook(dot)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 with the 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