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@xxxxxxxxx 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@xxxxxx] Sent: Friday, May 04, 2007 5:36 PM To: Hakan Kocaman; pgsql-general@xxxxxxxxxxxxxx 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 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.kocaman@xxxxxxxxx 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Thorsten Kraus Sent: Thursday, May 03, 2007 5:27 PM To: pgsql-general@xxxxxxxxxxxxxx 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@xxxxxxxxx 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Thorsten Kraus Sent: Thursday, May 03, 2007 5:00 PM To: pgsql-general@xxxxxxxxxxxxxx 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/
