[SQL] DateDiff, IsNull?

[Bill]

Hello all,

  I am a newbie to postgreSQL, is it possible to write a "DateDiff",
"IsNull" function work same as the one in SQL Server and are there and
sample out there? And is it possible to override the existing function and
operator like "+" to become a concate?

Regards
Bill



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] on update restrict

[Mister ics]

Hi,

I'm a little confused by the "on update restrict" option in a referential 
integrity constraint. I don't know if i have not understood the meaning of 
this statement or it does not work properly.
I think that if it is specified ON UPDATE RESTRICT in a foreign key 
costraint, the sql-server should not perform updates on the referenced rows. 
Here an example:

testdb=>create table t1 (
id int primary key,
foo int);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't1_pkey' for 
table 't1'
CREATE

testdb=>create table t2 (
id int primary key,
ref int references t1(id) on update restrict);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't2_pkey' for 
table 't2'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY 
check(s)
CREATE

testdb=>insert into t1 values (1,3000);
INSERT 19116 1
testdb=>insert into t2 values (1,1);
INSERT 19117 1

testdb=> update t1 set foo=2900 where id=1;
UPDATE 1<--- for me is wrong, we have updated a row referenced by t2 
with ON UPDATE RESTRICT OPTION

What do you think about ?

Thanks in advance

Regards,
 Silvio



_
Scarica GRATUITAMENTE MSN Explorer all'indirizzo 
http://explorer.msn.it/intl.asp


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] on update restrict

[Jan Wieck]

Mister ics wrote:
> Hi,
>
> I'm a little confused by the "on update restrict" option in a referential
> integrity constraint. I don't know if i have not understood the meaning of
> this statement or it does not work properly.
> I think that if it is specified ON UPDATE RESTRICT in a foreign key
> costraint, the sql-server should not perform updates on the referenced rows.
> Here an example:
>
> testdb=>create table t1 (
>id int primary key,
>foo int);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't1_pkey' for
> table 't1'
> CREATE
>
> testdb=>create table t2 (
>id int primary key,
>ref int references t1(id) on update restrict);
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't2_pkey' for
> table 't2'
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> CREATE
>
> testdb=>insert into t1 values (1,3000);
> INSERT 19116 1
> testdb=>insert into t2 values (1,1);
> INSERT 19117 1
>
> testdb=> update t1 set foo=2900 where id=1;
> UPDATE 1<--- for me is wrong, we have updated a row referenced by t2
> with ON UPDATE RESTRICT OPTION
>
> What do you think about ?

The behaviour is correct according to the SQL specifications.
RESTRICT (as well as NO ACTION) means, you cannot change  the
primary key value of the referenced row. All other values can
be changed of course.

So an attempt to

UPDATE t1 SET id = 2 WHERE id = 1;

is the thing prevented in your above example.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] create function using language SQL

[Gary Stainburn]

Hi all,

As I was reading up on create function etc. while learning plpgsql, I seam to 
remember it stated that as well as plpgsql, that other languages are/will be 
available including using SQL as the language.  However, I cannot find the 
syntax to create a function in SQL.  Specifically, how you return the result.

As an example, how would I create a SQL function to match the plpgsql 
function below?


CREATE FUNCTION getteamno(int4) RETURNS varchar  AS '
DECLARE
 unitno varchar;
BEGIN
select into unitno
tregion || ''/'' ||
to_char(tnumber,''FM000'')
from teams
where tid = $1;
if not found then
  raise exception ''Team % not found'',$1;
  return ;
end if;
return unitno;
END;
' LANGUAGE 'plpgsql';

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Re: Are circular REFERENCES possible ?

[William Courtier]

I denis,

I don't know if you can use a circular REFERENCES, but why you try made a
references before the table is created (customers). You should create the
references after and use the foreign key if circular references does not
work.

William
"Denis Bucher" <[EMAIL PROTECTED]> a écrit dans le message news:
[EMAIL PROTECTED]
>
> Hello !
>
> I have a case where I wanted to do circular REFERENCES, is this
> impossible ?
>
> Just an example where it would be useful :
>
> We deliver to the *shops* of our *customers*.
> We have therefore two tables :
>   - customers (enterprise, financial information, and so on...)
>   - shop (with a name, street, phone number, name of manager)
>
> Now, each shop REFERENCES a customer so that we know
> to which customer belongs a shop.
>
> AND, each customer has a DEFAULT shop for deliveries, i.e. most
> customers only have one shop, or a main shop and many small ones.
> Therefore a customer should REFERENCES the 'main' or 'default' shop.
>
> Which leads to :
>
> CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer
REFERENCES
> customers, ...)
> CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop
integer
> REFERENCES shops, ...)
>
> But this doesn't work ! Postgres complains like "ERROR:  Relation
> 'customers' does not exist"
> when creating 'shops'.
>
> Someone told me I should create a third table, ok, but in this case I
loose
> the total
> control about my logic... Do you have a suggestion ?
>
> Thanks a lot in advance !
>
> Denis
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Re: on update restrict

[Alan Gutierrez]

On Tue, 14 Aug 2001, Jan Wieck wrote:

> Mister ics wrote:
> > testdb=>create table t1 (
> >id int primary key,
> >foo int);

> > testdb=>create table t2 (
> >id int primary key,
> >ref int references t1(id) on update restrict);

