Re: Oracle to PGSQL -- need help

On Fri, 26 Jul 2002 22:54:23 +0900
I <rk73(at)sea(dot)plala(dot)or(dot)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