[postgis-users] How to implement a GUID

[Mark Cave-Ayland]

On Tue, 2007-07-24 at 15:52 +0200, Milo van der Linden wrote:
> This is a suggestion and it leads in the right direction but...
> 
> My temp_table contains more than one record..
> 
> This would make retrieving currval useless. It would be better if
> there was a way to get the id's of records that are changed during the
> last insert.
> 
> I am trying to prevent looping records in the temp_table because then
> I would end up looping like this
> 
> BEGIN;
> DO UNTIL NO_MORE_RECORDS in temp_table
> 	INSERT INTO t1 SELECT the_geom FROM geom_temp_table where id = MIN(id);
> 	INSERT INTO t2 (id, field) SELECT currval('seq1'), myattribute FROM
> 	geom_temp_table;
> 	DELETE FROM temp_table where id=MIN(id);
> LOOP
> COMMIT;
> but If that is the only solution please let me know.


Sure. You can always generate a temporary table containing all the new
ids and then join back onto it as part of a SELECT like this:


CREATE SEQUENCE seq1;

CREATE TABLE t1 AS (
        id int8 default nextval('seq1'),
        the_geom geometry
);

CREATE TABLE t2 AS (
        id int8,
        field varchar
);


BEGIN;
CREATE TEMPORARY TABLE lookup AS SELECT id, nextval('seq1') AS newid
FROM geom_temp_table;
INSERT INTO t1 (id, the_geom) SELECT s2.newid, s1.the_geom FROM
geom_temp_table AS s1, lookup AS s2 WHERE s2.id = s1.id;
INSERT INTO t2 (id, field) SELECT s2.newid, s1.myattribute FROM
geom_temp_table AS s1, lookup AS s2 WHERE s2.id = s1.id;
DROP TABLE lookup;
COMMIT;


Note that you can DROP the temporary table explicity or it will
automatically be removed when you disconnect from the server. Also
remember that if more than one person is running this at the same time,
you'll need to append some form of random session id onto the lookup
table name, otherwise things will break very quickly.


HTH,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk