Re: 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(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
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>