Re: [SQL] CoreReader
I really do need to develop some people skills. At least enough to avoid the ego bruises. CoreReader is a free download at http://corereader.com It does point and click queries. It runs from a windows front end, but is designed to connect to any data source on any computer. It has run against mainframes, spreadsheets, mysql, paradox, etc., but nobody has reported a Postgrsql connection, and I have not had time. It should work fine, but after spending long hours this weekend fixing a bug that manifested only in certain kinds of Oracle connections... > On Fri, Oct 25, 2002 at 10:32:00PM -0500, John Ragan wrote: > > > > Does anybody know of CoreReader being run > > against PostGresql? > > > > If so, I would appreciate knowing the > > results. > > What's CoreReader? > > -Roberto > > -- > +|Roberto Mello -http://www.brasileiro.net/ |--+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Creating Stored Procedures
Hi there, We are developing a large application which uses up to 500 Stored Procedures. Because we need a good but cheap database for when we sell our application to customers we decided to try porting our applications Database Management System to PostgreSQL. Our application is writting in .NET and uses ADO.NET, odbc for accessing the database. We have already ported our DBMS from MS SQL to Oracle in which we succeeded. So the next major step is the port to PostgreSQL and the PL/SQL to PLpg/SQL or SQL procedural language. Because we want to hide our database stuff from our business logics we decided to make use of Stored Procedures. I have already noticed that PostgreSQL only knows about Functions. The support for Stored Procedures can done by using functions. Am I right on this? The problem that I am having is that I have not find a way to return a Tuple or a RecordSet. Our Stored Procedures return for example the result of 'SELECT * FROM TABLE_X'. Our application uses a SqlConnection and a SqlDataAdaptor to fill a DataView (that last information is very .NET specific, I know. This might indeed be a .NET question to but I hope that, because we all are professionals in here, this will not create a "I like this Programming Language more then your stupid .NET" flamewar). We dislike changing sources and prefer changing/manipulating Stored Procedures or Functions so that the sources need less or no changes at all. Some people have advised us to start using Views instead of Stored Procedures. But that would mean to much SourceCcode changes. At this moment we have a source that works pretty good. We are satisfied with the speed and performance. So only PostgreSQL cannot be a good reason for us to redesign a lot of the Database Issues (Remember that we have +500 of such Stored Procedures to convert and not VERY much time to do this port -we have one month, thats it-). Is there a PostgreSQL version that can do Stored Procedures and return Tuples or RecordSets like MSSQL and Oracle can? And/or is there a way to create a function that returns a Tuple and/or a RecordSet that we can use in .NET (convert the result to a DataView)? Regretfully our commercial guys are not very pro Free Software products. They would prefer using MSDE as primary 'cheap' DBMS. We, the developers, dislike MSDE because then we are still stuck in the Microsoft MS SQL world. And there will be no way to get out of it. Also is MSDE not very usable for more then three users and will MSDE make our customer pay a lot for MS SQL when he or she wants to upgrade to more users -> that sucks. Maybe the use of Stored Procedures that return RecordSets actually 'was' a design problem of ours but we have now reached a point that we cannot go back and start changing such stuff ... The person who pays us would not like it I fear :-). So how much you guys would like us to start using Views, it will probably not happen then. I guess, if that would be the only option, MSDE will be used; period. :-) Which would suck... *ahum* Note that I have searched A LOT on google about this subject and I do know that this probably is a FAQ. But I also have not yet found any answer that we can actually use :). We have no problem with using beta versions. There is one requirement with betas : the version must run on both Linux and Windows NT (using CygWin if that is required, is no problem for us). ps. If I am posting to the wrong mailinglist or persons, feel free to send me pointers to the right mailinglist. -- Philip van Hoof aka freax (http://www.freax.eu.org) irc: irc.openprojects.net mailto:me at freax dot org Go not to the Elves for counsel, for they will say both no and yes. ---(end of broadcast)--- TIP 3: 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] BOOLEAN question
Josh Berkus <[EMAIL PROTECTED]> writes: > Odd question: I have a query that returns a set of none to several rows. One > column in these rows is BOOLEAN. I want the query to return: > 1) TRUE if *any* of the BOOLEAN values is TRUE; > 2) FALSE if *all* of the BOOLEAN values are FALSE; > 3) FALSE or NULL if no rows are returned. Perhaps SELECT true = ANY (SELECT boolcol FROM ...); or SELECT true IN (SELECT boolcol FROM ...); Which is not to say that MAX(bool) might not be a nicer solution; but you can definitely do it with SQL-spec constructs. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Stored Procedures
On 29 Oct 2002, Philip Van Hoof wrote: > Note that I have searched A LOT on google about this subject and I do > know that this probably is a FAQ. But I also have not yet found any > answer that we can actually use :). We have no problem with using beta > versions. There is one requirement with betas : the version must run on > both Linux and Windows NT (using CygWin if that is required, is no > problem for us). You might want to check out 7.3beta3 since it has support for functions that return datasets. Since you don't want to have any source changes, I'm not sure if the interface to such is the same as in the other two dbs mentioned. It should run with cygwin as well with a little bit of work (see recent messages on hackers) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] celko nested set functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message > I'm wondering if anyone has written code that enables you to move > entities between parents in a nested set model. Specifically something > that can do it without deleting any of the children involved in the > process. I looked in the postgresql cookbook which had adding and > building tree's, but not moving. I'm hoping to find something > postgresql specific but if not that's ok. Thanks in advance, CREATE FUNCTION MoveTree (integer, integer) RETURNS text AS ' -- Moves part of a nested set tree to another part. -- Pass in the left of the child (from) and the left of the parent (to) DECLARE cleft INTEGER; cright INTEGER; pleft INTEGER; pright INTEGER; leftbound INTEGER; rightbound INTEGER; treeshift INTEGER; cwidth INTEGER; BEGIN SELECT lft, rht FROM tree WHERE lft = $1 INTO cleft, cright; SELECT lft, rht FROM tree WHERE lft = $2 INTO pleft, pright; -- Make sure the child exists IF cleft IS NULL THEN RETURN ''No entry found with a left of ''||$1; END IF; -- Make sure the parent exists IF pleft IS NULL THEN RETURN ''No entry found with a left of ''||$2; END IF; -- Self-move makes no sense IF cleft = pleft THEN RETURN ''Cannot move: entries are identical''; END IF; -- Parent cannot be underneath the child IF pleft BETWEEN cleft AND cright THEN RETURN ''Cannot move: first entry contains second''; END IF; -- Child may already be in the proper place IF cleft = pleft+1 THEN RETURN ''No changes need to be made''; END IF; IF cleft > pleft THEN treeshift := pleft - cleft + 1; leftbound := pleft+1; rightbound := cleft-1; cwidth := cright-cleft+1; ELSE treeshift := pleft - cright; leftbound := cright + 1; rightbound := pleft; cwidth := cleft-cright-1; END IF; UPDATE tree SET lft = CASE WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth WHEN lft BETWEEN cleft AND cright THEN lft + treeshift ELSE lft END, rht = CASE WHEN rht BETWEEN leftbound AND rightbound THEN rht + cwidth WHEN rht BETWEEN cleft AND cright THEN rht + treeshift ELSE rht END; RETURN ''Tree has been moved''; END; ' LANGUAGE 'plpgsql'; Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200210291424 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE9vuDNvJuQZxSWSsgRApruAJ0bD2XyonsYNHV+XVEBYqJji3jxygCfeVk/ 27Cl7rTs5bQAkyBQXuXl3mw= =MZbR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] BOOLEAN question
Tom, > Perhaps > SELECT true = ANY (SELECT boolcol FROM ...); > or > SELECT true IN (SELECT boolcol FROM ...); > > Which is not to say that MAX(bool) might not be a nicer solution; > but you can definitely do it with SQL-spec constructs. Based on some rough testing, SELECT true = ANY ( SELECT boolcol FROM complex query ) Is marginlly faster than SELECT max(boolcol) FROM complex query With a custom MAX(boolean) function. So I'll stick to ANY(). -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] BOOLEAN question
Josh Berkus writes: > I wrote a MAX(bool), what are the chances it would get added to the core? I > don't see any good reason not to have one. One reason not to have one is that Boolean values are not naturally ordered, so a maximum doesn't exist. (Yes, I know there are comparison operators defined for boolean, but that doesn't make it right.) What seems more reasonable is to define conjuntion and disjunction aggregates, which would mostly do the same thing but their semantics wouldn't be as controversial. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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
[SQL] Selecting * from the base table but getting the inheriteds columns
Hello, I have a "baseTable". I have a number of decendantTables that INHERIT from the base table. I want to do somthing like =>select * from baseTable; But I want it to return the data in the all the descendant tables along with the extra column that they contain. How do I do this? please help many thanks for your time James
Re: [SQL] BOOLEAN question
On Tue, Oct 29, 2002 at 23:19:55 +0100, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Josh Berkus writes: > > > I wrote a MAX(bool), what are the chances it would get added to the core? I > > don't see any good reason not to have one. > > One reason not to have one is that Boolean values are not naturally > ordered, so a maximum doesn't exist. (Yes, I know there are comparison > operators defined for boolean, but that doesn't make it right.) > > What seems more reasonable is to define conjuntion and disjunction > aggregates, which would mostly do the same thing but their semantics > wouldn't be as controversial. However they would be slow. If you use an aggregate you don't get to short circuit the calculation. And for even distributed true and false values, most of the time the aggregate result would be known after just a few rows were checked. When checking for such values in long lived tables where one of the values occurs seldomly, using a partial index and a select could be a big win. I don't think this will work in the case that started this thread since it postulated checking the output of a complex query. However even in this case it may be worthwhile to rewrite the query as an exists test for boolcol to be the short circuit value so that the complex query need not be fully calculated. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Database Design tool
Hi. Can anybody take me reference on Database design tool with PostgreSQL support. thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
