source: trunk/data/konvert/transaction_delete.sql @ 99

Revision 99, 1.1 KB checked in by astrid.emde, 10 years ago (diff)

new table delete and function to delete features

Line 
1-- Table: "delete"
2
3-- DROP TABLE "delete";
4
5CREATE TABLE "delete"
6(
7  ogc_fid serial NOT NULL,
8  typename character(33),
9  featureid character(32),
10  CONSTRAINT delete_pk PRIMARY KEY (ogc_fid)
11);
12
13DROP FUNCTION deletefeature(text, text);
14CREATE FUNCTION deleteFeature(typename text, featureid text) RETURNS text
15AS $$
16 DECLARE
17  query text;
18  res text;
19 BEGIN
20     query := 'DELETE FROM ' || $1 || ' WHERE gml_id = substring(''' || $2 || ''' from 1 for 16)';
21     EXECUTE query;
22
23     IF FOUND THEN
24        RAISE NOTICE 'query successfull % ', query;
25        res := 1;
26     ELSE
27        RAISE NOTICE 'query no object found % ', query;
28        res := 0;
29     END IF;
30  RETURN res;
31 END;
32$$ LANGUAGE plpgsql;
33
34-- example
35--INSERT INTO AA_Antragsgebiet (gml_id) VALUES ('DENW44AL0000okRc');
36--INSERT INTO delete (typename, featureid) VALUES ('AA_Antragsgebiet','DENW44AL0000okRc20110428T135110Z');
37--Select deleteFeature('AA_Antragsgebiet','DENW44AL0000okRc20110428T135110Z');
38--INSERT INTO AA_Antragsgebiet (gml_id) VALUES ('DENW44AL0000okRc');
39--Select * from AA_Antragsgebiet
40
41Select deleteFeature(typename,featureid) from delete;
Note: See TracBrowser for help on using the repository browser.