SEO

Tino Wildenhain schrieb:
> Hi,
>
> Daniel Schuchardt wrote:
> ...
>> in 81:
>>
>> postgres=# SELECT 1::INTEGER||1::INTEGER;
>> ?column?
>> ----------
>> 11
>> (1 row)
>
> *shudder* is this actually a port of an application originally
> targeted at M*Sql?
>
> Are you using those columns somewhere with their real type - as
> integer? I mean if you use them as text everywhere why not change
> the type once?
>
> T.
*g*

yes, sure we have to CAST it now. thats no problem. but the problem is
to find all the places where to cast. and you see that there are many
possiblilitys.

another example?:

RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)

another one:

here we need to add 4 CASTS. you see.....

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN R;
END$$LANGUAGE plpgsql IMMUTABLE;

Tom Lane schrieb:
> David Fetter <david@fetter.org> writes:
>
>> On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:
>>
>>> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
>>> that all use implicit casting. and: everything is working fine ;-)
>>> :-P
>>>
>
>
>> How do you know? 8.3 removed the implicit casts precisely because
>> they were producing results that could most generously be describe as
>> "surprising."
>>
>
> This should not be underestimated. From the reports we've seen so far,
> a very sizable fraction of people who find this kind of failure with 8.3
> find out that their application was doing something unexpected in the
> cases where it happened. If you've got as many failures as you suggest,
> I'd be willing to bet that some of them are bugs in your code, not just
> reliance on an implicit feature.
>
> regards, tom lane
>
>
yes true your right but lets make a calculation:

our application runs about 6 Years now so lets say there are 5% queries
that run still in a mistake caus of auto cast.
We have to check about 1200 views, 500 functions, every runtime created
query and so on. Lets say 95% of them run fine now. Now lets think we
check and rewrite all of them. If you let out the time (our customers
wont pay for such changes, they will have more errors the first time
than the last years), we should expect about X % errors because of the
changes (wrong parantheses and so on). So it would be a horror for us.
thats the problem.

examples:

RAISE EXCEPTION "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (KS is DECLARED VARCHAR, LoopDate is
a TIMESTAMP);

stempz:=Round(SUM(COALESCE(ba_efftime, timediff(ba_anf, CAST(now() AS
TIMESTAMP(0) WITHOUT TIME ZONE))))) FROM bdea WHERE
timestamp_to_date(ba_anf)=current_date AND ba_ks=oks AND
*ba_ix||'~'||ba_op* IN (SELECT *a2_ab_ix||'~'||a2_n* FROM ab2_wkstplan
JOIN ab2 ON a2_id=a2w_a2_id WHERE a2w_oks=oks AND a2w_ks=ks AND
a2w_planweek=week);

this are integer fields. but they are unique with "*a2_ab_ix||'~'||a2_n*".

another one:

here we need to add 4 CASTS.
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN R;
END$$LANGUAGE plpgsql IMMUTABLE;

Tino Wildenhain schrieb:
>
> RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;
>
> was too clean and easy? )
>
> Looks like a good oportunity to clean up your code before anything
> unexpected happens :-)
>
> Cheers
> T.
>


LOL. Yes I don't like such easy things :-P

But you see i have a function so i simply can change it ;-)

thnx for the hint, i picked up the best example ever :-P

Daniel.

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

Daniel Schuchardt wrote:
> Tino Wildenhain schrieb:
>>
>> RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;
>>
>> was too clean and easy? )
>>
>> Looks like a good oportunity to clean up your code before anything
>> unexpected happens :-)
>>
>> Cheers
>> T.
>>
>
>
> LOL. Yes I don't like such easy things :-P
> RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)

You know you can use more than one % in a raise and it will take care of
the data types?

create function atest() returns integer as $$
declare
ks integer;
loopdate timestamp;
begin
ks := 3;
loopdate := now();
raise notice 'blah ks:%@%', ks, loopdate;
return 1;
end;
$$ language plpgsql;

postgres=# select atest();
NOTICE: blah ks:3@2008-05-06 09:58:55.812
atest
-------
1
(1 row)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au


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

> another example?:
>
> RAISE NOTICE "error during validation % :", 'ks:"'||ks||'"@"'||
> loopdate||'"'; (here LoopDate is a DateTime)

Ehm... What's wrong with RAISE NOTICE "error during validation
ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose
of that colon at the end, btw).
Allows you to format the date to your liking too, just add a to_char
(loopdate, <format string>).

I know these were just a few examples of your troubles, but so far it
appears it's desirable to get rid of them for better code.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,481ffc80927661001715755!



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

Klint Gore schrieb:
> > RAISE NOTICE "error during validation % :",
> 'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)
>
> You know you can use more than one % in a raise and it will take care
> of the data types?
>
yes i know. the real code looks like this:

S:='another ABG found on ks:"'||ks||'"@"'||loopdate||'"';
PERFORM internalcreatemessage(current_user, 'W', S);


greets.

Alban Hertroys schrieb:
>> another example?:
>>
>> RAISE NOTICE "error during validation % :",
>> 'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)
>
> Ehm... What's wrong with RAISE NOTICE "error during validation
> ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose
> of that colon at the end, btw).
> Allows you to format the date to your liking too, just add a
> to_char(loopdate, <format string>).
>
> I know these were just a few examples of your troubles, but so far it
> appears it's desirable to get rid of them for better code.
>
may be thats a good thing but its not possible. we have to do a major
rollout for this. we have to look through all the code.
and our next and more important project is to become .net compatible. we
have to upgrade our development enviroment because of trouble with
incompatibilities ;-)
so we have to stay on postgresql81 the next years.
if it is nice or not to work with autocasts doesnt matter, it was able
to do it so we used it for many years.