> So an attempt to
>
> UPDATE t1 SET id = 2 WHERE id = 1;
>
> is the thing prevented in your above example.

I find it odd that you specify a restiction on one table in the definition of
another table.

Sorry, if this was a double post.

Alan Gutierrez


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] deleting large objects from jdbc

[heatherm]

Hi,

I've been browsing the newsgroup archives and came across the following:

> > > > > Can you see a scenario where a programmer would forget to 
delete the
> > > > > data from pg_largeobject and the database becoming very large 
filled
> > > > > with orphaned large objects?

Does this mean that to delete my large object I need to delete the row 
in my table, and also the row or rows in pg_largeobject. Currently I am 
only deleting the row in my table. I have just been looking at the 
pg_largeobject table, it seems to have more rows than I would expect in 
it. Is the solution to pass these two commands to jdbc?
delete from my_table where lob_oid = 1234
delete from pg_largeobject where loid = 1234

Thanks in advance
Heather.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] problem connecting to pg.

[postgresql]

I am experimenting with running PG 7.1.2 on Mac OSX. I seem to 
have a problem with getting my users to connect. This works 
beautifully when connect to my linux box.

Any ideas?



[localhost:/etc] postgres% /usr/local/pgsql/bin/postmaster -i -p5444 -D 
/users/postgres/pgdata
DEBUG:  database system was shut down at 2001-08-14 09:03:12 EDT
DEBUG:  CheckPoint record at (0, 2571948)
DEBUG:  Redo record at (0, 2571948); Undo record at (0, 0); 
Shutdown TRUE
DEBUG:  NextTransactionId: 750; NextOid: 35106
DEBUG:  database system is in production state
Unable to connect to Ident server on the host which is trying to 
connect to Postgres (IP address 192.168.0.2, Port 113). errno = 
Connection refused (61)
IDENT authentication failed for user 'postgres'



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

RE: [SQL] Re: Are circular REFERENCES possible ?

[Michael Ansley (UK)]

Title: RE: [SQL] Re: Are circular REFERENCES possible ?





-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


The easiest way out is probably to create a relationship entity
called 'default' between customer and shop for the default
relationship.  This way you only have to have one direct
relationship, because the other is expressed through the 'default'
table.


Just a thought...



MikeA




>> -Original Message-
>> From: William Courtier [mailto:[EMAIL PROTECTED]]
>> Sent: 07 August 2001 11:10
>> To: [EMAIL PROTECTED]
>> Subject: [SQL] Re: Are circular REFERENCES possible ?
>> 
>> 
>> I denis,
>> 
>> I don't know if you can use a circular REFERENCES, but why 
>> you try made a
>> references before the table is created (customers). You 
>> should create the
>> references after and use the foreign key if circular 
>> references does not
>> work.
>> 
>> William
>> "Denis Bucher" <[EMAIL PROTECTED]> a écrit dans le message
>> news: [EMAIL PROTECTED]
>> >
>> > Hello !
>> >
>> > I have a case where I wanted to do circular REFERENCES, is this
>> > impossible ?
>> >
>> > Just an example where it would be useful :
>> >
>> > We deliver to the *shops* of our *customers*.
>> > We have therefore two tables :
>> >   - customers (enterprise, financial information, and so on...)
>> >   - shop (with a name, street, phone number, name of manager)
>> >
>> > Now, each shop REFERENCES a customer so that we know
>> > to which customer belongs a shop.
>> >
>> > AND, each customer has a DEFAULT shop for deliveries, i.e. most
>> > customers only have one shop, or a main shop and many small
>> > ones. Therefore a customer should REFERENCES the 'main' or 
>> 'default' shop.
>> >
>> > Which leads to :
>> >
>> > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer
>> REFERENCES
>> > customers, ...)
>> > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY,
>> > id_defaultshop 
>> integer
>> > REFERENCES shops, ...)
>> >
>> > But this doesn't work ! Postgres complains like "ERROR: 
>> > Relation 'customers' does not exist"
>> > when creating 'shops'.
>> >
>> > Someone told me I should create a third table, ok, but in 
>> this case I
>> loose
>> > the total
>> > control about my logic... Do you have a suggestion ?
>> >
>> > Thanks a lot in advance !
>> >
>> > Denis
>> >
>> >
>> > ---(end of 
>> broadcast)---
>> > TIP 1: subscribe and unsubscribe commands go to 
>> [EMAIL PROTECTED]
>> 
>> 
>> 
>> ---(end of 
>> broadcast)---
>> TIP 2: you can get off all lists at once with the unregister
>> command 
>> (send "unregister YourEmailAddressHere" to 
>> [EMAIL PROTECTED])
>> 


-BEGIN PGP SIGNATURE-
Version: PGPfreeware 6.5.3 for non-commercial use 


iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+
hXvHqca0bqE73XY4tmjDq/7v
=2nf2
-END PGP SIGNATURE-




_
This e-mail and any attachments are confidential and may also be privileged and/or copyright 
material of Intec Telecom Systems PLC (or its affiliated companies).  If you are not an 
intended or authorised recipient of this e-mail or have received it in error, please delete 
it immediately and notify the sender by e-mail.  In such a case, reading, reproducing, 
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. 
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free 
from computer viruses or other defects. The opinions expressed in this e-mail and any 
attachments may be those of the author and are not necessarily those of Intec Telecom 
Systems PLC. 

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses. 
__

