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(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: Thorsten Kraus [mailto:TK-Spam(at)gmx(dot)de]
Sent: Friday, May 04, 2007 5:36 PM
To: Hakan Kocaman; pgsql-general(at)postgresql(dot)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 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/