Re: Stored procedure

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(dot)kocaman(at)digame(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> Thorsten Kraus
> Sent: Thursday, May 03, 2007 5:27 PM
> To: pgsql-general(at)postgresql(dot)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(dot)kocaman(at)digame(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
> Thorsten Kraus
> > Sent: Thursday, May 03, 2007 5:00 PM
> > To: pgsql-general(at)postgresql(dot)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
>