[postgis-users] How to implement a GUID

[Mark Cave-Ayland]

On Tue, 2007-07-24 at 15:27 +0200, Milo van der Linden wrote:
> Hello Mark,
> 
> I am using your suggestion at this moment but the problem is as
> follows:
> 
> I do an insert into the final_geom table which then uses a new id
> but; 
> 
> I need this id to do an insert into the final_attrib table!
> 
> Basically what I want to do is:
> 
> INSERT geom into table1 AND INSERT attrib1, attrib2, attrib3 into
> table2 FROM temp_table.
> The resulting table1 and table2 need to have exactly the same ID.
> 
> I was first thinking about building an id in my temp_table and then do
> the 2 insert; But..my temp table is constantly created automatically
> by ogr2ogr, so I (seem to) have no influence on it's structure.

Ah OK - in that case you need to use currval() in the same transaction
as your original INSERT, e.g.


CREATE SEQUENCE seq1;

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

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


BEGIN;

INSERT INTO t1 SELECT the_geom FROM geom_temp_table;
INSERT INTO t2 (id, field) SELECT currval('seq1'), myattribute FROM
geom_temp_table;

COMMIT;


And then check the results with:

	SELECT id FROM t1;
	SELECT id FROM t2;

HTH,

Mark.

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