[SQL] Re: create function using language SQL

[Jeff Eckermann]

The "create function" syntax is the same.  The language syntax is straight
SQL:

CREATE FUNCTION getteamno(int4) RETURNS varchar AS '
SELECT tregion || ''/'' || to_char(tnumber, ''FM000'')
FROM teams
WHERE tid = $1
' LANGUAGE 'sql';

You don't get to build in error checking, but there has to be a reason to
use plpgsql :-)
SQL is non-procedural, after all.
Check the docs: Reference Manual/SQL Commands/Create Function
- Original Message -
From: "Gary Stainburn" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 14, 2001 7:40 AM
Subject: create function using language SQL


> Hi all,
>
> As I was reading up on create function etc. while learning plpgsql, I seam
to
> remember it stated that as well as plpgsql, that other languages are/will
be
> available including using SQL as the language.  However, I cannot find the
> syntax to create a function in SQL.  Specifically, how you return the
result.
>
> As an example, how would I create a SQL function to match the plpgsql
> function below?
>
>
> CREATE FUNCTION getteamno(int4) RETURNS varchar  AS '
> DECLARE
>  unitno varchar;
> BEGIN
> select into unitno
> tregion || ''/'' ||
> to_char(tnumber,''FM000'')
> from teams
> where tid = $1;
> if not found then
>   raise exception ''Team % not found'',$1;
>   return ;
> end if;
> return unitno;
> END;
> ' LANGUAGE 'plpgsql';
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
>


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: [NOVICE] Arithmetic operation on DATE

[Jason Wong]

I know you can do it for days, thus:

   expires = CURRENT_TIMESTAMP + 10

would be 10 days from now. Don't know about months though.

HTH
-- 
Jason Wong
Gremlins Associates
www.gremlins.com.hk


- Original Message - 
From: macky <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, August 13, 2001 5:48 PM
Subject: [NOVICE] Arithmetic operation on DATE


> is it possible to add a date datatype column to a number resulting to a
> date..
> 
> 
> theoretically it should do this,,,
> 
> X is in months
> 
> date + X = date
> 
> -->  2001-08-20 + 6 = 2002-02-20
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Where is pgplsql.so located in 7.1.2?

[Raymond Chui]

When I do

createlang -U postgres -d mydbname pgplsql mydbname
I got can't find /usr/share/pgsql/plpgsql.so error message.

But there is no plpgsql.so file in /usr/share/pgsql directory!
I just installed RPM binary 7.1.2 for Redhat 6.2

In 7.0 the pgplsql.so  file is located in /usr/lib/pgsql/
So I do

createlang -U postgres -d mydbname --pglib=/usr/lib/pgsql pgplsql
mydbname

Then I got
A function named plpgsql_call_handler already exists. Installation
abort.

But createlang -l mydbname shows there is nothing there.
Shall I do
drop function plpgsql_call_handler; ?
Or where I can find plpgsql.so file for 7.1.2 in /usr/share/pgsql/ ?

Please don't tell me I need to compile PostgreSQL by myself order to get
all
*.so files.

--Raymond


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Re: [GENERAL] Re: Mirroring the database?

[Justin Clift]

There's also PostgreSQL Replicator (which I haven't gotten around to
trying) :

http://pgreplicator.sourceforge.net

:-)

Regards and best wishes,

Justin Clift


Allan Engelhardt wrote:
> 
> Ehhh, use dual-ported RAID disks?  (Well, tri-port in your case, but maybe A and B 
>machines are enough.  You still have a (small) single point of failure, but you 
>probably have that anyhow (network switch?).  You'll need some way of brining 
>PostgreSQL up on B when A dies, but that could be a simple script.  Reconnect would 
>of course be manual from the point of the client.).
> 
> Or buy Oracle.
> 
> Replication is at the top of the TODO list: 
>http://www.uk.postgresql.org/docs/todo.html
> 
> Allan.
> 
> PS:
> Maybe SQLB does some of what you want?  http://sourceforge.net/projects/sqlb/  [The 
>documentation is a little opaque...I'm not quite sure what it does, exactly.  Anybody 
>want to comment?]
> 
> Raymond Chui wrote:
> 
> > Does the latest PostgreSQL 7.1.2 support database mirroring?
> >
> > I have machine A, B and C, they all have the same database and tables.
> > Machine A is the operational machine, machine B and C are backup.
> >
> > If users do INSERT, UPDATE and DELETE in machine A, I want have the
> > same SQL statements in machine B and C.
> >
> > 1. I can do pg_dump or COPY every hour.
> > 2. I can use PerlDBI or JDBC open multiple connections for each SQL
> > statement.
> > 3. But I prefer if the system auto mirroring the database, then I can do
> > nothing.
> > All I need is set the auto mirroring configure.
> >
> > Please tell me how to do in 3. above. Thank you in advance!
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] The numbers are not match in 7.1.2

[Raymond Chui]

When I do

select * from pg_database;

I got

datname | ... | datlastsysoid

mydbname | ... | 18539

But When I

cd $PGDATA/data/base/
ls -la

I got
1
18719
18725

Where is 18539 ??!!

I think mydbname is 18725
When I

cd 18725
ls

There are a lot of numbers. How do I figure out which number is which
table_name?

BTW, what is/are the advantage(s) used the numbers instead real text
name like
7.0.x used to do?

--Raymond


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] create function using language SQL

[Tom Lane]

