Обсуждение: 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