Re: Oracle to PGSQL -- need help

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