Gary Stainburn <[EMAIL PROTECTED]> writes:
> ... However, I cannot find the 
> syntax to create a function in SQL.  Specifically, how you return the result.

See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/xfunc.html#XFUNC-SQL

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Re: DateDiff, IsNull?

[Tom Lane]

Alan Gutierrez <[EMAIL PROTECTED]> writes:
> On Tue, 14 Aug 2001, Bill wrote:
>> And is it possible to override the existing function and
>> operator like "+" to become a concate?

> Overloading operators? *Please* tell me that this is impossible.

I've got bad news ;-) ... it's pretty easy.

regression=# select 'aa'::text + 'bb'::text;
ERROR:  Unable to identify an operator '+' for types 'text' and 'text'
You will have to retype this query using an explicit cast

regression=# create operator + (procedure = textcat,
regression(# leftarg = text, rightarg = text);
CREATE

regression=# select 'aa'::text + 'bb'::text;
 ?column?
--
 aabb
(1 row)

Whether this is a good idea is another question --- but if Bill's
intent on not using the SQL-standard text concatenation operator "||",
he can do so.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] how to use record type

[Horst Herb]

Help please!

How do I actually insert the variables OLD or NEW or a record type into a 
table from within a trigger?

Like doing the following:

drop table th1;
create table th1(
id serial, 
text text );

drop table th_audit;
create table th1_audit(
ts timestamp default now()
) inherits(th1);

drop function thaudit();
create function thaudit() returns opaque as '
begin
-- I want to insert OLD into th_audit - how do I do this???
return NEW;
end; ' language 'plpgsql';

drop trigger ta on th1;
create trigger ta before delete or update on th1
for each row execute procedure thaudit();

Reagrds,
Horst

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Eh?

[Tom Lane]

Tod McQuillin <[EMAIL PROTECTED]> writes:
> This makes me wonder... in the case of a stored complex view, would it be
> helpful to ask PostgreSQL to spend extra time in query optimisation and
> then cache the result?  Or does it do this already?

I don't see any value in caching plans for views as such.  The planner
considers each query as a whole, which it has to do for performance.
Example:
create view v as select * from foo;
select * from v where bar = 42;
If there's an index on foo.bar, you'd want this query to use it, no?
So the plan has to be formed on the basis of the actual query; there's
no way to pull out the part for a view.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Date: the day before yesterday.

[Jason]

I am trying to retrieve records generated in the passed two days and
encountered difficulties in dealing with the date in Oracle.
Here is the query I try to form:

select * from Table where InputDate>=[the day before yesterday]

I tried "sysdate-2", didn't work.
Any suggestions?
Thanks!

Jason




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: the day before yesterday.

[Carl Karsten]

I am not sure if this will work with Oracle.  I would like to know, so if
you could try out this test :
http://www.foxforum.com/showfullmessage.cfm?messageID=12382

If it does work, then you should be able to do this:

ldDby = date()-2
lnRet = sqlexec( h, "select * from Table where InputDate>=?ldDby" )

Carl Karsten

"Jason" <[EMAIL PROTECTED]> wrote in message
9l19vh$9ku$[EMAIL PROTECTED]">news:9l19vh$9ku$[EMAIL PROTECTED]...
> I am trying to retrieve records generated in the passed two days and
> encountered difficulties in dealing with the date in Oracle.
> Here is the query I try to form:
>
> select * from Table where InputDate>=[the day before yesterday]
>
> I tried "sysdate-2", didn't work.
> Any suggestions?
> Thanks!
>
> Jason
>
>
>



---(end of broadcast)---
TIP 3: 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] DateDiff, IsNull?

[Josh Berkus]

Bill,

>   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> "IsNull" function work same as the one in SQL Server and are there
> and
> sample out there?

I take it that you want to do this to port an exisiting application?

If so, the DateDiff thing has already been covered in Roberto Mello's
"cookbook".

However, ISNULL, last I checked, was a Microsoft Access function and not
supported by MS SQL Server.  If you're porting from MS Access, you'll
need to fix a lot more of your SQL than the functions.  There is,
however, help for MS Access users:
(for both of the above)
http://techdocs.postgresql.org/

> And is it possible to override the existing
> function and
> operator like "+" to become a concate?

How's your C porgramming?  It's an open-source database, you can do
anything for which you have the skill and the time.  The advisability of
doing so, however, is another thing entirely ...

Given that all of the MS SQL Server "peculiarities" are not ANSI SQL
standard, have you given any thought to porting by search-and-replace
script instead of trying to make Postgres support a bunch of
non-standard operators and functions?  It'd be easier.

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco












---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Deadlocks? What happened to MVCC?

[Josh Berkus]

Folks:

I'm getting deadlock errors on one of the operations on my web
application.  It's a function which adds a large number of rows to a
holding table, then updates that set of rows multiple times in order to
present scoring information to the user.

However, the function is deadlocking itself once it's in heavy use.  I
though that MVCC was supposed to supercede deadlocks, unless I
specifically set locks, which I'm not.

Help, anyone?

-Josh Berkus

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Re: Are circular REFERENCES possible ?

[Gary Stainburn]

Hi Mike,

A few people have suggested this, but the thing I don't like (and I think at 
some point William has also stated this) is that doing it this way, you lose 
the logic (and the purity) of the data.  If you allow the cyclic reference, 
then the data behaves -and looks - exactly as it should do. The customer 
refers to the shop, and the shop refers to the customer.

