Re: [SQL] A SQL Question About distinct, limit, group by, having,
On Thu, 2005-03-31 at 13:14 +0800, Lin Kun Hsin wrote: > below is the sql schema. i hope it will help. > > i want the top 3 score students in every class this has been discussed before. a quick google gives me: http://archives.postgresql.org/pgsql-sql/2004-04/msg00067.php gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Help - Oracle 9i to PostgreSQL SQL conversion
Hi, I have installed PostgreSQL 8.0.1 on Solaris 9. I am porting my database from Oracle 9i to PostgreSQL. I am using PL/pgSQL language. In Oracle we can get error message from "SQLERRM" keyword and inserting it into table. How can I get error message/code in PostgreSQL after an EXCEPTION or RAISE EXCEPTION occurs in EXCEPTION block?? Pls help me or send me some example. Fuction attached below Thanks Dinesh Pandey CREATE OR REPLACE FUNCTION DOES_NODE_HAVE_RULE (IN_SENTRYID_ID IN NUMBER ,IN_NODE_ID IN NUMBER ,IN_DEVICEID IN NUMBER ,IN_ACTION IN VARCHAR2 ) RETURN BOOLEAN IS does NUMBER(2) := 0; mesg VARCHAR2(500) := 'Does rule exist failed for sentry: '||in_sentryid_id||', node: '||in_node_id||'.'; c_context VARCHAR2(50) := 'DOES NODE HAVE RULE'; c_program VARCHAR2(100) := 'RULE_CONTROL.DOES_NODE_HAVE_RULE'; v_sql VARCHAR2(1000);BEGIN v_sql := 'SELECT COUNT(*) FROM PORTAL_'||in_action||'_NODE_RULE WHERE sentryid_id = '||in_sentryid_id|| ' AND node_id = '||in_node_id||' AND device_id ='||in_deviceid; EXECUTE IMMEDIATE v_sql INTO does; IF does > 0 THEN RETURN TRUE; ELSIF does = 0 THEN RETURN FALSE; END IF; EXCEPTION WHEN OTHERS THEN DATAMAN.log_error(c_program, c_context, 1, 'PACKAGE', 'OTHERS: '||mesg, SQLERRM); RAISE_APPLICATION_ERROR(-2,SUBSTR(SQLERRM,1,250)); END does_node_have_rule;/SHOW ERROR
Re: [SQL] A SQL Question About distinct, limit, group by, having, aggregate
Actually, i have a method to solve this problem.
But i really want to know, we have to write more statement to do one thing?
First step, we have to create 2 sequence. Let call them "foo" and "foo1".
create sequence foo;
create sequence foo1;
then, you can run below statement, and you will see the result that is we want.
select setval('foo',1);
select setval('foo1',1);
select id, score, class from (
select id, score, ph1.class , sequence1, sequence2, CASE WHEN
ph1.cc > 5
THEN ph3.sequence + 4
ELSE ph3.sequence + ph1.cc - 1 END as tail from
(select class, count(*) as cc from allscore group by
class)
as ph1 join
(select id, class, score, nextval('foo') as sequence1,
currval('foo') as
sequence2 from (select * from allscore order by class, score desc) as t2)
as ph2 on (ph1.class = ph2.class) join
(select distinct on (class) class, nextval('foo1'),
currval('foo1') as
sequence from (
select id , score , class from allscore order by
class, score desc) as t6)
as ph3 on (ph2.class = ph3.class) order by ph1.class , score
desc
) as con where sequence2 <= tail;
--
http://alumni.cyut.edu.tw
Open WebMail Project (http://openwebmail.org)
---(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] New record position
There's a difference between "natural" order (the location in the database or on disk) and "record" order (the order specified by the primary key)... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne Sent: Wednesday, March 30, 2005 3:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] New record position [EMAIL PROTECTED] writes: > Why it? I can't undestand why the new record location was change. Shouldn't it > apper at the LAST record??? > What need I do?? SQL only imposes an order on the return set if you add an "ORDER BY" clause. You can't expect any particular order to either recur or NOT recur unless you have specifically requested a particular ordering. There's no bug; just use ORDER BY if you need to, and, if you don't, make sure you don't expect any particular ordering... -- (format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com") http://www3.sympatico.ca/cbbrowne/spiritual.html "The present need for security products far exceeds the number of individualscapable ofdesigning secure systems. Consequently, industry has resorted to employing folks and purchasing "solutions" from vendors that shouldn't be let near a project involving securing a system." -- Lucky Green ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] New record position
[EMAIL PROTECTED] ("Greg Patnude") writes:
> There's a difference between "natural" order (the location in the
> database or on disk) and "record" order (the order specified by the
> primary key)...
That's well and fine; I could see the "natural order" in which data is
returned varying over time in view of the fact that it is probably
quickest to start by first returning the rows that are sitting in
shared cache, and only then going to the table to get more.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/postgresql.html
"The present need for security products far exceeds the number of
individualscapable ofdesigning secure systems. Consequently,
industry has resorted to employing folks and purchasing "solutions"
from vendors that shouldn't be let near a project involving securing a
system." -- Lucky Green
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] unsubscribe pgsql-sql
unsubscribe pgsql-sql ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] unsubscribe pgsql-sql
Robin M. wrote: unsubscribe pgsql-sql ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org roger doger, copy that tower 2, bravo delta. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 3/27/2005 ---(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] asynchrous triggers
hi folks, is it possible somehow to make (AFTER) triggers run in their own process/thread, so the calling session can return immediately and the trigger runs in background ? thx -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] asynchrous triggers
On Fri, Apr 01, 2005 at 05:52:49AM +0200, Enrico Weigelt wrote: > is it possible somehow to make (AFTER) triggers run in their > own process/thread, so the calling session can return immediately > and the trigger runs in background ? Not really, though you could signal an external process to do something asynchronously using LISTEN/NOTIFY. That is, unless you write your trigger function in C and it calls fork(). Not sure if that would actually work though (and you should forget about accessing the database.) -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
