Re: Stored procedure - Mailing list pgsql-general

Hi,

thank you for your detailled answer!
Today I had the possibility to test it in the office. The procedure could be stored.
But when I call it SELECT create_geom_table('testtable') Then an error occurs:  column testtable not available. Do you know why?

Regards

 
Hakan Kocaman schrieb:

Hi,

your example should look like this:
CREATE OR REPLACE FUNCTION create_geom_table(table_name text) RETURNS void AS $BODY$
DECLARE   func_text	text;
BEGIN 
   func_text:='DROP TABLE ' || table_name ||';	CREATE TABLE ' || table_name ||'	( 	  id integer, 	  mytimestamp timestamp without time zone--, 	  --geom geometry, 	  --CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2), 	  --CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = '|| quote_literal('MULTIPOLYGON') ||'::text OR geom IS NULL), 	  --CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) 	) 	WITHOUT OIDS; 	ALTER TABLE ' || quote_literal(table_name) ||'OWNER TO "admin"; 
	--CREATE INDEX geo_index ON '|| quote_literal(table_name) ||'USING gist(geom); 
		--ALTER FUNCTION create_geom_table('|| quote_literal(table_name) ||') OWNER TO "admin"; 	';   EXECUTE func_text;
END; 
$BODY$ LANGUAGE plpgsql; 

select create_geom_table('test_geom_tbl');

It's not exactly the same, hence i don't got some of yout types(geom for example) laying around, but you get the picture, no?

Best regards

Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de
digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast 
 

-----Original Message-----
From: pgsql-general-owner@postgresql.org 
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of 
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Stored procedure

Hi,

thanks for your answer, but I don't get the point. Perhaps 
you can give 
me a small example how to get the EXECUTE into a stored procedure.

Regards

Hakan Kocaman schrieb:   

Hi,
Try EXECUTE
     

http://www.postgresql.org/docs/8.2/interactive/plpgsql-stateme
nts.html#PLPGSQL-STATEMENTS-EXECUTING-DYN    

 
Best Regards

Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de
digame.de GmbH, Sitz der Gesellschaft: Köln,      

Handelsregister Köln, HRB 32349   

Geschäftsführung: Werner Klötsch, Marco de Gast 


________________________________
From: pgsql-general-owner@postgresql.org      

[mailto:pgsql-general-owner@postgresql.org] On Behalf Of 
Thorsten Kraus   

	Sent: Thursday, May 03, 2007 5:00 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Stored procedureHi NG,I want to write a stored procedure which creates a      

table in my PostgreSQL database. The procedure has one input 
parameter: the table name.   

	Here is my first try, but that does not work:     

--------------------------------------------------------------
------------------------------------    

	CREATE OR REPLACE FUNCTION create_geom_table(text)      

RETURNS void AS $$    

	DECLARE     --table_name TEXT; BEGIN --------------------------------------- CREATE TABLE table_name (   id integer,   "time" timestamp without time zone,   geom geometry,   CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),   CONSTRAINT enforce_geotype_geom CHECK      

(geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),    

	  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = -1) ) WITHOUT OIDS; ALTER TABLE table_name OWNER TO "admin"; 
CREATE INDEX geo_index ON table_name USING gist(geom); 
--------------------------------------- ALTER FUNCTION create_geom_table(table_name) OWNER TO "admin"; END; $$ LANGUAGE plpgsql; 
     

--------------------------------------------------------------
------------------------------------   

	Can someone tell me what's wrong with this and what I      

have to change?   

	Regards,Thorsten
      

---------------------------(end of 
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
   

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org/