If I remember correctly, one of the cardinal rules of normalising data is 
that all related data (e.g. customer) should be together (one table) - hense, 
the default_shop belongs to the customer table
.  
Relationship table should only be used for n-to-n links.

Gary

On Tuesday 14 August 2001  2:16 pm, Michael Ansley (UK) wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> The easiest way out is probably to create a relationship entity
> called 'default' between customer and shop for the default
> relationship.  This way you only have to have one direct
> relationship, because the other is expressed through the 'default'
> table.
>
> Just a thought...
>
>
> MikeA
>
> >> -Original Message-
> >> From: William Courtier [mailto:[EMAIL PROTECTED]]
> >> Sent: 07 August 2001 11:10
> >> To: [EMAIL PROTECTED]
> >> Subject: [SQL] Re: Are circular REFERENCES possible ?
> >>
> >>
> >> I denis,
> >>
> >> I don't know if you can use a circular REFERENCES, but why
> >> you try made a
> >> references before the table is created (customers). You
> >> should create the
> >> references after and use the foreign key if circular
> >> references does not
> >> work.
> >>
> >> William
> >> "Denis Bucher" <[EMAIL PROTECTED]> a écrit dans le message
> >> news: [EMAIL PROTECTED]
> >>
> >> > Hello !
> >> >
> >> > I have a case where I wanted to do circular REFERENCES, is this
> >> > impossible ?
> >> >
> >> > Just an example where it would be useful :
> >> >
> >> > We deliver to the *shops* of our *customers*.
> >> > We have therefore two tables :
> >> >   - customers (enterprise, financial information, and so on...)
> >> >   - shop (with a name, street, phone number, name of manager)
> >> >
> >> > Now, each shop REFERENCES a customer so that we know
> >> > to which customer belongs a shop.
> >> >
> >> > AND, each customer has a DEFAULT shop for deliveries, i.e. most
> >> > customers only have one shop, or a main shop and many small
> >> > ones. Therefore a customer should REFERENCES the 'main' or
> >>
> >> 'default' shop.
> >>
> >> > Which leads to :
> >> >
> >> > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer
> >>
> >> REFERENCES
> >>
> >> > customers, ...)
> >> > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY,
> >> > id_defaultshop
> >>
> >> integer
> >>
> >> > REFERENCES shops, ...)
> >> >
> >> > But this doesn't work ! Postgres complains like "ERROR:
> >> > Relation 'customers' does not exist"
> >> > when creating 'shops'.
> >> >
> >> > Someone told me I should create a third table, ok, but in
> >>
> >> this case I
> >> loose
> >>
> >> > the total
> >> > control about my logic... Do you have a suggestion ?
> >> >
> >> > Thanks a lot in advance !
> >> >
> >> > Denis
> >> >
> >> >
> >> > ---(end of
> >>
> >> broadcast)---
> >>
> >> > TIP 1: subscribe and unsubscribe commands go to
> >>
> >> [EMAIL PROTECTED]
> >>
> >>
> >>
> >> ---(end of
> >> broadcast)---
> >> TIP 2: you can get off all lists at once with the unregister
> >> command
> >> (send "unregister YourEmailAddressHere" to
> >> [EMAIL PROTECTED])
>
> -BEGIN PGP SIGNATURE-
> Version: PGPfreeware 6.5.3 for non-commercial use 
>
> iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+
> hXvHqca0bqE73XY4tmjDq/7v
> =2nf2
> -END PGP SIGNATURE-


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 


-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Deadlocks? What happened to MVCC?

[Jan Wieck]

Josh Berkus wrote:
> Folks:
>
> I'm getting deadlock errors on one of the operations on my web
> application.  It's a function which adds a large number of rows to a
> holding table, then updates that set of rows multiple times in order to
> present scoring information to the user.
>
> However, the function is deadlocking itself once it's in heavy use.  I
> though that MVCC was supposed to supercede deadlocks, unless I
> specifically set locks, which I'm not.
>
> Help, anyone?

You   must   have  misunderstood  something.  MVCC  does  not
supersede deadlocks, nor can it prevent them. All  MVCC  does
is to help "readers" not beeing blocked by "writers" and vice
versa, by avoiding the need for  shared  "read"  locks.   But
writers  still  need  locks,  so  if  your  application  does
something like

Xact-1: Starts transaction
Xact-2: Starts transaction
Xact-1: Updates row A
Xact-2: Updates row B
Xact-1: Wants to update row B (blocks)
Xact-2: Wants to update row A

then it's a deadlock. A deadlock is a deadlock and remains to
be a deadlock, no matter if you have versioning or not.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Date: the day before yesterday.

[Jan Wieck]

Jason wrote:
> I am trying to retrieve records generated in the passed two days and
> encountered difficulties in dealing with the date in Oracle.
> Here is the query I try to form:
>
> select * from Table where InputDate>=[the day before yesterday]
>
> I tried "sysdate-2", didn't work.
> Any suggestions?

Try harder.

> Thanks!

You're welcome.


Jan

PS: What about ('now'::date - 2) ?


--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] psql connection being reset during function?

[Randall Skelton]

Hi all,

I am running 7.1.2 and I have the unenviable task of cleaning up a
database which has columns:

