Re: [SQL] Round Numeric Type
Numeric(9,2)2006/10/31, Andrew Sullivan <[EMAIL PROTECTED]>: On Tue, Oct 31, 2006 at 05:35:17PM -0200, Ezequias Rodrigues da Rocha wrote:> Hi list,>> I did a trigger to update a points on a table but in some cases the> PostgreSQL does a round of my Numeric like. >> If >= 0.5 so postgresql puts 1> If < 0.5 so postgresql puts 0>> Did anybody knows if it is possible to control this matter ?Sounds like your datatype doesn't match your input, and that you've got a type that rounds. What's the datatype you're putting into?A--Andrew Sullivan | [EMAIL PROTECTED]Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath."--Damien Katz---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Atenciosamente (Sincerely)Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-A pior das democracias ainda é melhor do que a melhor das ditadurasThe worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] record datatype comparisons
On Tue, Oct 31, 2006 at 11:45:00PM +0200, Volkan YAZICI wrote: > > > > Note the column headers. They're differently shaped. Because > > pseudotype record doesn't have a shape, equality doesn't make sense, > > so you need two shapes that are already identical, so they can use > > the matching rules for that. > > Can you be more verbose please? I couldn't understand what you mean with > "shape". It's an analogy, really. The datatype numeric() for instance, is determined. Because it's determined, you can have determinate rules for comparing one to another. But the pseudotype record doesn't work that way. It can accept whatever you put in there, which is why you can define a variable as type record, and then select from different tables (or even different datatypes!) to it in plpgsql: it accommodates these different things. The trade-off is that comparing a record to another record won't always work: because what a record is is not pre-determined, you can't have determinate rules for comparing one record to another. And without determinate rules, you can't have an equality operator. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] record datatype comparisons
On Tue, Oct 31, 2006 at 11:58:08PM +0200, Volkan YAZICI wrote:
> On Oct 31 06:49, Alvaro Herrera wrote:
> > > select
> > > (select (1,2))
> > > is distinct from
> > > (select (1,2))
> > > ;
> > alvherre=# select
> > row(1,2)
> > is distinct from
> > row(1,2)
>
> What's the difference between "SELECT (1, 2);" and "SELECT ROW(1, 2);"?
It's not the difference between SELECT (1,2) and SELECT ROW(1,2)
that's relevant, but the difference between SELECT (SELECT (1,2)) and
SELECT ROW(1,2).
The row's datatype(s) is(are) defined. The record's datatype isn't.
So you can have an equality operator for the row. You can see this
from the error message when you do this:
testing=# SELECT ROW(1,2) is distinct from ROW ('a','b');
ERROR: invalid input syntax for integer: "a"
This is part of the subtle difference between the record and row
datatypes.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] Statement Triggers and access to other rows in the transaction
I have tables representing a ledger system and the accounts on which it operates. I want to do 2 things to all transactions altering the ledger tables. First, all of the inserts into the ledger tables should be balanced ( They should sum to 0 ). If not I want to abort the transaction. Second, if the transaction is balanced I want to update the account balance on the account tables. In other database systems that I have used when you have a statement level trigger you get access to the inserted/updated/deleted rows from that transaction as "virtual" tables within the trigger that you can manipulate. I am currently using plpgsql to write my triggers. I have not been able to figure out the "right" way to do this in PostgreSQL and would appreciate any advice. Thanks, Craig ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] record datatype comparisons
On Wed, Nov 01, 2006 at 11:00:04PM +0200, Volkan YAZICI wrote: > But there should be a way to reach the types of the attributes of the > record data type. Otherwise, how would it be possible to place it into > another tuple as an attribute. Well, surely there is, but that has to happen at assignment time, AFAICT. So you can decide what the record is on the basis of the datatype, but you can't write an equality operator, because you don't know what the general rule is going to be in advance. At least, that's as far as I'm able to understand how to do this. But I'm really past my Latin at this point, because even if I looked at the code that supports the record type, I wouldn't understand it. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] record datatype comparisons
On Nov 01 09:28, Andrew Sullivan wrote: > On Tue, Oct 31, 2006 at 11:45:00PM +0200, Volkan YAZICI wrote: > > > Note the column headers. They're differently shaped. Because > > > pseudotype record doesn't have a shape, equality doesn't make sense, > > > so you need two shapes that are already identical, so they can use > > > the matching rules for that. > > > > Can you be more verbose please? I couldn't understand what you mean with > > "shape". First, thanks for your answer. > It's an analogy, really. The datatype numeric() for instance, is > determined. Because it's determined, you can have determinate rules > for comparing one to another. > > But the pseudotype record doesn't work that way. It can accept > whatever you put in there But there should be a way to reach the types of the attributes of the record data type. Otherwise, how would it be possible to place it into another tuple as an attribute. And AFAIK, there're some (hardcoded) ways to get TupleDesc of a record data type. From this point of view, as long as you're able to get the TupleDesc of a record data type, you should have been able to compare it with any other row/record data type. OTOH, let's assume that we have no idea about attributes of a record data type. So how does PostgreSQL manage to output it without knowing attributes' typoutput functions due to their data type OIDs? That sounds like a contradiction to me. > which is why you can define a variable as type record, and then select > from different tables (or even different datatypes!) to it in plpgsql: > it accommodates these different things. IMHO, plpgsql's record data type is a totally different thing. > The trade-off is that comparing a record to another record won't > always work: because what a record is is not pre-determined, you > can't have determinate rules for comparing one record to another. > And without determinate rules, you can't have an equality operator. IIRC, there's some kind of type registration mechanism in the PostgreSQL internals while forming a record data type. (I'm not sure but, IIRC, BlessTupleDesc() registers the TupleDesc into somewhere, so it'll be able to reach its attributes through its registered TupleDesc in the future.) Can you point me to related source/README files in the PostgreSQL source? I read a little bit about it in the backend/parser/*.c and couldn't see the distinction you explained. Maybe I was looking at wrong lines. Regards. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
