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

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