Re: Oracle to PGSQL -- need help

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