sgmty  integer,   -- YEAR
sgmtmo integer,   -- MONTH
sgmtd  integer,   -- DAY
sgmth  integer,   -- HOUR
sgmtm  integer,   -- MINUTE
sgmts  float, -- SECOND
+ 190 other floats per row

The data is basically an atmospheric model dump at 1 frame per second. Why
exactly time is stored like this remains a mystery... The values for
seconds resemble a 16 bit float output directly inserted (via libpq) into
the database:

  0.92423414
  1.86999391
  3.21808504
  ...
  4.16384481
 58.08695482
 59.03271459
 59.97847437
 ...

My first thought was simply to add a new column called gmt_timestamp
and write a simple function in plpgsql to update each record.

My first-cut function:

CREATE FUNCTION mydatetime() RETURNS text AS'
   DECLARE
   -- defines a record and text variable
   recRECORD;
   date_time TEXT;

   BEGIN
   -- loop over all entries in path
   FOR rec IN SELECT * FROM path LOOP
 date_time:= ''UPDATE path SET sgmt_timestamp = '' ||
quote_literal(
rec.sgmty   || ''-'' ||
rec.sgmtmo  || ''-'' ||
rec.sgmtd   || '' '' ||
rec.sgmth   || '':'' ||
rec.sgmtm   || '':'' ||
cast(rec.sgmts as integer)
)
|| '' WHERE'' ||
'' sgmty  = '' || quote_literal(rec.sgmty) ||
'' AND sgmtmo = '' || quote_literal(rec.sgmtmo) ||
'' AND sgmtd  = '' || quote_literal(rec.sgmtd) ||
'' AND sgmth  = '' || quote_literal(rec.sgmth) ||
'' AND sgmtm  = '' || quote_literal(rec.sgmtm) ||
'' AND sgmts  = '' || quote_literal(rec.sgmts)
|| '';'';
 EXECUTE date_time;
  END LOOP;

   --return date_time;
   return ''done'';

   END;
   ' LANGUAGE 'plpgsql';

Surely this can be improved upon, but it leads to problem #1.  In the case
of sgmts = 59.97847437 my explicit cast of, 'cast(rec.sgmts as
integer)' creates a problem in that I make a timestamp with '60' in the
seconds column.  A time stamp of this sort is not handled by the postgres
timestamp type and the function falls over.

My revised function explicitly propogates 60 seconds to be a minute, 60
minutes to be an hour, 24 hours to be a day and so on.

CREATE FUNCTION mydatetime() RETURNS text AS'
   DECLARE
   -- defines a record and text variable
   recRECORD;
   year   INTEGER;
   month  INTEGER;
   dayINTEGER;
   hour   INTEGER;
   minute INTEGER;
   second INTEGER;
   addone INTEGER;
   date_time TEXT;
   date_time_two TEXT;

   BEGIN
   -- loop over all entries in atlas3_path
   FOR rec IN SELECT * FROM atlas3_path LOOP
 /* THIS IS A DIRTY HACK!!! Should never have excluded a time stamp;
  * casting allows the seconds to be 60 which is causes
  * problems for the timestamp postgres type.
  * Emperically checked to ensure month does not flip for this
  * dataset (i.e. 1996-01-31 23:59:60 does not occur)
  * Original code has now been modified to
  * include a proper timestamp calculation.
  */

 day := 0;
 hour := 0;
 minute := 0;
 second := 0;

 IF cast(rec.sgmts as integer) > 59 THEN
second := cast(rec.sgmts as integer) - 60;
minute := 1;
 ELSE
second := cast(rec.sgmts as integer);
 END IF;

 IF minute + rec.sgmtm > 59 THEN
minute := minute + rec.sgmtm - 60;
hour := 1;
 ELSE
minute := minute + rec.sgmtm;
 END IF;

 IF hour + rec.sgmth > 23 THEN
hour := hour + rec.sgmth - 24;
day := 1;
 ELSE
hour := hour + rec.sgmth;
 END IF;

 day :=  day + rec.sgmtd;

 date_time:= ''UPDATE atlas3_path SET sgmt_timestamp = '' ||
quote_literal(
rec.sgmty   || ''-'' ||
rec.sgmtmo  || ''-'' ||
day || '' '' ||
hour|| '':'' ||
minute  || '':'' ||
second
)
|| '' WHERE'' ||
'' sgmty   = '' || quote_literal(rec.sgmty) ||
'' AND sgmtmo  = '' || quote_literal(rec.sgmtmo) ||
'' AND sgmtd= '' || quote_literal(rec.sgmtd) ||
'' AND sgmth   = '' || quote_literal(rec.sgmth) ||
'' AND sgmtm = '' || quote_literal(rec.sgmtm) ||
'' AND sgmts = '' || quote_literal(rec.sgmts)
|| '';'';
 EXECUTE date_time;
  END LOOP;

  

Re: [SQL] Deadlocks? What happened to MVCC?

[Tom Lane]

MVCC does not mean "no locks" ... particularly not when UPDATEs are
involved.  You'll need to be more specific about what your function is
doing, but my first thought would be to look for the possibility of
conflicting updates of the same row.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Deadlocks? What happened to MVCC?

[Josh Berkus]

Tom, Jan,

> MVCC does not mean "no locks" ... particularly not when UPDATEs are
> involved.  You'll need to be more specific about what your function
> is
> doing, but my first thought would be to look for the possibility of
> conflicting updates of the same row.

