Re: Stored procedure - Mailing list pgsql-general

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