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
geomIS 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
>