[postgis-users] How to implement a GUID

[Milo van der Linden]

Hello!

Mark, for me, this is the WINNER SOLUTION!  This is the correct way to
do it in my particular situation; respect!


Only one more hurdle left: I am doing the insert from php with
variables. what would be the best way to go; store this in a function
whit the right variables in place?

Or is there a way to control the BEGIN; COMMIT; loop from within php?

I will look in the php and postGress wiki's and documentation. When I
find the answer myself, I will post it.



Mark Cave-Ayland schreef:
> 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.
>
>   


-- 


	

Milo van der Linden
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
milo at 3dsite.nl <mailto:milo at 3dsite.nl>
http://www.3dsite.nl

	  	

De informatie in dit bericht reflecteerd mijn persoonlijke mening en
niet die van een bedrijf of instantie. Aan de informatie kunnen geen
rechten worden ontleend. Indien dit bericht onderdeel is van een forum,
mailing-list of community dan gelden automatisch de bijbehorende
voorwaarden.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070724/f17bb494/attachment.html>