Re: [SQL] Cannot build PL/Perl ...

[Mark Nielsen]

cd /usr/local/src
lynx --source http://www.tcu-inc.com/perl5.6.1.tgz > perl-5.6.1.tgz
tar -zxvf perl-5.6.1.tgz
cd perl-5.6.1
rm -f config.sh Policy.sh
sh Configure

Change the default prefix to "/usr" instead of "/usr/local". Also, when it asks the 
question "Build a
shared libperl.so (y/n) [n] ", answer y. Press enter for any other question. 

make
make install

Then I downloaded and install postgresql.
./configure --prefix=/usr/local/pg711 --with-perl --with-tcl --with-CXX --with-python 
--enable-odbc 

cd /usr/local/src/postgresql-7.1.1/src/interfaces/perl5
perl Makefile.PL
make 
make install

ln -s /usr/lib/perl5/5.6.1/i686-linux/CORE/libperl.so \
  /usr/local/pg711/lib/libperl.so
su -c 'createlang plperl template1' postgres

Then I started psql as postgres, and ran teh create language command. 


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

Re: [SQL] execute client application from PL/pgSql

[datactrl]

Thank You Jeff,
What is phpPgAdmin and where can get it?

Jack

- Original Message - 
From: "Jeff MacDonald" <[EMAIL PROTECTED]>
To: "Jack" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, May 12, 2001 2:28 AM
Subject: Re: [SQL] execute client application from PL/pgSql


> you could hack the pg_dump bit out of phpPgAdmin
> i think the license permits it.
> 
> just my 2 cents.
> 
> jeff
> 
> On Wed, 9 May 2001, Jack wrote:



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

Re: [SQL] Re: How to store gif or jpeg? Thanks!

[Mathijs Brands]

On Tue, May 08, 2001 at 03:16:53PM +0200, Sylte allegedly wrote:
> > src/interfaces/jdbc/example/ImageViewer.java
> 
> Somewhere on the internet Please be more specific

It's part of the PostgreSQL source. You can download the PostgreSQL
source from www.postgresql.org...

Regards,

Mathijs
-- 
And the beast shall be made legion. Its numbers shall be increased a
thousand thousand fold. The din of a million keyboards like unto a great
storm shall cover the earth, and the followers of Mammon shall tremble.

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

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

Re: [SQL] number of days in a month

[Kaare Rasmussen]

>  does it exist a date function  to determine the number of days in a
>  Select, knowing a specifique date ?

January, 2001: 

select '2001-2-1'::datetime - '2001-1-1'::datetime; 

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 14.00-18.00Web:  www.suse.dk
2000 FrederiksbergLørdag 11.00-17.00   Email: [EMAIL PROTECTED]

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

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

Re: [SQL] Is this possible?

[Roberto Mello]

On Tue, May 08, 2001 at 09:16:56PM -0400, Wei Weng wrote:
> I have a table that has a serial for primary key. Is it possible to get
> the new available primary key right after I insert a row of new entry?

Yeah. Se the documentation on triggers.

-Roberto
-- 
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
   http://www.sdl.usu.edu - Space Dynamics Lab, Developer
"Data! I thought you were dead!" "No, Sir. I rebooted."

---(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] Postgres function library

[Roberto Mello]

On Fri, May 11, 2001 at 08:48:01AM +0200, Lajtos Mate wrote:
> Hi all,
> 
> I read the post from Josh Berkus where he (at the end of the message)
> mentions a 'function library'.
> Is that library available for any developer or it's a proprietary one?
> If it's public, can I post functions there?

It's a "cookbook" of functions that we are trying to gather. Each
function submitted has its own license specified by its author. Most of
them are licensed under the GPL or BSD. Many are public domain.
And yes, PLEASE post functions there. The more we have, the better. I
am going to improve the site in a few days.

http://www.brasileiro.net/postgres

-Roberto
-- 
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
   http://www.sdl.usu.edu - Space Dynamics Lab, Developer
