Re: [SQL] OT: OpenDatabase Model ?
On Wed, Aug 16, 2006 at 10:03:38PM +0200, Jesper K. Pedersen wrote: > The opendatabase model actually offered a standard set of table > definitions covering a wide range of data storage. > > Of course this means that the tables would often have stuff you dont > need, and may not have the things you need, but at least there is a > common "thread" in how you different databases look. For the big company Ick. I confess my reaction is mostly aesthetic, but still. Why don't coding practices and column naming conventions get you this? That way, you can make your physical data model resemble your logical data model, rather than pounding with a big hammer on your logical model to make the physical storage you have fit? The database is not a filesystem. If you just need a filesystem and a SQL-like interface to it, use MySQL 3.x. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help with optional parameters
Rob Tester wrote: I have the need to have optional values for a query in a stored procedure that I am building. (using postgres 8.1.4). This particular query executes against a huge table (several million rows) and has six optional parameters that can be sent to the function. If one of the parameters is null then the parameter doesn't need to be included in the query. Also the values in the columns that are optional can contain NULL values. One way that will work (although extremely cumbersome) is to have a switch on the parameters to execute the correct query: --This is a sample IF (a IS NULL AND b IS NULL) THEN select * from my_table; ELSEIF (a IS NOT NULL and b IS NULL) THEN select * from my_table where a=parama; ELSEIF (a IS NULL and b IS NOT NULL) THEN select * from my_table where b=paramb; ELSE select * from my_table where a=parama AND b=paramb; ENDIF; This is extremely bad when you have 6 parameters giving 64 possible queries. You can try something like this, it should show the basis, CREATE OR REPLACE FUNCTION test(int4, int4) RETURNS text AS $BODY$ DECLARE a ALIAS FOR $1; b ALIAS FOR $2; -- add as many as input param query_base text; has_param bool; query_where text; query_final text; BEGIN query_base := 'SELECT * FROM my_table '; has_param := FALSE; query_where := ''; IF (a IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'parama='||a; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF; IF (b IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'paramb='||b; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 param END IF; --copy/paste/edit this IF ENDIF block for each param query_final := query_base || query_where; RAISE NOTICE '%', query_final; RETURN query_final; -- EXECUTE query_final; -- END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; test: select test(null,null) union select test(1,null) union select test(1,1) union select test(null,1); result: "SELECT * FROM my_table " "SELECT * FROM my_table WHERE parama=1" "SELECT * FROM my_table WHERE parama=1 AND paramb=1" "SELECT * FROM my_table WHERE paramb=1" HTH, -- MaXX ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] NULL becomes default
A surrogate key has been introduced on a table with modifiers:
Column|Type |
Modifiers
-+-+
new_id| integer | not null default
nextval(('some_id_seq'::text)::regclass)
However, some legacy software inserts into the table by first getting column
names and then composing an INSERT for all the columns, filling in any
undefined values with NULLs.
Thus when it attempts to insert a row I get a:
ERROR: null value in column "new_id" violates not-null constraint
Thus I would like NULLs in such INSERTs to be treated as DEFAULT rather than
violating the constraint. Is there an easy way to do that at the database
level?
Thanks
Julian
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] NULL becomes default
Hi Julian u need to reset your sequence, i had run through the same problem. On 8/17/06, Julian Scarfe <[EMAIL PROTECTED]
> wrote:A surrogate key has been introduced on a table with modifiers:
Column|Type |Modifiers-+-+ new_id| integer | not null default
nextval(('some_id_seq'::text)::regclass)However, some legacy software inserts into the table by first getting columnnames and then composing an INSERT for all the columns, filling in anyundefined values with NULLs.
Thus when it attempts to insert a row I get a:ERROR: null value in column "new_id" violates not-null constraint
To fix this, you need to do something like:
SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;
This will make sure that the next value your sequence generates is
greater than any key that already exists in the table.
>> taken from tom lane.-- Thanks,Sumeet.
[SQL] About DBlink
Hi All,Im trying to find out documentation for postgres module names "dblink", can anyone point me out to it. What i want to do is join multiple databases instead of multiple tables.Thanks,Sumeet.
Re: [SQL] About DBlink
On Thu, Aug 17, 2006 at 04:37:03PM -0400, Sumeet wrote: > Im trying to find out documentation for postgres module names "dblink", can > anyone point me out to it. What i want to do is join multiple databases > instead of multiple tables. If you've installed dblink then somewhere on your system should be a file named README.dblink. How to find that file depends on the platform: on many Unix-like systems you can use "locate" or "find", or if you installed dblink via a package then you could use the package management tool to display a list of files that it installed. I'd mention that doing joins between databases isn't a good idea but it looks like you've already had that discussion in the recent "Multiple DB join" thread. http://archives.postgresql.org/pgsql-sql/2006-08/msg00097.php -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] About DBlink
Thanks Micheal I dont have dblink installed, I need to find a good documentation which will help me do this and also the place where i can download this module.Thanks,Sumeet. On 8/17/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Aug 17, 2006 at 04:37:03PM -0400, Sumeet wrote:> Im trying to find out documentation for postgres module names "dblink", can> anyone point me out to it. What i want to do is join multiple databases > instead of multiple tables.If you've installed dblink then somewhere on your system should bea file named README.dblink. How to find that file depends on theplatform: on many Unix-like systems you can use "locate" or "find", or if you installed dblink via a package then you could use thepackage management tool to display a list of files that it installed.I'd mention that doing joins between databases isn't a good ideabut it looks like you've already had that discussion in the recent "Multiple DB join" thread.http://archives.postgresql.org/pgsql-sql/2006-08/msg00097.php--Michael Fuhr---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings-- Thanks,Sumeet AmbreMasters of Information Science Candidate,Indiana University.
Re: [SQL] About DBlink
> Thanks Micheal I dont have dblink installed, I need to find a good > documentation which will help me do this and also the place where i > can download this module. dblink is a contrib module, that means it is part of the postgresql source code distribution. you can read README.dblink online at http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/contrib/dblink/README.dblink?rev=1.12.4.1;content-type=text%2Fplain Bye, Chris. -- Chris Mair http://www.1006.org ---(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
Re: [SQL] About DBlink
On Thu, Aug 17, 2006 at 11:25:30PM +0200, Chris Mair wrote: > > Thanks Micheal I dont have dblink installed, I need to find a good > > documentation which will help me do this and also the place where i > > can download this module. > > dblink is a contrib module, that means it is part of the postgresql > source code distribution. If you install PostgreSQL via packages then the contrib modules might be in a package other than the base installation. If so then the package will typically have the word "contrib" in its name. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help with optional parameters
This seems to be the best solution. At first I was a bit skeptical about the perfomance using execute versus using pre-planned queries. However, I found that this model actually executes faster than the paramterized queries that I was trying (even using the if-then-endif model). Thanks for this tip. On 8/17/06, MaXX <[EMAIL PROTECTED]> wrote: Rob Tester wrote:> I have the need to have optional values for a query in a stored procedure> that I am building. (using postgres 8.1.4). This particular query> executes> against a huge table (several million rows) and has six optional> parameters> that can be sent to the function. If one of the parameters is null> then the > parameter doesn't need to be included in the query. Also the values in> the> columns that are optional can contain NULL values. One way that will work> (although extremely cumbersome) is to have a switch on the parameters to > execute the correct query:>> --This is a sample> IF (a IS NULL AND b IS NULL) THEN>select * from my_table;> ELSEIF (a IS NOT NULL and b IS NULL) THEN>select * from my_table where a=parama; > ELSEIF (a IS NULL and b IS NOT NULL) THEN>select * from my_table where b=paramb;> ELSE>select * from my_table where a=parama AND b=paramb;> ENDIF;>> This is extremely bad when you have 6 parameters giving 64 possible > queries.>You can try something like this, it should show the basis,CREATE OR REPLACE FUNCTION test(int4, int4) RETURNS text AS$BODY$DECLAREa ALIAS FOR $1;b ALIAS FOR $2; -- add as many as input paramquery_base text;has_param bool;query_where text;query_final text;BEGINquery_base := 'SELECT * FROM my_table ';has_param := FALSE;query_where := ''; IF (a IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'parama='||a; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 paramEND IF; IF (b IS NOT NULL) THEN IF (has_param IS FALSE)THEN -- there is no param yet add WHERE to the query query_where := ' WHERE '; ELSE -- there is already something in the WHERE clause, we need to add AND query_where := query_where || ' AND '; END IF; query_where := query_where || 'paramb='||b; --beware if param quoting is required has_param := TRUE; -- now there is at least 1 paramEND IF; --copy/paste/edit this IF ENDIF block for each paramquery_final := query_base || query_where;RAISE NOTICE '%', query_final;RETURN query_final;-- EXECUTE query_final;--END;$BODY$LANGUAGE 'plpgsql' VOLATILE; test:select test(null,null)union select test(1,null)union select test(1,1)union select test(null,1);result:"SELECT * FROM my_table ""SELECT * FROM my_table WHERE parama=1" "SELECT * FROM my_table WHERE parama=1 AND paramb=1""SELECT * FROM my_table WHERE paramb=1"HTH,--MaXX
