[GENERAL] Database reverse engineering
Hello, I am looking for a tool which is able to generate a database diagramm including the relationships from an existing database schema. The only tool I know for this purpose is the Clay database plugin for eclipse. Are there any other tools which can help me? Best regards, Thorsten ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
digame.de GmbH, Sitz der Gesellschaft: Köln,
Handelsregister Köln, HRB 32349
Geschäftsführung: Werner Klötsch, Marco de Gast
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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/
[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
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-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: [EMAIL PROTECTED] digame.de GmbH, Sitz der Gesellschaft: Köln, Handelsregister Köln, HRB 32349 Geschäftsführung: Werner Klötsch, Marco de Gast From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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
Re: [GENERAL] Webappication and PostgreSQL login roles
No idea?? Thorsten Kraus schrieb: Hi, I designed a Java web application. The persistence layer is a PostgreSQL database. The application needs user authentication. I think it's a good choice to implement this authentication mechanism via PostgreSQL login roles. So I can create several database login roles and set the database permissions to this login roles. This is my first project with the postgres database, so I don't know how I can validate a login from the website. Is there a best practice to do this or does PostgreSQL offers a stored procedure like 'authenticateUser(String username, String password)'? Thanks for your help. Bye, Thorsten ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Webappication and PostgreSQL login roles
Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb: Can you not use the username/password as part of the DSN? Regards, Lutz Broedel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Webappication and PostgreSQL login roles
This would be a possible way. Now the question is which algorithm implementation of md5 PostgreSQL uses... Bye, Thorsten Ben Trewern schrieb: You could originally connect to the database as some kind of power user. Check the password against the pg_shadow view (you would need to md5 your password somehow) and then do a SET SESSION AUTHORIZATION (or SET ROLE) to change your permissions. Not sure how secure this would be but it's the way I would try. Regards, Ben Thorsten Kraus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, thanks for your answer. I cant use the username/password in my DSN because I don't connect directly via JDBC to the database. I use hibernate for all database actions. The username and password has to be stored in the hibernate configuration file... Bye, Thorsten Lutz Broedel schrieb: Can you not use the username/password as part of the DSN? Regards, Lutz Broedel ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Webappication and PostgreSQL login roles
Hi, I designed a Java web application. The persistence layer is a PostgreSQL database. The application needs user authentication. I think it's a good choice to implement this authentication mechanism via PostgreSQL login roles. So I can create several database login roles and set the database permissions to this login roles. This is my first project with the postgres database, so I don't know how I can validate a login from the website. Is there a best practice to do this or does PostgreSQL offers a stored procedure like 'authenticateUser(String username, String password)'? Thanks for your help. Bye, Thorsten ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] DB Modeler
Hi, which tools do you use for modelling your databases? I need a tool with a graphical interface where I can create tables and relations. The tool should also be able to create the DDL for Postgres. Thanks, Thorsten ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