As easy as pi=3.14159265358979323846264338327950288419716939937511

---(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] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

[Tom Lane]

"David D. Kilzer" <[EMAIL PROTECTED]> writes:
> [ wants to write an aggregate function that returns its last input ]

The SQL model of query processing has a very definite view of the stages
of processing: first group by, then aggregate, and last order by.  Tuple
ordering is irrelevant according to the basic semantics of the language.
Probably the SQL authors would have left out ORDER BY entirely if they
could have got away with it, but instead they made it a vestigial
appendage that is only allowed at the very last instant before query
outputs are forwarded to a client application.

Thus, it is very bad form to write an aggregate that depends on the
order it sees its inputs in.  This won't be changed, because it's part
of the nature of the language.

In PG 7.1 it's possible to hack around this by ordering the result of
a subselect-in-FROM:

SELECT orderedagg(ss.x) FROM (select x from tab order by y) ss;

which is a gross violation of the letter and spirit of the spec, and
should not be expected to be portable to other DBMSes; but it gets the
job done if you are intent on writing an ordering-dependent aggregate.

However, I don't see any good way to combine this with grouping, since
if you apply GROUP BY to the output of the subselect you'll lose the
ordering again.

>   SELECT r.personid   AS personid
> ,SUM(r.laps)  AS laps
> ,COUNT(DISTINCT r.id) AS nightsraced
> ,(SELECT r.carid
> FROM race r
>WHERE r.personid = 14 
> ORDER BY r.date DESC
>LIMIT 1)   AS carid
> FROM race r
>WHERE r.personid = 14
> GROUP BY r.personid
> ORDER BY r.date;

This is likely to be reasonably efficient, actually, since the subselect
will be evaluated only once per output group --- in fact, as you've
written it it'll only be evaluated once, period, since it has no
dependencies on the outer query.  More usually you'd probably do

,(SELECT r2.carid
FROM race r2
   WHERE r2.personid = r.personid
ORDER BY r2.date DESC
   LIMIT 1)   AS carid

so that the result tracks the outer query, and in this form it'd be
redone once per output row.

regards, tom lane

---(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] execute client application from PL/pgSql

[clayton cottingham]

Jack wrote:
> 
> Is that possible to execute a client application from server site by
> PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS,
> or is there any Windows version of  all Client Applications come from
> PostGreSQL V7.1?
> 
> Jack
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster


afaik 
if you have tcl/tk on windows 
should you not be able to run the pgaccess?

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

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

Re: [SQL] execute client application from PL/pgSql

[Jeff MacDonald]

you could hack the pg_dump bit out of phpPgAdmin
i think the license permits it.

just my 2 cents.

jeff

On Wed, 9 May 2001, Jack wrote:

> Date: Wed, 9 May 2001 09:45:46 +1000
> From: Jack <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: [SQL] execute client application from PL/pgSql
> 
> Is that possible to execute a client application from server site by
> PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS,
> or is there any Windows version of  all Client Applications come from
> PostGreSQL V7.1?
> 
> Jack
> 
> 
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 


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

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

[SQL] create type and domains

[Valerio Santinelli]

I'm trying to simulate domains in PostgreSQL using 
the create type function, but I cannot find how the input and output functions 
for standard types are called. Is there anyone who can help me ?
Thanks!
 
--Valerio 
Santinelli

[SQL] Re: [HACKERS] Problems in porting from Oracle to Postgres

[Roberto Mello]

This is more appropriate for the pgsql-sql list, so im forwarding it
that way. The hackers list is for other purposes.

On Fri, May 11, 2001 at 12:24:25PM +0530, Amit wrote:
> 
> 1> There is a code in Oracle like
> 
>   Type Tstate is table of number(9)
> index by binary_integer;
> 
> To define a runtime table, basically it works like a array, How can it
> be possible in Postgres SQL,
> I have tried create temp table But it not works..
> Is there any way to use arrays.

It'd be much easier to help you if you posted the function/procedure
you're trying to port. Just one line is harder.
 
