Need special sequence generator

Hi!

CREATE TABLE t1 (c1 text, c2 SMALLINT, PRIMARY KEY (c1,c2));

CREATE TABLE t2 (a text);

I am looking for a solution that will insert rows into table t1 with one
SQL similar to this:

INSERT INTO t1
SELECT a,my_seq() FROM t2
WHERE a='const_id'

my_seq() in SELECT clause returns a sequence of SMALLINT starting from 1
for every rows returned from t2. As a result, suppose 3 rows are
returned from

SELECT * FROM t2 WHERE a='const_id'

, then 3 records will be inserted to table t1:

const_id, 1
const_id, 2
const_id, 3

I know a PL/PGSQL function like this does the job:

DECLARE
i SMALLINT:=1;
rec RECORD;
BEGIN
FOR rec IN
SELECT 1 FROM t2 WHERE a='const_id'
LOOP
INSERT INTO t1 VALUES ('const_id',i);
i:=i+1;
END LOOP
END

but it works much slower than a single SQL especially when there are
many rows returned from table t2.

Sequence mechanism appears to be not applicable to column t1.c2, either,
becase t1.c2 is not the primary key, but the concatenated columns
(c1,c2) are.

I also thought about implementing my_seq(bool start) in C by utilizing
some static variables. However, this approach should not work, either,
at least becasue (a) I think the backend will call it only once in a SQL
statement rather than once for every returned rows from table t2, and
(b) this function, if ever exists, has race issue.

Regards,

CN

--
http://www.fastmail.fm - One of many happy users:
http://www.fastmail.fm/docs/quotes.html