I have PostgreSQL 8.4 with 3 pairs of tables like this:
CREATE TABLE filedata_temp
(
num serial NOT NULL,
id integer,
mydata character(25),
the_geom geometry,
CONSTRAINT filedata_temp_pkey PRIMARY KEY (num)
)
CREATE TABLE filedata
(
num serial NOT NULL,
id integer,
mydata character(25),
the_geom geometry,
CONSTRAINT filedata_pkey PRIMARY KEY (num)
)
And 3 functions like this:
-- Function: insert_into_wgs()
-- DROP FUNCTION insert_into_wgs();
CREATE OR REPLACE FUNCTION insert_into_wgs()
RETURNS void AS
$BODY$
BEGIN
INSERT INTO filedata (num,id,mydata,the_geom)
SELECT num,id,mydata,ST_TRANSFORM(the_geom,4326)
FROM filedata_temp
WHERE id NOT IN (SELECT id FROM filedata);
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insert_into_wgs() OWNER TO postgres;
);
So i have 3 simular functions for 3 pairs of tables. Its not good. So can i make a one function for all 3 pairs? How to make this?
Yes, you can. Use the PL/PgSQL EXECUTE ... USING
statement to dynamically construct the SQL string and execute it. This will be quite a bit slower though, as there's the PL/PgSQL call overhead and the cost of replanning the statement every time.
More importantly, you appear to be attempting to write an upsert / merge function. This function is wrong. For why, see depesz's excellent-as-usual article on upsert/merge.