Re: Stored procedure

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
youknow 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')
||'::textOR 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
thepicture, 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/