Re: Oracle to PGSQL -- need help

On Wed, 17 Jul 2002 12:29:27 -0600
"Randall Barber" <rdb55(at)email(dot)byu(dot)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