0

I have a two tables: 

CREATE TABLE filedata_temp
(
 num serial NOT NULL,
 id integer,
 mydata character(25),
 the_geom geometry,
 CONSTRAINT filedata_pkey PRIMARY KEY (num)
)


CREATE TABLE filedata
(
 num serial NOT NULL,
 id integer,
 mydata character(25),
 the_geom geometry,
 CONSTRAINT filedata_temp_pkey PRIMARY KEY (num)
)

i want to make function which insert rows from first table in second table if this rows not exist in second table. Field for comparison tables is num field.

After reading some examples :

  CREATE OR REPLACE FUNCTION  insert_into_wgs()
  RETURNS void AS
  $BODY$
  BEGIN
insert into filedata 
(
    id,
    mydata,
    the_geom,

)
values
(
    id,
    mydata,
    ST_TRANSFORM(the_geom,4326)
);
 end
 $BODY$
 LANGUAGE 'plpgsql'

So i need some help/

UPDATE

i try this function(gods of data base's says me it)

CREATE OR REPLACE FUNCTION  insert_into_wgs()
RETURNS void AS
$$
BEGIN
 INSERT INTO filedata (id,mydata,the_geom)  
 SELECT id,mydata,ST_TRANSFORM(the_geom,4326)
 FROM filedata_temp
 WHERE id NOT IN (SELECT id FROM filedata);
end;
$$
LANGUAGE 'plpgsql'

But nothing insert in table filedata.

function calling

  ce_proc = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgis","postgres","123456");
  CallableStatement proc = (CallableStatement) ce_proc.prepareCall("{call insert_into_wgs()}");
  proc.execute();
Improve this question
6
  • My answer stackoverflow.com/a/11797148/905902 here should provide enough material. It should be quite easy to change the trigger functions into ordinary functions. and the casts into the geom transformations you want. Commented Aug 6, 2012 at 9:06
  • Im already read your answer. Now i use simular function. Bit when i try use this in trigger i get a problem with function ST_TRANSFORM(). So now i want to create function to call it from java code. Commented Aug 6, 2012 at 9:27
  • You should provide ST_transform() with the correct arguments (the values from NEW.the_geom plus the srid you want). In my example I use a plain cast zvalue::text, or ztext::integer, you should use the st_tranform() function there instead. (possibly prefixed by the schema name eg public.st_transform() ) That's all. Commented Aug 6, 2012 at 9:32
  • i know what arguments st_transform() gonna get. Its function works when i call it in sql edidor or insert or update another table. But in my case its problem with srids. I update my previous question and u can see what st_transform() do with geometry. Commented Aug 6, 2012 at 10:08
  • BTW: why do the tables appear to have two keyfields (num, id) and only one (id) seems to be used, while the other (num) is defined as a primary key? Commented Aug 6, 2012 at 16:27

2 Answers 2

Reset to default
1

You can use the INSERT ... SELECT syntax to shove data from one table into another:

INSERT INTO filedata (...) SELECT ... FROM filedata_temp WHERE NOT EXISTS 
(
    SELECT 1 FROM filedata WHERE filedata.num = filedata_temp.num
)

Of course the WHERE NOT EXISTS part is only one way to check for differences. Joining the table is another. Which is the best one depends on your actual data.

Other Note: Both tables have num serial. But shoving data from one table into the other this means that both SERIALs are fighting each other. You should make that a plain int in the second table.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The two serials will be painful, but they could also use the same underlying sequence (as a default for a bigint column type) The "explicit" syntax will be a bit more complex.
@wildplasser: I know what you mean, but I think your wording is wrong: Since a serial is an int together with a new sequence you cannot create a serial with a "shared" sequence. You can create an 'int' column with a default using an existing sequence though. How the "ownership" of the shared sequence is handled... I don't know.
The only hard part will be the owned by part. But I think it only serves to establish the dependency (to enable DROP ... CASCADE to cascade to the sequence), so you could choose to do without the "owned by" part. That would leave the sequence orphaned on a DROP table ... CASCADE
0

The most relevant section in the manual is 39.9. Trigger Procedures. The examples there, especially Example 39-4. A PL/pgSQL Trigger Procedure For Auditing can be a better starting point than your current code.

Improve this answer

3 Comments

@KliverMax: You might explain that a little bit more.
When i use a triggers function st_transform() not work becouse i try to put geometry with srid=70066 in geometry column with srid = 4326.
@KliverMax: I don't see why this has anything to do with triggers or not.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.