Re: Absolute value of intervals

Tom Lane wrote:
> Sam Mason <sam <at> samason.me.uk> writes:
>> On Tue, Oct 27, 2009 at 11:27:17AM -0300, Joshua Berry wrote:
>>> I couldn't find the operator ' <at> ' for intervals 
> 
>> A simple SQL implementation would look like:
> 
>>   CREATE FUNCTION absinterval(interval) RETURNS interval
>>     IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
>>   CREATE OPERATOR  <at>  ( PROCEDURE = absinterval, RIGHTARG = interval );
> 
>> or is a C version really needed?
> 
> I think this came up again recently and somebody pointed out that the
> correct definition isn't as obvious as all that.  The components of
> an interval can have different signs, so should abs('-1 day 1 hour') be
> '1 day -1 hour' or '1 day 1 hour'?  Or what about corner cases like
> '1 day -25 hours'?

I'm writing this at about 8:35 p.m. New York time on October 31, 2009.  From 
now, adding interval '1 day -25 hours' yields right now, New York time.

-- 
Lew

--

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

Re: Absolute value of intervals

Jeff Davis wrote:
> On Thu, 2009-10-29 at 16:39 -0700, Scott Bailey wrote:
>> But there is some ambiguity around the length of a month. So INTERVAL '1 
>> month - 30 days' =  INTERVAL '0 days' = INTERVAL '-1 month +30 days'. 
>> But when added to a date, it makes no change for months with 30 days, 
>> adds 1 day for months with 31 days and subtracts 2 days for February.

or 1 day for February, 2012.

-- 
Lew

--

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

Re: What order of steps of the postgres when you change information in the table?

Stephan Szabo wrote:

> However, I don't think you can currently have both the property that you
> will never have a failure to commit after your external action runs and
> that your external action can abort the transaction if the external action
> fails.

Yeah. That's what two-phase commit is for - but I don't see how you
could use it in a trigger.

--
Craig Ringer

--

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

Handling Complex queries in PostgreSQL fulltext search

Re: Handling Complex queries in PostgreSQL fulltext search

Le dimanche 1 novembre 2009 à 09:50:29, Gaini Rajeshwar a écrit :
> Hi All,
> 
> Does anyone know how to handle complex queries?
> 
> For Example, If i want to search for something like:
> 
> *(portable battery AND magnetic induction) OR (database management system)*
> 
> This is a sample query and user can specify any kind of complex query in
> this way. I can't ask user to specify *&, |, !* operators. Instead i can
>  ask to use *AND, OR, NOT* operators. If nothing is specified by default
>  *&*operator should be used.
> 
> Is there any way to convert/specify user given query in Fulltext Search.
> 

No, but the tool they use can do the translation work: replacing AND with an 
ampersand, OR with |, etc.

> For Example, In Sphinx I can give the same query as
> 
> *'(portable battery && magnetic induction) | (database management system)'
> 
> *Here i need not to mention the & operator between *portable, battery.
> 
> *Is there any way we can specify this in PostgreSQL Fulltext search (i.e
> without bothering about inserting & operator in between)?
> 

Not yet.

-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

--

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

Re: Postgres alpha testing docs and general test packs

On ons, 2009-10-28 at 14:13 +0000, Thom Brown wrote:
> > http://developer.postgresql.org/pgdocs/postgres/release-8-5.html
> 
> Thanks Adrian.  I just wasn't looking hard enough obviously :)  That
> list still doesn't appear to be explicit enough though as we have
> "Multiple improvements in contrib/hstore, including raising limits on
> keys and values".  What exactly is meant by limit, what was this limit
> before and what has it been raised to?
> 
> Similarly: "Fix encoding handling in binary input function of xml
> type."  What was the problem before?
> 
> And: "Allow the collection of statistics on sequences".  How would
> your average end-user see whether these statistics are being colelcted
> on sequences?  And are these statistics actually used anywhere yet?