Found the problem after 3 hours and a phone call to PostgreSQL Inc.
Turns out that I had one too-broad UPDATE in a 250-line function ...
feh!

-Josh


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] psql connection being reset during function?

[Tom Lane]

Randall Skelton <[EMAIL PROTECTED]> writes:
> problem #2: While this works perfectly for a small table of 10 entries, it
> crashes the database connection when I try to update 311537 rows

In present releases, plpgsql tends to leak a lot of memory intra-call;
you're probably just running out of memory.  (I think we have cleaned up
the leak problems in the CVS-tip code, but that won't help you unless
you're brave enough to run a snapshot version.)  Consider restructuring
your approach so that the plpgsql function just does a single conversion
and is invoked separately at each row:

UPDATE atlas3_path SET sgmt_timestamp = mydatetime(sgmty,sgmtmo,sgmtd,...);

where mydatetime takes five integers and a float and returns a timestamp.
This way, any memory leaked during plpgsql function execution is
reclaimed when the function exits, so it doesn't build up across rows.

This will probably be much faster than your other approach anyway, since
it doesn't require re-finding each row with a fresh UPDATE.  A function
call is a whole lot cheaper than parsing, planning, and executing a new
query.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Temp tables being written to disk. Avoidable?

[Paul McGarry]

Hi,

I have an application where I am using fulltextindex to
create a searchable index for a table and wish to return
pages of results, ranked in order of relevance. So for any
particular page view I need to know:
a) Total number of results relevant to search (so I can display
page [1] 2 3 4 5 6)
b) The results to be ranked by 'relevance' to search terms.
c) The details for a part of that range (ie 15 or so entries
per page.

Database structure is something like:

==
txdev43=# \d entry_fti
  Table "entry_fti"
 Attribute | Type  | Modifier 
---+---+--
 string| character varying(25) | 
 id| oid   | 
Index: entry_fti_idx

txdev43=# \d entry
  Table "entry"
Attribute |   Type   |   Modifier
--+--+
 entry_id | integer  | default nextval('ent_id_seq'::text)
 name | text | 
 description_html | text | 
 fti_text | text | 
Indices: entry_oid_idx,
 entry_pkey
==
(The entry table is simplified here, real one has 24 columns).

My original plan was to do two/three queries, ie:
==
SELECT COUNT (entry_fti.id) as rating , id 
INTO TEMP TABLE searchtemp 
FROM entry_fti  
WHERE (entry_fti.string ~'^word1' OR entry_fti.string ~'^word2') 
GROUP BY id

SELECT entry.*, searchtemp.rating 
FROM searchtemp, entry 
WHERE entry.oid=searchtemp.id 
ORDER BY rating DESC 
LIMIT 15 OFFSET 0

SELECT count(*) FROM searchtemp; 
(optional, depending on number of rows returned by previous query)
==

This seemed to be the cheapest way to get all the information I need.
However, I noticed a disk access each time I did the search. I fiddled
with the SHARED_BUFFERS and SORT_MEM and discovered they weren't the
problem but that the SELECT INTO was causing the disk access. If I have
multiple searches occuring concurrently I imagine this becoming a 
problem. Can I avoid having the temp table written to disk (unless
required by running out of memory)? The temp table has a tiny lifespan
and needs to be accessed for each subsequent query so there's no
point in writing it to disk unless completely necessary.

For the moment I have moved to a query like:
==
SELECT COUNT (entry_fti.id) as rating , entry_id, name, description_html
FROM entry_fti, entry 
WHERE (entry_fti.string ~'^word1' OR entry_fti.string ~'^word2') 
AND entry_fti.id=entry.oid 
GROUP BY id ,entry_id, name, description_html
ORDER BY rating DESC
==
which seems significantly less elegant (I need to return all rows and
do my 'OFFSET' and 'LIMIT' manually, also bearing in mind that I'm
pulling 20+ cols from the entry table, not the 3 I have here) but 
doesn't cause any disk access as the entry_fti and entry tables sit
in memory.

Hmm, I should to do some proper benchmarking on this rather than
worrying about the noises coming from the disks..

However, even if the original method does turn out to be faster,
I imagine it could be faster still without the disk writes (though
I don't know anywhere near enough about the PostgreSQL internals
for that to be anything other than a gut feeling).

--
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9870 4718 
North Ryde NSW 2113 Fax:   (02) 9878 1755 

This document and any attachments are intended solely for
the named addressee(s), are confidential, and may be subject to
legal professional privilege. Please notify us (on +61-2 9878 1744)
as soon as possible if you have received this document in error.
Any confidentiality or privilege is not waived or lost because this
email has been sent to you by mistake. This document and any
attachments are subject to copyright.  No part of them should be
reproduced or distributed by any means whatsoever without the
prior consent of the copyright owner.  Opentec does not warrant
that this email and any attachments are error or virus free.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] result sets from functions...

[Robert J. Sanford, Jr.]

i'm reading the postgres documentation and i'm specifically
interested in creating stored procedures so that i can keep
as much of the business logic in the database as possible.
while reading 13.1.3 (SQL Functions on Composite Types) in
the Programmer's Guide i come across the phrase...
   "When calling a function that returns a row, we cannot
   retrieve the entire row. We must either project an
   attribute out of the row or pass the entire row into
   another function.
  SELECT name(new_emp()) AS nobody;"

