Re: Serial Updating - Mailing list pgsql-general

From A. Kretschmer
Subject Re: Serial Updating
Date
Msg-id 20060904052151.GA10852@localhost.localdomain
Whole thread Raw
In response to Serial Updating  (Bob Pawley <rjpawley@shaw.ca>)
List pgsql-general
am  Sun, dem 03.09.2006, um 14:24:41 -0700 mailte Bob Pawley folgendes:
> I have a column that I wish to use to designate loop numbers.
>
> A loop number is composed of a letter (F for flow, T for temperatures etc.)
> followed by a sequence number starting at one and ending with the last loop.
>
> The loop numbers will appear as F-1, T-2 and so on.
>
> I seem to have no problem designating the column with the appropriate letters.
>
> However, I haven't a clue how to add the numerical portion - or if it is
> even possible.

I'n not sure if i understand you correctly, but perhaps this example
will be helpful for you:

test=# select * from foo;
 col1
------
 a
 b
 a
 c
 a
(5 rows)

test=*# create sequence s1;
CREATE SEQUENCE
test=*# create sequence s2;
CREATE SEQUENCE
test=*# select col1, case when col1 = 'a' then nextval('s1') when col1 ='b' then nextval('s2') else 0 end from foo;
 col1 | case
------+------
 a    |    1
 b    |    1
 a    |    2
 c    |    0
 a    |    3
(5 rows)

-- or, with the value from col1:

test=*# rollback;
ROLLBACK
test=# create sequence s1;
CREATE SEQUENCE
test=*# create sequence s2;
CREATE SEQUENCE
test=*# select col1, col1 || '-' || case when col1 = 'a' then nextval('s1') when col1 ='b' then nextval('s2') else 0
endfrom foo; 
 col1 | ?column?
------+----------
 a    | a-1
 b    | b-1
 a    | a-2
 c    | c-0
 a    | a-3


Read more about conditional expressions:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12589
and about sequences:
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html


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

pgsql-general by date:

Previous
From: Jorge Godoy
Date:
Subject: Re: Creating a date/time search function in Postgres.
Next
From: Anton de Wet
Date:
Subject: Re: [pgsql-advocacy] Thought provoking piece on NetBSD