Thread: 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

am  Mon, dem 18.09.2006, um 15:13:10 +0800 mailte CN folgendes:
> 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:

You can use this:

test=# select * from t2;
 id
-----
 aaa
 bbb
 aaa
 ccc
 aaa
 ddd
(6 rows)

test=*# create sequence t2_seq;
CREATE SEQUENCE
test=*# select *, nextval('t2_seq') as foo from t2 where id = 'aaa';
 id  | foo
-----+-----
 aaa |   1
 aaa |   2
 aaa |   3
(3 rows)


(You should use a temp. sequence instead...)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On 18/9/2006 16:43, "CN" <cnliou9@fastmail.fm> wrote:

> 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.

Have you tried using the INSERT INTO t1 SELECT .... in the trigger instead
of the loop?

This may be the result you are after but it would appear to me that the
contents of t1 is going to get exponentially huge. The first insert of
a=const_id will insert 1 row into t1 the second will insert 2 rows one
duplicating the previous insert ... the 100th will insert 100 rows - all 99
inserts before will be inserted again with new seq numbers.

> 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.

You will find the sequence isn't inserting values because the column is
simply defined as a smallint. Having a type of serial will auto enter
sequence numbers (but not smallint) or have c2 smallint DEFAULT
nextval('myseq')


> 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

--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz