Re: Stored procedure

[Thorsten Kraus <TK-Spam@xxxxxx>]

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@xxxxxxxxx
 
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@xxxxxxxxxxxxxx 
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of 
Thorsten Kraus
Sent: Thursday, May 03, 2007 5:27 PM
To: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxx
 
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@xxxxxxxxxxxxxx 
      

[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of 
Thorsten Kraus
    

	Sent: Thursday, May 03, 2007 5:00 PM
	To: pgsql-general@xxxxxxxxxxxxxx
	Subject: [GENERAL] Stored procedure
	
	
	Hi 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/