here another nice example:
(old.dbrid is INTEGER)

EXECUTE 'UPDATE '||old.rc_tablename||' SET wvod=NULL WHERE
dbrid='||old.rc_dbrid;

TIMESTAMP
f:=EXTRACT(MINUTE FROM new.bd_anf_rund-CAST('0:'||CAST(f3 AS
VARCHAR) AS TIME));

TIMESTAMP too
new.bd_anf_rund:=timestamp_to_date(new.bd_anf) || ' ' || f4+f2 ||
':' || f1*tplrund;



PS:

i pick up only some string concatanation examples because i can identify
them fast. all other things i cant see so easy.
(varcharfield=integerfield ^^)

PSPS:
don't touch a running system ;-)

Daniel.

Daniel Schuchardt schrieb:
> Hey Group,
>
> i know what all will say but i need to recreate the = operator for
> datatypes varchar and integer in PostgreSQL 8.3.
>
> Our Software Project has Millions of Lines and so it would be
> difficult to check all queries and Datatypes. Also it works really
> fine and we all know the risk of wrong auto casting.
>
> Anyone knows the Syntax?
>
> Thanks a lot for your great work.
>
>
> Daniel.

so it depends on ? if i need an explicit cast?

demo=# CREATE TABLE a (a VARCHAR, b VARCHAR);
CREATE TABLE
demo=# CREATE SEQUENCE test;
CREATE SEQUENCE
demo=# ALTER TABLE a ALTER COLUMN a SET DEFAULT nextval('test');
ALTER TABLE
demo=# INSERT INTO a (b) VALUES ('C');
INSERT 0 1
demo=# SELECT * FROM a;
a | b
---+---
1 | C
(1 row)

demo=# INSERT INTO a (b) VALUES (nextval('test'));
INSERT 0 1
demo=# INSERT INTO a (b) VALUES (5);
INSERT 0 1
demo=# SELECT * FROM a WHERE b=5;
ERROR: operator does not exist: character varying = integer at character 24
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE b=5;
^
demo=# SELECT * FROM a WHERE b='5';
a | b
---+---
4 | 5
(1 row)

demo=# UPDATE a SET a=nextval('test'), b=nextval('test');
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3;
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
a | b
---+------
5 | 20~1
6 | 21~1
7 | 22~1
(3 rows)

demo=# UPDATE a SET b=3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
a | b
---+-----
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b=3||'~1';
a | b
---+-----
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3||'%';
a | b
---+-----
5 | 3~1
6 | 3~1
7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3;
ERROR: operator does not exist: character varying ~~ integer at
character 25
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE b LIKE 3;
^
demo=# ALTER TABLE a ADD COLUMN c INTEGER;
ALTER TABLE
demo=# UPDATE a SET a=1, c=nextval('test');
UPDATE 3
demo=# SELECT * FROM a WHERE c=1;
a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c='1';
a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c=a;
ERROR: operator does not exist: integer = character varying at character 24
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE c=a;
^

demo=# SELECT * FROM a WHERE a=1;
ERROR: operator does not exist: character varying = integer at character 24
HINT: No operator matches the given name and argument type(s). You
might need t
o add explicit type casts.
LINE 1: SELECT * FROM a WHERE a=1;
^
demo=# SELECT * FROM a WHERE a='1';
a | b | c
---+-----+----
1 | 3~1 | 23
1 | 3~1 | 24
1 | 3~1 | 25
(3 rows)


demo=# CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$ BEGIN RAISE
EXCEPTION '%', 1||'B'||current_date; RETURN; END$$LANGUAGE plpgsql;
CREATE FUNCTION
demo=# SELECT test();
ERROR: 1B2008-05-06

demo=# ALTER TABLE a ADD COLUMN d VARCHAR;
ALTER TABLE
demo=# UPDATE a SET d=current_date;
UPDATE 3
demo=# SELECT * FROM a WHERE d=current_date;
ERROR: operator does not exist: character varying = date at character 24
HINT: No operator matches the given name and argument type(s). You
might need t
o add explicit type casts.
LINE 1: SELECT * FROM a WHERE d=current_date;


so and now think what takes happen in plpgsql functions if you work with
variables.

DECLARE z INTEGER;

calculations

UPDATE a SET b=z WHERE xyz;

GET DIAGNOSTICS rows = ROW_COUNT;
IF rows=0 THEN
ELSE
FOR r IN SELECT * FROM a WHERE b=z....
END IF;



i know that are hypotethical issues but i will show the risk.

On Tue, May 06, 2008 at 11:31:55AM +0200, Daniel Schuchardt wrote:
> demo=# SELECT * FROM a WHERE b=3||'~1';
> a | b
> ---+-----
> 5 | 3~1
> 6 | 3~1
> 7 | 3~1
> (3 rows)

The difference is the use of '||' which will autocast *if* one of the
arguments is text.

I was about to ask it you'd actually tested the examples you posted
because they looked like they should work fine.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIID4OIB7bNG8LQkwRAk7DAJ9fj2Q5nKQ0sW/iShJCcjjsBccWtQCeMhuD
ms070/QcX3lZFYWexYhycwc=
=Cy2q
-----END PGP SIGNATURE-----