I agree some of the release note items could be written in a more useful
way.  But ultimately, we can't elaborate on every code fix in detail.
Concentrate on the new features and try them out.  They should be
documented.  If not, or you can't find the documentation, please report
that.

> I'm not really asking for the answer to those questions.  I'm pointing
> out that it isn't clear (at least to me) how to determine what exactly
> has been fixed in order to test it.  This doesn't apply to everything
> listed as some of it is quite clear, like "pg_dump/pg_restore --clean
> now drops large objects."

You can be reasonably assured that the particular fixes have been tested
and work, unless they are explicitly documented otherwise.  We don't
necessarily need more eyeballs to, say, check that the binary input
function of the xml type has *really* been fixed.

One point of the alpha releases is to test whether nothing else has been
broken by the various fixes, new features, and refactorings.  And you
can check that by running your application on top of the new database
server.  It helps if you have a test suite for your application.  For
example, if the fix of the binary input function of the xml type breaks
your application because it had relied on some undocumented corner case,
now would be good time to find that out.

--

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

Re: Emal reg expression

On ons, 2009-10-28 at 05:45 -0700, Xai wrote:
> i want to create a type for an email field but i'm not good with regx
> can some one help me?

I suggest that you make use of an already written module in some
language such as Perl.  See
http://wiki.postgresql.org/wiki/Email_address_parsing for an example.

--

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

Windowing functions - future plans

Hi,

I was wondering if there are plans to extend the window framing clause to allow the number of rows in the frame
to be specified. 

Something like:

ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING

I did find a comment on the TODO list regarding the framing clause ("Implement full support for window
framing clauses") but the explanation did not mention allowing a number instead of UNBOUND for the frame
definition. 

Oracle and DB2 do support this syntax ;)

Regards
Thomas

--

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

Re: Postgres alpha testing docs and general test packs

2009/11/1 Peter Eisentraut <peter_e <at> gmx.net>:
> On ons, 2009-10-28 at 14:13 +0000, Thom Brown wrote:
>> I'm not really asking for the answer to those questions.  I'm pointing
>> out that it isn't clear (at least to me) how to determine what exactly
>> has been fixed in order to test it.  This doesn't apply to everything
>> listed as some of it is quite clear, like "pg_dump/pg_restore --clean
>> now drops large objects."
>
> You can be reasonably assured that the particular fixes have been tested
> and work, unless they are explicitly documented otherwise.  We don't
> necessarily need more eyeballs to, say, check that the binary input
> function of the xml type has *really* been fixed.

Fair enough. :)  It's my experience in testing that makes me want to
test particular things to death.

> One point of the alpha releases is to test whether nothing else has been
> broken by the various fixes, new features, and refactorings.  And you
> can check that by running your application on top of the new database
> server.  It helps if you have a test suite for your application.  For
> example, if the fix of the binary input function of the xml type breaks
> your application because it had relied on some undocumented corner case,
> now would be good time to find that out.

Yeah, I should realise this is just an alpha release really.
Unfortunately I'd imagine that most places using PostgreSQL in
production won't be participating in alpha testing at all as there's
no call for it.

No doubt I can find what I'm looking for, even if it's looking through
the commit log.  But I guess that's your point, I probably don't need
to do that.

At the moment Gentoo doesn't even want for format my primary partition
(some DRDY error), so I still haven't had the chance to give this a
go. :(

Thom

--

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

Re: Forms generator ?

Stuart Adams wrote:
>  Looking for a forms generator for a web based UI for
>  entering/modifiying/viewing a table's records.
> 
>  Any recommendations ???
> 
> Thanks,
>    Stuart

I've been using OpenOffice Base to hack something needed in a hurry. Not 
bad with the native SDBC driver on Debian i386, problems on SPARC and 
PPC (form creation wizard broken, ODBC suspect) but I've not had time to 
investigate properly.

To do the job properly I'd be using Lazarus.

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--

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