Re: Need special sequence generator

On 18/9/2006 16:43, "CN" <cnliou9(at)fastmail(dot)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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz