Re: Oracle to PGSQL -- need help
От | Masaru Sugawara |
---|---|
Тема | Re: Oracle to PGSQL -- need help |
Дата | |
Msg-id | 20020721125229.64DC.RK73@sea.plala.or.jp обсуждение исходный текст |
Ответ на | Oracle to PGSQL -- need help ("Randall Barber" <rdb55@email.byu.edu>) |
Ответы |
Re: Oracle to PGSQL -- need help
(Masaru Sugawara <rk73@sea.plala.or.jp>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: