Обсуждение: Oracle to PGSQL -- need help
Hi there-- I'm trying to restrict some of my programming logic to the database. I have a table that has a one to many relationship with its data. Specifically, I could have group 1 with 5 tie ins, like so (this table also has a closed path which is used to find the left column): 1,2 1,5 1,200 1,4356 1,483 Given an insert request, I want the trigger to find out which # is used on the left, and then randomly select one of the resultant rows and insert the randomly picked row's PKEY value along with the original insertion info. into a third table. Here is how to do it in Oracle (non-trigger, but not hard to do). Can someone tell me how to make it work in PGSQL??? SELECT * FROM (SELECT XL.*, ROW_NUMBER() OVER (PARTITION BY 'Q' ORDER BY JOBID) AS N FROM XEROXLOGENTRIES XL) X WHERE X.N = ABS(MOD(DBMS_RANDOM.RANDOM, 100)) Aprpeciate it very much! RDB Randall Barber Brigham Young University rdb55@email.byu.edu
On Wed, 17 Jul 2002 12:29:27 -0600 "Randall Barber" <rdb55@email.byu.edu> wrote: > I'm trying to restrict some of my programming logic to the database. I have > a table that has a one to many relationship with its data. > Specifically, I could have group 1 with 5 tie ins, like so (this table also > has a closed path which is used to find the left column): > > 1,2 > 1,5 > 1,200 > 1,4356 > 1,483 > > Given an insert request, I want the trigger to find out which # is used on > the left, and then randomly select one of the resultant rows and insert the > randomly picked row's PKEY value along with the original insertion info. > into a third table. > > Here is how to do it in Oracle (non-trigger, but not hard to do). Can > someone tell me how to make it work in PGSQL??? > > SELECT * FROM > (SELECT XL.*, ROW_NUMBER() OVER (PARTITION BY 'Q' ORDER BY JOBID) AS N > FROM XEROXLOGENTRIES XL) X > WHERE X.N = ABS(MOD(DBMS_RANDOM.RANDOM, 100)) I think that "PARTITION BY" is equivalent to the following query. Its statement is so long that you may use VIEW. However, I haven't yet understood the meaning of MOD(DBMS_RANDOM.RANDOM, 100). CREATE TABLE xeroxlogentries (q int4, jobid int4 UNIQUE); INSERT INTO xeroxlogentries VALUES(1,2); INSERT INTO xeroxlogentries VALUES(1,5); INSERT INTO xeroxlogentries VALUES(1,200); INSERT INTO xeroxlogentries VALUES(1,4356); INSERT INTO xeroxlogentries VALUES(1,483); INSERT INTO xeroxlogentries VALUES(3,10); INSERT INTO xeroxlogentries VALUES(2,6); INSERT INTO xeroxlogentries VALUES(2,3); CREATE SEQUENCE seq_n; CREATE VIEW v_xeroxlogentries AS SELECT xl2.*, xl2.i - xl3.j_min AS n FROM (SELECT xl0.*, nextval('seq_n') - 1 AS i FROM (SELECT *, (SELECT setval('seq_n',1)) FROM xeroxlogentries ORDER BY q, jobid) AS xl0 LIMIT ALL ) AS xl2 INNER JOIN (SELECT xl1.q, min(xl1.j) - 1 AS j_min FROM ( SELECT xl0.q, xl0.jobid, nextval('seq_n') - 1 AS j FROM (SELECT q, jobid, (SELECT setval('seq_n',1)) FROM xeroxlogentries ORDER BY q, jobid) AS xl0 ) AS xl1 GROUP BY xl1.q LIMIT ALL ) AS xl3 ON (xl2.q = xl3.q); ------------------------------------------------------------ -- SELECT XL.*, ROW_NUMBER() OVER (PARTITION BY 'Q' ORDER BY JOBID) AS N -- FROM XEROXLOGENTRIES XL select xl.q, xl.jobid, xl.n from v_xeroxlogentries xl; q | jobid | n ---+-------+--- 1 | 2 | 1 1 | 5 | 2 1 | 200 | 3 1 | 483 | 4 1 | 4356 | 5 2 | 3 | 1 2 | 6 | 2 3 | 10 | 1 (8 rows) select xl.q, xl.jobid, xl.n from v_xeroxlogentries xl where xl.n = 2; q | jobid | n ---+-------+--- 1 | 5 | 2 2 | 6 | 2 (2 rows) Regards, Masaru Sugawara
On Sun, 21 Jul 2002 13:02:43 +0900 Masaru Sugawara <rk73@sea.plala.or.jp> wrote: > CREATE TABLE xeroxlogentries (q int4, jobid int4 UNIQUE); > INSERT INTO xeroxlogentries VALUES(1,2); > INSERT INTO xeroxlogentries VALUES(1,5); > INSERT INTO xeroxlogentries VALUES(1,200); > INSERT INTO xeroxlogentries VALUES(1,4356); > INSERT INTO xeroxlogentries VALUES(1,483); > INSERT INTO xeroxlogentries VALUES(3,10); > INSERT INTO xeroxlogentries VALUES(2,6); > INSERT INTO xeroxlogentries VALUES(2,3); > > > CREATE SEQUENCE seq_n; > You probably use TEMP SEQUENCE so as not to be interfered with increasing the number of seq_n by other sessions. CREATE TEMP SEQUENCE seq_n; Regards, Masaru Sugawara
On Thu, 25 Jul 2002 15:17:08 -0600 Randall Barber <rdb55@email.byu.edu> wrote: > I greatly appreciate your time and help--I have not yet tried to implement > what you gave me, but here is some additional explanation: > > ABS(MOD(DBMS_RANDOM.RANDOM, 100)) > > This generates a random number between between +/- 2^32. I use the MOD to > keep the number with a range (0-100) and an ABS to make it positive > regardless. The idea is to pick a random row out of information that shares > a common value. In your example, you list everything in the table, but > without much changing I think I can make it so that the result brings back a > single row instead of multiple. Just needs a where clause in one of the > SELECTs :). Thank you for your explanation. I were roughly able to figure out the whole process you thought. But there are some columns I don't know; actually their names don't appear in either of this mail and prior mail. Could you tell me the answer to the following questions ? 1. Does "a common value" mean one of the values that Q column has? 2. Does "a range (0-100)" mean the fact that the range is always 0 to 100 ? Doesn't the range depend on the number of the rows that share a selected value ? >> Given an insert request, I want the trigger to find out which # is used on >> the left, and then randomly select one of the resultant rows and insert the >> randomly picked row's PKEY value along with the original insertion info. >> into a third table. 3. What table is "a request" inserted into ? 4. What column is "PKEY" created on ? >> Q, JOBID >> -------- >> 1,2 >> 1,5 >> 1,200 >> 1,4356 >> 1,483 4. Is this a second table ? Regards, Masaru Sugawara
On Fri, 26 Jul 2002 22:54:23 +0900 I <rk73@sea.plala.or.jp> wrote: > >> Given an insert request, I want the trigger to find out which # is used on > >> the left, and then randomly select one of the resultant rows and insert the > >> randomly picked row's PKEY value along with the original insertion info. > >> into a third table. Probably, I would think this problem can be solved by means of the way like the following steps. -- in 7.2.1 CREATE TABLE third (q int4, jobid int4, name text, age int4); CREATE VIEW v_third AS SELECT 0::int4 AS q, name, age FROM third; CREATE OR REPLACE FUNCTION fn_get_jobid(int4) RETURNS int4 AS ' DECLARE x ALIAS FOR $1; rec RECORD; BEGIN CREATE TEMP SEQUENCE seq_n; SELECT INTO rec xl.jobid FROM (SELECT xl0.*, nextval(''seq_n'') - 1 AS i, (SELECT count(*) FROM xeroxlogentries WHERE q = x ) AS n FROM (SELECT *, (SELECT setval(''seq_n'',1)) FROM xeroxlogentries WHERE q = x ) AS xl0 LIMIT ALL ) AS xl WHERE xl.i = ceil( (SELECT random()) * xl.n); DROP SEQUENCE seq_n; RETURN rec.jobid; END; ' LANGUAGE 'plpgsql'; CREATE RULE r_v_third AS ON INSERT TO v_third DO INSTEAD INSERT INTO third VALUES (NEW.q, fn_get_jobid(NEW.q), NEW.name, NEW.age); -- insert requests INSERT INTO v_third VALUES (1, 'Mike', 20); INSERT INTO v_third VALUES (1, 'Jack', 25); INSERT INTO v_third VALUES (1, 'John', 15); INSERT INTO v_third VALUES (1, 'Mary', 20); . . . . Regards, Masaru Sugawara