[BUGS]
-- -- Ing. Marcos Luís Ortíz Valmaseda -- -- FreeBSD Fan/User -- -- http://www.freebsd.org/es -- -- Linux User # 418229-- -- Database Architect/Administrator -- -- PostgreSQL RDBMS -- -- http://www.postgresql.org -- -- http://planetpostgresql.org-- -- http://www.postgresql-es.org -- -- Data WareHouse -- Business Intelligence Apprentice -- -- http://www.tdwi.org-- -- Ruby on Rails Fan/Developer-- -- http://rubyonrails.org -- Comunidad Técnica Cubana de PostgreSQL http://postgresql.uci.cu Centro de Gestión de Datos (DATEC) Contacto: Correo: centa...@uci.cu Telf: +53 07-837-3737 +53 07-837-3714 Universidad de las Ciencias Informáticas http://www.uci.cu -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5398: psql gives syntax error on .sql files with UTF-8 BOM
The following bug has been logged online: Bug reference: 5398 Logged by: Peter Jukel Email address: pju...@triplehash.com PostgreSQL version: 8.4 Operating system: Mac OSX 10.5.8 Description:psql gives syntax error on .sql files with UTF-8 BOM Details: The psql command gives syntax errors when fed .sql files that are saved with encoding "UTF-8" and contain a BOM (byte order marker). When the same file is saved using the encoding "UTF-8, no BOM", the syntax error is removed. The irony is that the query tool supplied with PostGres (PGAdmin) saves with the BOM. So it would appear to me that any script written in this tool would display a syntax error. Regards Peter -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5400: Columns count mismatch in RULE with subquery
The following bug has been logged online: Bug reference: 5400 Logged by: Teodor Buchner Email address: t.buch...@autoguard.pl PostgreSQL version: 8.4.1 Operating system: Linux Description:Columns count mismatch in RULE with subquery Details: When SELECT INSERT syntax is used to pump data in a rule, an error appears during creation of this rule if only SELECT has more than one argument. ERROR: INSERT has more target columns than expressions Stan SQL:42601 Znak:137 CREATE OR REPLACE RULE move_iteration_1 AS ON INSERT TO a.iteration DO ALSO INSERT INTO b.iteration(id,date_period_begin) SELECT (NEW.id,NEW.date_period_begin) FROM a.iteration; The example is dummy as INSERT VALUES can be used here but it is impossible to build any complex subquery instead. Reduction to a single variable (removing date_period_begin from INSERT/SELECT) removes this error. Rgds TB -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
Chris Travers wrote: > Just thinking about the more general problem and how things could > be handled more gracefully... Sure, but in the meantime, consider: test=# select '12'::money * '2'::numeric; ?column? -- $24.00 (1 row) test=# select '24'::money / '2'::numeric; ?column? -- $12.00 (1 row) test=# select '24'::money / '12'::money; ERROR: operator does not exist: money / money LINE 1: select '24'::money / '12'::money; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. So we support: a * b = c c / b = a but don't even *think* about c / a = b ??? The OP just wanted to add some symmetry to this, so that the existing class could handle a not-uncommon use case more easily. As far as I can see, the implementation of this operator could convert two int64 values to numeric values and perform numeric division to get the result. (I was going to mark the TODO as an easy one.) I don't see how this change would affect what you want to do, one way or the other. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5400: Columns count mismatch in RULE with subquery
"Teodor Buchner" writes: > CREATE OR REPLACE RULE move_iteration_1 AS ON INSERT TO a.iteration DO ALSO > INSERT INTO b.iteration(id,date_period_begin) > SELECT (NEW.id,NEW.date_period_begin) FROM a.iteration; Your use of parentheses in the SELECT targetlist is incorrect (and would be with or without the RULE context). What this is trying to do is insert a single composite column into the target table. You want INSERT INTO b.iteration(id,date_period_begin) SELECT NEW.id,NEW.date_period_begin FROM a.iteration; regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
On Mar 31, 2010, at 7:07 AM, Kevin Grittner wrote:
> (I was going to mark the TODO as an easy one.)
I thought it would be pretty simple, too, so I decided to go ahead and write
and test it as an external module.
I think the function definition could be pasted directly into an appropriate
place in src/backend/utils/adt/cash.c, if someone wants to add it to the main
code base. The SQL to load it would need to be modified somewhat to fit into
postgres.bki.
Here is the C source:
#include
#include
#include
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(cash_div_cash);
/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cashdividend = PG_GETARG_CASH(0);
Cashdivisor = PG_GETARG_CASH(1);
float8 quotient;
if (divisor == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}
-
And here is the SQL to load it (assuming it has been compiled as a dynamically
loadable module named divide_money and placed in the library directory on the
server):
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE
AS '$libdir/divide_money', 'cash_div_cash';
CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
Andy Balholm wrote: > quotient = (float8)dividend / (float8)divisor; > PG_RETURN_FLOAT8(quotient); That was my first inclination, but the fact that two different people talked about using division by '1'::money as a way to convert money to another type has me nervous about using an approximate type. Any chance you could rework it using numeric? I know it's less trivial that way, but unless we provide a cast to numeric, I'm afraid people will use the above trick, assign it to a numeric variable or column, and then wonder why they've lost precision. Or I guess we could leave this as you've written it and add support for a cast from money to numeric. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
On Mar 31, 2010, at 11:01 AM, Kevin Grittner wrote: > That was my first inclination, but the fact that two different > people talked about using division by '1'::money as a way to convert > money to another type has me nervous about using an approximate > type. Any chance you could rework it using numeric? I know it's > less trivial that way, but unless we provide a cast to numeric, I'm > afraid people will use the above trick, assign it to a numeric > variable or column, and then wonder why they've lost precision. > > Or I guess we could leave this as you've written it and add support > for a cast from money to numeric. It probably is wiser to rewrite it with the numeric type. A cast from money to numeric is theoretically ambiguous (the result could be either dollars or cents), although most people would expect dollars. I'll see if I can rewrite it with a return type of numeric. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
> Or I guess we could leave this as you've written it and add support
> for a cast from money to numeric.
I tried rewriting my function to use numeric, but I discovered that numeric
division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result
in an infinite loop.) So I went back to my float8 version and wrote a cast from
money to numeric.
Here is my C source code now:
#include
#include
#include
#include
#include
PG_MODULE_MAGIC;
extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cash_div_cash);
/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cashdividend = PG_GETARG_CASH(0);
Cashdivisor = PG_GETARG_CASH(1);
float8 quotient;
if (divisor == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}
PG_FUNCTION_INFO_V1(cash_numeric);
/* cash_numeric()
* Convert cash to numeric.
*/
Datum
cash_numeric(PG_FUNCTION_ARGS)
{
Cashmoney = PG_GETARG_CASH(0);
int fpoint;
int64 scale;
int i;
Numeric result;
Datum amount;
Datum numeric_scale;
Datum one;
struct lconv *lconvert = PGLC_localeconv();
/*
* Find the number of digits after the decimal point.
* (These lines were copied from cash_in().)
*/
fpoint = lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint = 2;
scale = 1;
for (i = 0; i < fpoint; i++)
scale *= 10;
amount= DirectFunctionCall1(&int8_numeric, Int64GetDatum(money));
one = DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
numeric_scale = DirectFunctionCall2(&numeric_div, one, numeric_scale);
result = DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount,
numeric_scale));
result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; /* Display the right
number of decimal digits. */
PG_RETURN_NUMERIC(result);
}
And here is the SQL it takes to load it:
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';
CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';
CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);
CREATE CAST (money AS numeric) WITH FUNCTION cash_numeric(money) AS ASSIGNMENT;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
Andy Balholm writes: > I tried rewriting my function to use numeric, but I discovered that numeric > division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result > in an infinite loop.) So I went back to my float8 version and wrote a cast > from money to numeric. That's hardly an improvement if you're concerned about lack of exactness. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] dividing money by money
> That's hardly an improvement if you're concerned about lack of > exactness. I know; I lose a couple of digits by using float8 instead of numeric, but it's much simpler and faster, and if it returned numeric people would _think_ it was exact. And if we have a cast to numeric, people who want those extra digits can cast to numeric before dividing. But I do still have the numeric code that I tried, so if that's how people want to do it, I can provide it. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5398: psql gives syntax error on .sql files with UTF-8 BOM
"Peter Jukel" wrote: > PostgreSQL version: 8.4 > > The psql command gives syntax errors when fed .sql files that are saved with > encoding "UTF-8" and contain a BOM (byte order marker). When the same file > is saved using the encoding "UTF-8, no BOM", the syntax error is removed. It will be improved in 9.0, the next release. Ignore leading UTF-8-encoded Unicode byte-order marker in psql http://developer.postgresql.org/pgdocs/postgres/release-9-0.html#AEN98917 Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5388: bortdagos
On Thu, Mar 25, 2010 at 11:13 AM, bortdagos wrote: > > The following bug has been logged online: > > Bug reference: 5388 > Logged by: bortdagos > Email address: bortda...@ucla.edu > PostgreSQL version: bortdagos > Operating system: bortdagos > Description: bortdagos > Details: > > 1980 anthropogenic google environmental depend trends geoengineering Thanks for the report. Will fix. ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5388: bortdagos
On Wed, Mar 31, 2010 at 10:46 PM, Robert Haas wrote: > On Thu, Mar 25, 2010 at 11:13 AM, bortdagos wrote: >> >> 1980 anthropogenic google environmental depend trends geoengineering > > Thanks for the report. Will fix. > > no, let it for next version... then we can announce 10.0 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