> 2> There is one function in Oracle Executesql '...' to execute
> and what i got in Postgres is Execute immediate '.'
> But it is giving error at Execute.

Again, you're giving way too little detail. What error? What are you
trying? Without this, it's very hard to help.

-Roberto

-- 
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
   http://www.sdl.usu.edu - Space Dynamics Lab, Developer
Cannot open CATFOOD.CAN - Eat logitech mouse instead (Y/n)?

---(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: Order by email address by domain ?

[Jeff Eckermann]

???
I don't think anyone suggested adding a new builtin function.
Yes, your suggestion is good for an occasional use.
Where this kind of functionality is likely to be needed on a continuing
basis, my experience is that wrapping the code up in a custom function is
easier and cleaner than writing it out every time.  Saves a lot of typing,
not to mention the risk of typos (which could give spurious results without
being obvious about it).  Also, a function allows for indexing on that
value, which can be a great aid to performance.
I have found that sometimes it is better to just add a column or two to the
table to contain the needed key, because with large amounts of data that can
be much quicker.  Doing this too much though can lead to a cluttered
database, and a loss of clarity about just what all of those extra fields
are for...  It's a judgement call.
Just my $0.01 (That's $0.02 Australian :-))

> -Original Message-
> From: Frank Bax [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, May 10, 2001 8:53 PM
> To:   [EMAIL PROTECTED]
> Cc:   [EMAIL PROTECTED]
> Subject:  Re: Order by email address by domain ?
> 
> Why is everyone else suggesting new functions?  This works (in 6.5.3):
> 
>ORDER BY lower(substring(email from position('@' in email)+1 )),
> lower(email)
> 
> remove the lower() functions if you don't need them (I had mixed case
> addresses).
> 
> I am guessing/assuming that it's cheaper to just use entire email address
> in second key rather than extract before the '@' character.
> 
> Frank
> 
> At 08:37 PM 5/10/01 +0200, you wrote:
> >Hi,
> >
> >I just want to order by a listing of email address by domain like :
> >
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >
> >Is it possible and how ?
> >
> >Thanks !
> >-- 
> >Hervé Piedvache
> >
> >Elma Ingenierie Informatique
> >6, rue du Faubourg Saint-Honoré
> >F-75008 - Paris - France 
> >http://www.elma.fr
> >Tel: +33-1-44949901
> >Fax: +33-1-44949902 
> >Email: [EMAIL PROTECTED]
> >
> >---(end of broadcast)---
> >TIP 6: Have you searched our list archives?
> >
> >http://www.postgresql.org/search.mpl
> >
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

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

[SQL] Re: Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Orac le ROWNUM = PSQL ???

[J.H.M. Dassen (Ray)]

Robert Beer <[EMAIL PROTECTED]> wrote:
> Oracle has a ROWNUM pseudo column that works like this ...
> TEST>select callid, rownum from cs_calls where rownum < 5;

> Is there something like this in PSQL?

SELECT callid FROM cs_calls LIMIT 4;

HTH,
Ray
-- 
Give a man a fire, he's warm for a day.  Set a man on fire, he's warm for
the rest of his life.


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

[SQL] Is this possible?

[Wei Weng]

I have a table that has a serial for primary key. Is it possible to get
the new available primary key right after I insert a row of new entry?

Thanks

Wei

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

[SQL] Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

[David D. Kilzer]

Okay, I found a workaround for PostgreSQL 7.0.3 for the specific query I
was working on below, but I'd still like to know if an ORDER BY clause
will ever be allowed with an aggregated SELECT statement, and whether it
would generally be more efficient to use an ORDER BY in the situation
described below instead of a subselect query.

Also, I realized after I sent the original message below that I could 
work around the "no ORDER BY, LIMIT in subselect" limitation in 
PostgreSQL 7.0.x using a subselect within a subselect, but then I'd
just be abusing the database engine, not finding a real-world 
solution.  :^)

Thanks!

Dave


On Mon, May 07, 2001 at 12:27:59PM -0500, David D. Kilzer wrote:

> NOTE: I did a moderate search through the PgSQL mail list archives, 
> but couldn't find an example of a question where both ORDER BY and
> aggregation were used.  In all examples it was possible to use either
> one or the other with a possible subselect.  [Keep reading...I
> discovered how to do this with subselects, but I'm wondering if (1) what
> I'm doing with ORDER BY and aggregation will ever be allowed and (2)
> whether the subselect solution is much more inefficient than being able
> to order a pre-aggregate set.]
> 
> I need to query a table and return aggregated results.  The aggregation
> occurs many ways (I'm joining 7 tables and returning 19 columns
> currently):
> 
>   o Columns in a GROUP BY clause
>   o SUM() functions
>   o COUNT() functions
> 
> However, I wrote a new FUNCTION and a new AGGREGATE to return the *last*
> value of a field during the aggregation process (see below).  This means
> that I would like to use an ORDER BY clause with the SELECT ... GROUP BY
> statement before the results are aggregated.
> 
> DROP FUNCTION "lastint4" (int4, int4);
> CREATE FUNCTION "lastint4" (int4, int4)
> RETURNS int4
>  AS 'BEGIN RETURN $2; END;'
>LANGUAGE 'PLPGSQL';
> 
> DROP AGGREGATE lastitem int4;
> CREATE AGGREGATE lastitem (
>   SFUNC1 = lastint4,
> BASETYPE = int4,
>   STYPE1 = int4);
> 
> A simple (made-up) example:
> 
>   SELECT r.personid   AS personid
> ,SUM(r.laps)  AS laps
> ,COUNT(DISTINCT r.id) AS nightsraced
> ,lastitem(r.carid)AS carid
> FROM race r
>WHERE r.personid = 14
> GROUP BY r.personid
> ORDER BY r.date DESC;
> 
> Does PostgreSQL (or ANSI SQL) allow this?  In the simple tests I ran, I
> would get errors similar to the following:
> 
> ERROR:  Attribute r.carid must be GROUPed or used in an aggregate function
> 
> This seems a bit...obvious because r.carid is already being used in an
> aggregate function.  I'm guessing that I'm running into the internals
> of how PgSQL processes the query.
> 
> Hmm...well I just figured out how I could do this as a subselect, but it
> seems hugely inefficient and would require PostgreSQL 7.1 or later
> (ORDER BY, LIMIT used in a subselect):
> 
>   SELECT r.personid   AS personid
> ,SUM(r.laps)  AS laps
> ,COUNT(DISTINCT r.id) AS nightsraced
> ,(SELECT r.carid
> FROM race r
>WHERE r.personid = 14 
> ORDER BY r.date DESC
>LIMIT 1)   AS carid
> FROM race r
>WHERE r.personid = 14
> GROUP BY r.personid
> ORDER BY r.date;
> 
> Is a subselect like this really that inefficient (assuming appropriate 
> indexes on r.date and r.personid)?  I would think doing this during
> aggregation would be much more efficient.
> 
> I'm using PostgreSQL 7.0.3 on Debian GNU/Linux 2.2r3
> (postgresql-7.0.3-4) on a Linux 2.2.1x kernel.
> 
> Thanks for any insight you can provide!
> 
> Dave

---(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: How to store gif or jpeg? Thanks!

[Sylte]

> src/interfaces/jdbc/example/ImageViewer.java

Somewhere on the internet Please be more specific



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

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

Re: [SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Orac le ROWNUM = PSQL ???

[Cliff Crawford]

* Robert Beer <[EMAIL PROTECTED]> menulis:
> Oracle has a ROWNUM pseudo column that works like this ...
> TEST>select callid, rownum from cs_calls where rownum < 5;
> 
> CALLID ROWNUM
> -- --
>   7806  1
>   7807  2
>   7809  3
>   6443  4
> 
> 4 rows selected.
> 
> ... which can be quite handy.
> 
> Is there something like this in PSQL?

You can use the LIMIT clause:

SELECT callid FROM cs_calls LIMIT 4;

See

for more info.


> By the way, having used Oracle since 1987 it's a pleasure to use PSQL.
> Someone actually thinks about the implemented features.
> For example, Oracle's implementation of ROWNUM gives them in the order the
> rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER
> BY jumbles them up.  Duh!

LIMIT in PostgreSQL applies *after* ORDER BY, so you won't have this
problem :)


-- 
Cliff Crawford   http://www.sowrong.org/
birthday party cheesecake jellybean BOOM

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

[SQL] Postgres function library

[Lajtos Mate]

Hi all,

I read the post from Josh Berkus where he (at the end of the message)
mentions a 'function library'.
Is that library available for any developer or it's a proprietary one?
If it's public, can I post functions there?


Thanks in advance,
Ma'te'



---(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

[SQL] number of days in a month

[Cwhisperer]

Hello,

  in php I can find out the number of days within a month :
 march 31
 april 30
 mai   31
 

 does it exist a date function  to determine the number of days in a
 Select, knowing a specifique date ?

-- 
Best regards,
 Cwhisperer  mailto:[EMAIL PROTECTED]



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

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

[SQL] transaction isolation question

[Jack]

Regarding "Committed Isolation" on PostgreSql Ver 7.1 Users Guide, is that
possible a "Dead Lock" happened when two concurrent transactions are waiting
each other? And how to avoid or fix it?

Jack



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

[SQL] execute client application from PL/pgSql

[Jack]

Is that possible to execute a client application from server site by
PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS,
or is there any Windows version of  all Client Applications come from
PostGreSQL V7.1?

Jack



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

[SQL] Problem in Porting from Oracle to Postgres SQl

[Amit]

Hi!
I am facing two problems in porting from oracle to Postgres SQL.
1> There is a code in Oracle like
  Type Tstate is table of number(9)
    index by binary_integer;
.

To define a runtime table, basically it works like a array, How can
it be possible in Postgres SQL,
I have tried create temp table But it not works..
Is there any way to use arrays.
 
2> There is one function in Oracle Executesql '...' to
execute
    and what i got in Postgres is Execute immediate
'.'
    But it is giving error at Execute.
I will be very thankful if any one help me.
  Amit
( India )

[SQL] Informix->PostgreSQL database convertion

[Sylte]

Howto? Are there tools or is it just a lot of hard work with a lot of PHP?
Some examples or site-links will be appreciated :O)

Thanks
- Sylte



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

[SQL] Re: Informix->PostgreSQL database convertion

[Sylte]

It is convertion of the data, not the application...



---(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

[SQL] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

[David D. Kilzer]

NOTE: I did a moderate search through the PgSQL mail list archives, 
but couldn't find an example of a question where both ORDER BY and
aggregation were used.  In all examples it was possible to use either
one or the other with a possible subselect.  [Keep reading...I
discovered how to do this with subselects, but I'm wondering if (1) what
I'm doing with ORDER BY and aggregation will ever be allowed and (2)
whether the subselect solution is much more inefficient than being able
to order a pre-aggregate set.]

I need to query a table and return aggregated results.  The aggregation
occurs many ways (I'm joining 7 tables and returning 19 columns
currently):

  o Columns in a GROUP BY clause
  o SUM() functions
  o COUNT() functions

However, I wrote a new FUNCTION and a new AGGREGATE to return the *last*
value of a field during the aggregation process (see below).  This means
that I would like to use an ORDER BY clause with the SELECT ... GROUP BY
statement before the results are aggregated.

DROP FUNCTION "lastint4" (int4, int4);
CREATE FUNCTION "lastint4" (int4, int4)
RETURNS int4
 AS 'BEGIN RETURN $2; END;'
   LANGUAGE 'PLPGSQL';

DROP AGGREGATE lastitem int4;
CREATE AGGREGATE lastitem (
  SFUNC1 = lastint4,
BASETYPE = int4,
  STYPE1 = int4);

A simple (made-up) example:

  SELECT r.personid   AS personid
,SUM(r.laps)  AS laps
,COUNT(DISTINCT r.id) AS nightsraced
,lastitem(r.carid)AS carid
FROM race r
   WHERE r.personid = 14
GROUP BY r.personid
ORDER BY r.date DESC;

Does PostgreSQL (or ANSI SQL) allow this?  In the simple tests I ran, I
would get errors similar to the following:

ERROR:  Attribute r.carid must be GROUPed or used in an aggregate function

This seems a bit...obvious because r.carid is already being used in an
aggregate function.  I'm guessing that I'm running into the internals
of how PgSQL processes the query.

Hmm...well I just figured out how I could do this as a subselect, but it
seems hugely inefficient and would require PostgreSQL 7.1 or later
(ORDER BY, LIMIT used in a subselect):

  SELECT r.personid   AS personid
,SUM(r.laps)  AS laps
,COUNT(DISTINCT r.id) AS nightsraced
,(SELECT r.carid
FROM race r
   WHERE r.personid = 14 
ORDER BY r.date DESC
   LIMIT 1)   AS carid
FROM race r
   WHERE r.personid = 14
GROUP BY r.personid
ORDER BY r.date;

Is a subselect like this really that inefficient (assuming appropriate 
indexes on r.date and r.personid)?  I would think doing this during
aggregation would be much more efficient.

I'm using PostgreSQL 7.0.3 on Debian GNU/Linux 2.2r3
(postgresql-7.0.3-4) on a Linux 2.2.1x kernel.

Thanks for any insight you can provide!

Dave

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

[SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Oracle ROWNUM = PSQL ???

[Robert Beer]

Oracle has a ROWNUM pseudo column that works like this ...
TEST>select callid, rownum from cs_calls where rownum < 5;

CALLID ROWNUM
-- --
  7806  1
  7807  2
  7809  3
  6443  4

4 rows selected.

... which can be quite handy.

Is there something like this in PSQL?

By the way, having used Oracle since 1987 it's a pleasure to use PSQL.
Someone actually thinks about the implemented features.
For example, Oracle's implementation of ROWNUM gives them in the order the
rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER
BY jumbles them up.  Duh!

Thanks in advance for any responses.

---(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

[SQL] Re: Informix->PostgreSQL database convertion

[Sergey E. Volkov]

I think this isn't so difficult.

Use "unload to ..." on Informix side ( from dbaccess ) and "copy ... from
..." om Postgres side.

"Sylte" <[EMAIL PROTECTED]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
9d8r7v$1to$[EMAIL PROTECTED]">news:9d8r7v$1to$[EMAIL PROTECTED]...
> Howto? Are there tools or is it just a lot of hard work with a lot of PHP?
> Some examples or site-links will be appreciated :O)
>
> Thanks
> - Sylte
>
>



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

[SQL] Client Applications come with PostGreSQL V7.1

[Jack]

Is there any Windows version of  all Client Applications come with
PostGreSQL V7.1?

Jack




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

RE: [SQL] Order by email address by domain ?

[Maxim Maletsky]

here's an idea:

if you would have all emails stored in the reverse order:

moc.niamod@resu

then all you would need is simple 'ORDER BY email'.

Also, in this way, emails like [EMAIL PROTECTED] would be perfectly
ordered.

Is that a crazy thought?

If not yet then: why not to add an additional field to the table with the
reverse domain only? (after an @) ordering by it. INDEX would also be
simple.

now is crazy? Then, is there any such function in PostgreSQL? With C it
would be so easy and fast.

Cheers!
Maxim Maletsky



-Original Message-
From: Hervé Piedvache [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 11, 2001 3:38 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Order by email address by domain ?


Hi,

I just want to order by a listing of email address by domain like :

[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]

Is it possible and how ?

Thanks !
-- 
Hervé Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France 
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902 
Email: [EMAIL PROTECTED]

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

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

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