[SQL] More stupid questions: prettyprinting of ints??
hello, the thing i am making is a bookkepping program. This program handles multiple currencies, each currency is formatted a bit differently on from the other. I do store the amounts as int's in the DB... In another table i have sample format strings that could be used to format them... but if there is an inbuild functionality to format currencies, i will gladly take it on the other hand is it possible to make substring operations in postgres (some examples somewhere)? What would be the best way to implement this thing? -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
Re: [SQL] More stupid questions: prettyprinting of ints??
yeah forgot another thing
i want also to write a SQL trigger to parse incoming fields for
preprocessing before DB insertion
in perl i would do a thing like that:
$format = ".999.999,99"; #took out of DB...
#inserting a number into the db
$theval =~ s/\.//g;
if($theval =~/\S+,\d{2}/)
{
$theval =~ s/,//g;
$theval *= 100;
}
#extracting a number from the db
$f= $#format;
$i= $#theval;
$res = "";
while($i>=0)
{
if($format[$f] neq "9")
{
$res = $format[$f].$res
$f--;
}#if($format[$f] neq "9")
$res = $theval[$i].$res ;
$i--;
$f--;
}#while($i>=0)
any chance i could this get out of the frontend into the DB? (as sayd
want to write more frontends so the less is in the frontend and the more
in the DB...)
--
ciao bboett
==
[EMAIL PROTECTED]
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===
the total amount of intelligence on earth is constant.
human population is growing
[SQL] numeric conversions?
Hello, me again :( i just ran into another problem didn't found it neither in the doc nor in the FAQ. the currencies conversion factors i use are stored as float4 in a table, the values to apply on are stores as int4 is the type casting done automaticly? how? can i have an influence of the cast order? is there an easy way to doing the rounding? the operation i want to do is something along this line: int4 = ((int4) float4 * (float4)int4 +.5); -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
[SQL] Return number of rows update in query
Hi, can anybody tell me how to capture the number of rows updated in an update query inside a stored procedure? This doesn't work but hopefully you will see what I mean. CREATE FUNCTION "test" () RETURNS int AS ' DECLARE v_number_of_rows int; BEGIN select into v_number_of_rows update carbon_user set email_confirmed = ''Y'' where email_confirmed = ''Y''; RETURN v_myvar; END; ' LANGUAGE 'plpgsql'; Thanks in advance!!! Shane
Re: [SQL] Like seems to fail !
Yves Martin <[EMAIL PROTECTED]> writes: > base=# select * from persistent_config where key like '/%'; > key | type | value > -+--+--- > (0 rows) What LOCALE are you running in? There are some known problems with LIKE index optimization in some non-ASCII locales. If you drop the index on persistent_config.key, does the problem disappear? regards, tom lane
Re: [SQL] numeric conversions?
> is the type casting done automaticly? how? can i have an influence of > the cast order? is there an easy way to doing the rounding? bf2=# select 1 / 2 from dual; ?column? -- 0 (1 row) bf2=# select 1::float / 2 from dual; ?column? -- 0.5 (1 row) bf2=# select (1::float / 2)::int from dual; ?column? -- 0 (1 row) Also you have the functions round, floor, and ceil, which do what you would expect. -Jonathan
[SQL] pgpl-problem, what's wrong with my loop?
Hello,
once more, i ran into a problem
i got no syntax error, i don't know how to debug, except for raising
exceptions the loop never executes making the select call by hand
with fixed values, returns a result... but the second raise is never passed
with the function...so something seems wrong
CREATE FUNCTION accSum(text,text) RETURNS int4 AS '
DECLARE
col ALIAS FOR $1;
sumup ALIAS FOR $2;
actsum journal.amount%TYPE;
arow journal%ROWTYPE;
conversion float4;
temp float4;
sum int4;
BEGIN
sum := 0;
RAISE NOTICE ''stats %=% '', col,sumup;
FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP
RAISE NOTICE ''% current line: %'', arow.id,arow.amount;
SELECT conv FROM currencies WHERE tag=arow.currency INTO conversion;
temp := conversion*arow.amount+0.5;
sum := sum + temp;
END LOOP;
return sum;
END;
' LANGUAGE 'plpgsql';
as sayd :
fibu=> select accSum('plus','102');
NOTICE: stats plus=102
accsum
0
(1 row)
fibu=> SELECT currency,amount FROM journal WHERE plus=102;
currency | amount
--+
EUR | 10
EUR | 10
EUR | 10
EUR | 10
EUR | 10
(5 rows)
surely some stupid error somewhere.
--
ciao bboett
==
[EMAIL PROTECTED]
http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett
===
the total amount of intelligence on earth is constant.
human population is growing
Re: [SQL] pgpl-problem, what's wrong with my loop?
Bruno Boettcher <[EMAIL PROTECTED]> writes: > CREATE FUNCTION accSum(text,text) RETURNS int4 AS ' > DECLARE > col ALIAS FOR $1; > sumup ALIAS FOR $2; > ... > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP Looks like the WHERE condition is testing for equality between the two parameters of the function. Since evidently that wasn't what you meant to do, perhaps you'd be well advised to choose local-variable names that don't conflict with column names of your tables... regards, tom lane
Re: [SQL] pgpl-problem, what's wrong with my loop?
> FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP My opinion is the problem that you cannot give a column name as a parameter. But I'm not sure. This never worked for me. The thing here happens that you get all rows if and only if $1=$2 (as strings) and if they are not equal, the WHERE clause will stand for constant false. This second case may be the fact for you. You should write different codes for the different col parameters in my opinion. Or you might write a C function which can send arbitrary SQL queries to the backend. Regards, Zoltan
Re: [SQL] pgpl-problem, what's wrong with my loop?
On Mon, Nov 20, 2000 at 06:06:52PM +0100, Kovacs Zoltan Sandor wrote: > > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP > My opinion is the problem that you cannot give a column name as a > parameter. But I'm not sure. This never worked for me. The thing here :( can somebody confirm this? in this case i have to check only 2 cols put if i had more to check, this would be a serious limitation. > You should write different codes for the different col parameters in my > opinion. Or you might write a C function which can send arbitrary SQL > queries to the backend. :D wanted to stay as SQL'is as possible anyway thanks for the answer... -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing
Re: [SQL] pgpl-problem, what's wrong with my loop?
On Mon, 20 Nov 2000, Bruno Boettcher wrote: > On Mon, Nov 20, 2000 at 06:06:52PM +0100, Kovacs Zoltan Sandor wrote: > > > FOR arow IN SELECT currency,amount FROM journal WHERE col=sumup LOOP > > My opinion is the problem that you cannot give a column name as a > > parameter. But I'm not sure. This never worked for me. The thing here > :( can somebody confirm this? in this case i have to check only 2 > cols > put if i had more to check, this would be a serious limitation. I sent this question to the list about 4 months ago without receiving any answers. Jan, could you please help? > > You should write different codes for the different col parameters in my > > opinion. Or you might write a C function which can send arbitrary SQL > > queries to the backend. > :D wanted to stay as SQL'is as possible Me too. PLPGSQL is a good piece of ware. :-) Zoltan
[SQL] Bug or feature
Here's an interesting test of referential integrity. I'm not sure if this is working the way it should or if it is a bug. I'm trying to update the primary key in records that are linked together from the two different tables. My initial assumption was that because of the cascade, I could update the primary key only in the gl_hdr table and it would cascade to the gl_items table. I have two separate updates of gl_items shown below. One updates the key in gl_items explicitly, the other tries to wait and allow the cascade to do it. Only the first one works (try commenting one in/out at a time). Unless I update the glid explicitly in gl_items, I get an RI violation when it tries to update the gl_hdr record. --Test RI in the general ledger drop table gl_hdr; drop table gl_items; create table gl_hdr ( glid int4, hstat varchar(1), constraint gl_hdr_pk_glid primary key (glid) ); create table gl_items ( glid int4, inum int4, istat varchar(1), primary key (glid, inum), constraint gl_items_fk_glid foreign key (glid) references gl_hdr on update cascade deferrable initially deferred ); insert into gl_hdr (glid,hstat) values (1,'w'); insert into gl_items (glid,inum,istat) values (1,1,'w'); insert into gl_items (glid,inum,istat) values (1,2,'w'); select * from gl_hdr h, gl_items i where h.glid = i.glid; begin; --This one works: -- update gl_items set glid = 1000, istat = 'c' where glid = 1; --This one doesn't: update gl_items set istat = 'c' where glid = 1; update gl_hdr set glid = 1000, hstat = 'c' where glid = 1; end; select * from gl_hdr h, gl_items i where h.glid = i.glid; begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
Re: [SQL] Using a postgres table to maintain unique id?
Steve Wampler schrieb: > > Poet/Joshua Drake wrote: > ? > ? ?However, I also use Postgres (7.0.2) throughout this > ? ?application and it seems cleaner to me to keep the current > ? ?id value in a table and just use postgres to provide access > ? ?(with a trigger function to increment the id on access). > ? > ? Why not a sequence? > > Can someone show me how to create (and use) an int8 sequence? > > ? ?Is this reasonable? Is it fast? (I need 10 or more IDs > ? ?generated each second.) Can I avoid having the table > ? ?gradually fill with "old" rows for this entry, and this > ? ?avoid the need to run VACUUM ANALYZE periodically? Throw away all the "hardwired"-stuff and do it with software. I once described an algorithm in one of this lists how to create unique values for clients without minimum interaction with the database. The result: query once in the beginning of your application, generate your id's "offline" at the maximum speed you may have and store your last generated id when your client finished. Superior to all the "hardwired"-database solutions ! Marten
RE: [SQL] how to continue a transaction after an error?
> I would like to insert a bunch of rows in a table in a > transaction. Some of > the insertions will fail due to constraints violation. When > this happens, > Postgres automatically ends the transaction and rolls back > all the previous > inserts. I would like to continue the transaction and issue the > commit/rollback command myself. > > How to do it? > Is there any setting I am missing? > Is it possible at all? Hopefully, we'll have savepoints in 7.2 Vadim
Re: [SQL] Using a postgres table to maintain unique id?
Steve Wampler schrieb: > > Yes, but... > > (1) The application I have is composed of about 50 processes > running on 3 different OS/architectures (Linux/intel, > Solaris/sparc, and VxWorks/ppc). The IDs I need must be > unique across all processes (I suppose one solution would > be to provide each ID with a unique prefix based on the > process that is running, but...) We've build a document management system using this system and the clients all created ids are based on the a kind of high-low algorithm to create unique indices. The indices are unique among all possible clients ... the number of clients does not matter. As I said before: better than any hardwired solution. You have two database queries among the normal lifetime of a client to get the base information to create unique clients .. during the lifetime the ids are created offline and they are garanteed to be unique. Actually we're now in the process to build an object-oriented PPD system and we use the same algorithm again. > > (2) Some of these systems are real-time boxes that might get > rebooted at any moment, or might hang for hardware-related > reasons [I'd like to able to say that all of the processes > could detect imminent failure, but unfortunately, I can't]. > So determining when a client "finishes" is not always possible, > which prevents (he claims) the above solution from claiming > ID uniqueness. > It does not matter until your machines do not reboot every second but even then you may get along for ten or 20 years before you ran out of indices. > (where N might be < 1.0). This, while still not guaranteeing > uniqueness, would at least come pretty close... It would still be > nice to avoid having to VACUUM ANALYZE this table, though, and it The base idea for all of it is simple: The unique id is based on three integer numbers: a) id-1 is a class id number (16 bit ?) b) id-2 is a global-session-number (32 bit): n c) id-3 is a local-session-number (32 bit): x The id-3, id-2 and id-1 are converted to the base 36 and by this they are converted to strings. The result unique id is about 15 characters long. (6+6+3) We need a table to hold pairs of "global-id, local-id", this table is initially empty. When a client starts, it connects to the database, lockes this table and now the following happens: a) if the table is empty, the client uses (1,0) for its own and stores (2,0) for the next client into the table. b) if the table has ONE entry, the client removes the pair (n,x) from the table and stores (n+1,0) into the table. c) if the table has more than one entry, the client takes any entry (normaly the one with the lowest n) from the table and removes it. d) the client unlocks the table Now the client is able to create offline up to 2^32 new unique identifiers. Increasing the numbers above and you get even more possible values. They create unique identifieres like (n,x), (n,x+1), ... If the client reaches this limit during lifetime it does the above again. If the client terminates, it writes it actual pair into this table. Ok, that's it. If you want to have more information ... just contact me. Marten
[SQL] Postgres 7.0.X and arrow keys
Morning. I installed new Mandrake 7.2 and was eager to try new postgres. So I installed it and used pgsql. What surprised me was that the arrow keys wouldn't work anymore as history, instead I get those ascii codes. I was wondering is it the matter of configuration or is it a new feature that one has to live with? Btw, sorry to post it here, but I'm too lazy to order a new list just for this question. Greetings, Antti
[SQL] Re: MySQL -> Postgres dump converter
On Tue, 21 Nov 2000 00:01:33 +0200, Max Fonin said: > Hi. > > MySQL->Postgres dump converter is now available at >http://ziet.zhitomir.ua/~fonin/code/my2pg.pl. > Still beta and bugsome version but working, supports MySQL ENUMs, near the end are >SET emulation. > Please help me to test. > > Max Rudensky. > > i just did a dump of one of our work tables and it produced a lot of functions returned as opaque .. which is ok, except i cant find said shared object anywhere on my sys what is this part of?