how does this affect calling functions as stored procedures
from the jdbc driver and getting a result set returned bac?
am i allowed to use Connection.PrepareCall() to create a
CallableStatement against a postgres function and receive a
ResultSet that can be iterated over?

thanks!

rjsjr


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] result sets from functions...

[Josh Berkus]

Robert,

> i'm reading the postgres documentation and i'm specifically
> interested in creating stored procedures so that i can keep
> as much of the business logic in the database as possible.
> while reading 13.1.3 (SQL Functions on Composite Types) in
> the Programmer's Guide i come across the phrase...
>"When calling a function that returns a row, we cannot
>retrieve the entire row. We must either project an
>attribute out of the row or pass the entire row into
>another function.
>   SELECT name(new_emp()) AS nobody;"

Unfortunately, PostgreSQL functions do not yet return result sets.  This
is on the "todo" list but unfortunately requires an overhaul of how
postgresql functions work.

-Josh Berkus

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Re: DateDiff, IsNull?

[Bill]

IsNull work like this

SELECT IsNull( user_name, '') FROM user ;

then the query will return empty string when the field user_name is null

Regards
Bill

> > Hello all,
> >
> >   I am a newbie to postgreSQL, is it possible to write a "DateDiff",
> > "IsNull" function work same as the one in SQL Server and are there and
> > sample out there? And is it possible to override the existing function
and
> > operator like "+" to become a concate?
>
> I don't know what IsNull does but:
>
> template1=> select coalesce(NULL, 'Hello');
>  case
> ---
>  Hello
> (1 row)
>
> I do know what DateDiff does:
>
> template1=> select '2001-08-14'::date - '6 weeks'::interval;
> ?column?
> 
>  2001-07-03 00:00:00-05
> (1 row)
>
> Overloading operators? *Please* tell me that this is impossible.
>
> Alan Gutierrez





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] result sets from functions...

[Jan Wieck]

Josh Berkus wrote:
> Robert,
>
> > i'm reading the postgres documentation and i'm specifically
> > interested in creating stored procedures so that i can keep
> > as much of the business logic in the database as possible.
> > while reading 13.1.3 (SQL Functions on Composite Types) in
> > the Programmer's Guide i come across the phrase...
> >"When calling a function that returns a row, we cannot
> >retrieve the entire row. We must either project an
> >attribute out of the row or pass the entire row into
> >another function.
> >   SELECT name(new_emp()) AS nobody;"
>
> Unfortunately, PostgreSQL functions do not yet return result sets.  This
> is on the "todo" list but unfortunately requires an overhaul of how
> postgresql functions work.

As  said,  in  v7.2  we'll have the ability to return cursors
from functions (and I'm looking actually into the ability  of
doing  RETURN AND RESUME).  So there is at least some sort of
workaround on the horizon.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #



_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

RE: [SQL] Temp tables being written to disk. Avoidable?

[Paul McGarry]

Hi Tom,

Thanks for your response, enlightening as always.

> Not at present --- temp tables are not different from real tables,
> except for some naming shenanigans.  So creation of a temp table will
> involve some disk hits.

Ok, would it be a good idea to modify that for the future? Given that
temp tables:-
a) can't be seen by other connections.
b) are likely to be selected upon heavily close to creation time.
c) are likely to be short lived.
is there any reason to move them out to disk unless strictly
necessary (aside from that it may take a fair bit of 
re-engineering and the core developers have other more important 
and/or more interesting things to be getting on with)?

> Do you really *need* a temp table, as opposed to writing a
> sub-SELECT-in-the-FROM-clause?  ISTM that that feature takes care
> of most of the simple notational reasons for wanting a temp table.

I have rewritten the query with such a subselect and it looks
much more elegant than my previous solution:


SELECT rating , entry.*
FROM  entry , 
(
SELECT COUNT(entry_fti.id) as rating, id
FROM entry_fti
WHERE (entry_fti.string ~'^blu' OR entry_fti.string ~'^word2') 
GROUP BY id
[LIMIT X OFFSET Y]
)  vtable
WHERE vtable.id=entry.oid 
ORDER BY rating DESC


The only I'm missing now is the bit equivalent to the
SELECT COUNT(*) FROM searchtemp.

With the temporary table I was effectively trying to
cache that subselect (without the LIMIT) and do two
queries on it, one returning the total number of
rows and one returning information relating to a
LIMITed number of those rows.

Without a temporary table (unless there's a cunning
trick I haven't thought of) it seems I either have
a choice of:

a) Not knowing how many rows match the full text query.
b) Returning the entry details for all the rows even though
   I only want a small number of them.
c) Doing two queries on the entry_fti table

It'll be interesting to see which of b) c) or using a temporary
table is more expensive. I imagine that could vary heavily
on the contents of the tables and the number of concurrent
searches that are occuring.

Thanks again.

--
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9870 4718 
North Ryde NSW 2113 Fax:   (02) 9878 1755  

This document and any attachments are intended solely for
the named addressee(s), are confidential, and may be subject to
legal professional privilege. Please notify us (on +61-2 9878 1744)
as soon as possible if you have received this document in error.
Any confidentiality or privilege is not waived or lost because this
email has been sent to you by mistake. This document and any
attachments are subject to copyright.  No part of them should be
reproduced or distributed by any means whatsoever without the
prior consent of the copyright owner.  Opentec does not warrant
that this email and any attachments are error or virus free.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]