psql access of user's environmental variables

Поиск
Список
Период
Сортировка
Искать

psql access of user's environmental variables

От:
Paul Tilles <Paul.Tilles@noaa.gov>
Дата:
I need to use the value of an environment variable as part of an SQL 
query within psql.

I can do the following withing psql:

\set  local_site  `echo  $FXA_LOCAL_SITE

\echo  local site =  :local_site

The result is "local_site = xxx"  which is correct.

What I really want to do is the following:

\set  local_site  `echo  $FXA_LOCAL_SITE

UPDATE  table_name  SET  office_id =  :local_site;

This results in the message

               column  "xxx" does not exist

Is there any way that I can use the value of the FXA_LOCAL_SITE env 
variable in my UPDATE statement?

Paul Tilles


Re: psql access of user's environmental variables

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Paul Tilles  writes:
> What I really want to do is the following:

> \set  local_site  `echo  $FXA_LOCAL_SITE
> UPDATE  table_name  SET  office_id =  :local_site;

> This results in the message
>                column  "xxx" does not exist

Yes, because you have no quotes in the value of the variable, so that
update looks to the server like
	UPDATE  table_name  SET  office_id =  xxx;

After some fooling around, the easiest way to get the needed quotes is
to embed them in the echo result:

\set local_site `echo "'$FXA_LOCAL_SITE'"`

BTW, does your psql really let you leave off the trailing ` like
that?  Mine doesn't.

			regards, tom lane

Stored procedure

От:
Thorsten Kraus <TK-Spam@gmx.de>
Дата:
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

Re: psql access of user's environmental variables

От:
Paul Tilles <Paul.Tilles@noaa.gov>
Дата:
Tom,
Thank you. That works.

My psql does not allow me to leave off the trailing `.

It is my typing that is the problem.

Paul

Tom Lane wrote:
> Paul Tilles  writes:
>   
>> What I really want to do is the following:
>>     
>
>   
>> \set  local_site  `echo  $FXA_LOCAL_SITE
>> UPDATE  table_name  SET  office_id =  :local_site;
>>     
>
>   
>> This results in the message
>>                column  "xxx" does not exist
>>     
>
> Yes, because you have no quotes in the value of the variable, so that
> update looks to the server like
> 	UPDATE  table_name  SET  office_id =  xxx;
>
> After some fooling around, the easiest way to get the needed quotes is
> to embed them in the echo result:
>
> \set local_site `echo "'$FXA_LOCAL_SITE'"`
>
> BTW, does your psql really let you leave off the trailing ` like
> that?  Mine doesn't.
>
> 			regards, tom lane
>   

Re: Stored procedure

От:
"Hakan Kocaman" <Hakan.Kocaman@digame.de>
Дата:
Hi,
 
Try EXECUTE
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.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 PM
	To: pgsql-general@postgresql.org
	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
	

Re: Stored procedure

От:
Thorsten Kraus <TK-Spam@gmx.de>
Дата:
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-statements.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 PM
> 	To: pgsql-general@postgresql.org
> 	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
> 	
>
>   

Re: Stored procedure

От:
"Hakan Kocaman" <Hakan.Kocaman@digame.de>
Дата:
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 PM
> > 	To: pgsql-general@postgresql.org
> > 	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
> 

Re: Stored procedure

От:
Thorsten Kraus <TK-Spam@gmx.de>
Дата:
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/
 

Re: Stored procedure

От:
"Hakan Kocaman" <Hakan.Kocaman@digame.de>
Дата:
Hi,
 
could you please post the complete code that you used to create the function.
 
It sounds suspicously, that pg thinks 'testtable' is a coloum.
Have you set proper quotes in your function-code?
Maybe i got some mistakes regarding the usage of quote_literal in my sample code.

Till later

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: Thorsten Kraus [mailto:TK-Spam@gmx.de] 
	Sent: Friday, May 04, 2007 5:36 PM
	To: Hakan Kocaman; pgsql-general@postgresql.org
	Subject: Re: [GENERAL] Stored procedure
	
	
	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 PM
					To: pgsql-general@postgresql.org
					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/
		
		  


FAQ