Thread: Cast in PG 8.3

Cast in PG 8.3

From
Franklin Haut
Date:
<br /><br /><font face="Courier New, Courier, monospace">Hi all,<br /><br /> I´m testing the new version of PostgreSQL
inwindows plataform but i have a problem.<br /><br /><br /><b>In PG 8.2 </b><br /><br /> Create Table temp (id
int4);<br/> insert into temp values (1215);<br /> insert into temp values (1216);<br /> insert into temp values
(1217);<br/> insert into temp values (1218);<br /> insert into temp values (1219);<br /> insert into temp values
(1220);<br/> insert into temp values (1221);<br /> insert into temp values (1222);<br /> SELECT * FROM TEMP WHERE id
ilike('%122%');<br /><br /> return ok, 3 rows (1220, 1221 and 1222)<br /><br /><br /><br /><b>In PG 8.3<br /><br
/></b>CreateTable temp (id integer);<br /> insert into temp values (1215);<br /> insert into temp values (1216);<br />
insertinto temp values (1217);<br /> insert into temp values (1218);<br /> insert into temp values (1219);<br /> insert
intotemp values (1220);<br /> insert into temp values (1221);<br /> insert into temp values (1222);<br /> SELECT * FROM
TEMPWHERE id ilike ('%122%');<br /><br /><b>Error:</b><br /> operator does not exist: integer ~~* unknown at character
31<br/> HINT:  No operator matches the given name and argument type(s). <br /> You might need to add explicit type
casts.<br/><br /><br /> I Know that changing the SQL command to :<br /></font><font face="Courier New, Courier,
monospace">SELECT* FROM TEMP WHERE CAST(id AS TEXT) ilike ('%122%'); <br /> work´s but for now isn't possible... :(<br
/><br/></font><font face="Courier New, Courier, monospace"><br /> I Tries  create a cast but the function text doesn't
existmore in PG 8.3<br /><br /> CREATE CAST (int AS text) WITH FUNCTION text(int);<br /><br /> can everyone help me to
createa cast ou other solution ?<br /><br /> Thank´s <br /><br /><br /> Franklin<br /><br /><br /><br /><br /><br /><br
/><br/><br /><br /><br /></font> 

Re: Cast in PG 8.3

From
"Jaime Casanova"
Date:
On Feb 5, 2008 2:22 PM, Franklin Haut <franklin.haut@gmail.com> wrote:
>
>
> Hi all,
>
> I´m testing the new version of PostgreSQL in windows plataform but i have a
> problem.
>
>
> In PG 8.2
>
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> return ok, 3 rows (1220, 1221 and 1222)
>
>
>
> In PG 8.3
>
> Create Table temp (id integer);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> Error:
> operator does not exist: integer ~~* unknown at character 31
> HINT:  No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>

did you read the release notes?
http://www.postgresql.org/docs/current/static/release-8-3.html
"""
Non-character data types are no longer automatically cast to TEXT (Peter, Tom)
"""

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: Cast in PG 8.3

From
"Li, Jingfa"
Date:
check the typo -- "ilike"

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Jaime Casanova
Sent: Tuesday, February 05, 2008 11:19 AM
To: Franklin Haut
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Cast in PG 8.3

On Feb 5, 2008 2:22 PM, Franklin Haut <franklin.haut@gmail.com> wrote:
>
>
> Hi all,
>
> I´m testing the new version of PostgreSQL in windows plataform but i
> have a problem.
>
>
> In PG 8.2
>
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> return ok, 3 rows (1220, 1221 and 1222)
>
>
>
> In PG 8.3
>
> Create Table temp (id integer);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
>
> Error:
> operator does not exist: integer ~~* unknown at character 31
> HINT:  No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>

did you read the release notes?
http://www.postgresql.org/docs/current/static/release-8-3.html
"""
Non-character data types are no longer automatically cast to TEXT (Peter, Tom) """

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and
theuniverse trying to produce bigger and better idiots. 
So far, the universe is winning."                                      Richard Cook

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org


Re: Cast in PG 8.3

From
"Jaime Casanova"
Date:
On Feb 5, 2008 2:39 PM, Li, Jingfa <jinli@paypal.com> wrote:
> check the typo -- "ilike"
>

that's not a typo, it's "case insensitive LIKE"

-- 
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: Cast in PG 8.3

From
"Li, Jingfa"
Date:
the column is an integer, no sense for case sensitive or insensitive...

change it to 'like', you'll get what you want.

-----Original Message-----
From: Jaime Casanova [mailto:systemguards@gmail.com]
Sent: Tuesday, February 05, 2008 11:47 AM
To: Li, Jingfa
Cc: Franklin Haut; pgsql-sql@postgresql.org
Subject: Re: [SQL] Cast in PG 8.3

On Feb 5, 2008 2:39 PM, Li, Jingfa <jinli@paypal.com> wrote:
> check the typo -- "ilike"
>

that's not a typo, it's "case insensitive LIKE"

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying to
produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: Cast in PG 8.3

From
"Markus Bertheau"
Date:
2008/2/6, Jaime Casanova <systemguards@gmail.com>:

> did you read the release notes?

Obviously he did:

> I Know that changing the SQL command to :
> SELECT * FROM TEMP WHERE CAST(id AS TEXT) ilike ('%122%');
> work´s but for now isn't possible... :(
>
>
> I Tries  create a cast but the function text doesn't exist more in PG 8.3
>
> CREATE CAST (int AS text) WITH FUNCTION text(int);
>
> can everyone help me to create a cast ou other solution ?

So the question stands.


Re: Cast in PG 8.3

From
Tom Lane
Date:
Franklin Haut <franklin.haut@gmail.com> writes:
> Create Table temp (id int4);
> insert into temp values (1215);
> insert into temp values (1216);
> insert into temp values (1217);
> insert into temp values (1218);
> insert into temp values (1219);
> insert into temp values (1220);
> insert into temp values (1221);
> insert into temp values (1222);
> SELECT * FROM TEMP WHERE id ilike ('%122%');
> 
> return ok, 3 rows (1220, 1221 and 1222)

That usage is simply bizarre.  Why don't you define the column as text,
instead, if that's the type of query you intend to perform on it?
        regards, tom lane


Re: Cast in PG 8.3

From
Volkan YAZICI
Date:
Franklin Haut <franklin.haut@gmail.com> writes:
> I Tries  create a cast but the function text doesn't exist more in PG 8.3

sql-createcast.html tells that
 "It is normally not necessary to create casts between user-defined types and the standard string types (text, varchar,
andchar(n)). PostgreSQL will automatically handle a cast to a string type by invoking the other type's output function,
..."

Therefore, this might help you:

test=# SELECT typoutput FROM pg_type WHERE typname = 'int4';typoutput
-----------int4out
(1 row)


Regards.


Multiple postgresql functions in a single transaction

From
"Jyoti Seth"
Date:
Hi,

I have two postgresql functions. One function is calling another function
for certain value. I want that these two functions work under single
transaction so that even if the value gets generated in the second function
and the first function that calls the second function fails. Then the value
generated in the second function should also roll back.

Please let me know if we can execute two functions of postgresql in a single
transaction.

Thanks,
Jyoti Seth







Re: Multiple postgresql functions in a single transaction

From
"Robins Tharakan"
Date:

It can be done, but it depends on how you are generating the value in the first function.
If you sequences though you may have to take care of reverting it yourself.

Robins


---------- Forwarded message ----------
From: Jyoti Seth <jyotiseth2001@gmail.com>
Date: Feb 6, 2008 11:51 AM
Subject: [SQL] Multiple postgresql functions in a single transaction
To: pgsql-sql@postgresql.org


Hi,

I have two postgresql functions. One function is calling another function
for certain value. I want that these two functions work under single
transaction so that even if the value gets generated in the second function
and the first function that calls the second function fails. Then the value
generated in the second function should also roll back.

Please let me know if we can execute two functions of postgresql in a single
transaction.

Thanks,
Jyoti Seth






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


Re: Multiple postgresql functions in a single transaction

From
"Jyoti Seth"
Date:

Thanks. I am not using sequences. I tried an example and it worked.

 

From: Robins Tharakan [mailto:tharakan@gmail.com]
Sent: Wednesday, February 06, 2008 12:54 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Multiple postgresql functions in a single transaction

 

 

It can be done, but it depends on how you are generating the value in the first function.
If you sequences though you may have to take care of reverting it yourself.

Robins

 

---------- Forwarded message ----------
From: Jyoti Seth <jyotiseth2001@gmail.com>
Date: Feb 6, 2008 11:51 AM
Subject: [SQL] Multiple postgresql functions in a single transaction
To: pgsql-sql@postgresql.org


Hi,

I have two postgresql functions. One function is calling another function
for certain value. I want that these two functions work under single
transaction so that even if the value gets generated in the second function
and the first function that calls the second function fails. Then the value
generated in the second function should also roll back.

Please let me know if we can execute two functions of postgresql in a single
transaction.

Thanks,
Jyoti Seth






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

 

 

Re: Multiple postgresql functions in a single transaction

From
Date:
Robins Tharakan  wrote:
> > It can be done, but it depends on how you are generating the value in the
> > first function.
> > If you sequences though you may have to take care of reverting it
> > yourself.

Sequences had been constructed in this manner not to cause pain for users -- think of it.
So i advise to avoid reverting sequences ANYWAY.
Even if you think that this would not cause a problem.


> > ---------- Forwarded message ----------
> > From: Jyoti Seth <jyotiseth2001@gmail.com>
> > Date: Feb 6, 2008 11:51 AM
> > Subject: [SQL] Multiple postgresql functions in a single transaction
> > To: pgsql-sql@postgresql.org
> >
> >
> > Hi,
> >
> > I have two postgresql functions. One function is calling another function
> > for certain value. I want that these two functions work under single
> > transaction so that even if the value gets generated in the second
> > function
> > and the first function that calls the second function fails. Then the
> > value
> > generated in the second function should also roll back.
> >
> > Please let me know if we can execute two functions of postgresql in a
> > single
> > transaction.
> >
> > Thanks,
> > Jyoti Seth
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> >