Re: [SQL] Selecting * from the base table but getting the inheriteds
Uz.ytkownik James Adams napisa?: 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? select *, cast(null as varchar) as another_field1, cast(null as int4) as another_field2 from only basetable union select * from descent_table Isn't it easier to just create baseTable with the same columns as descent tables (and fill them with nulls)? Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Creating Stored Procedures
As far as I know functions can not return recordsets. Try functions returning cursors (then FETCH ALL IN..), or temporary tables. regards Joseph - Original Message - From: Philip Van Hoof [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 29, 2002 7:23 PM Subject: [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 ---(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] Database Design tool
Can anybody take me reference on Database design tool with PostgreSQL support. visit, http://sourceforge.net/projects/phppgadmin/ regards, bhuvaneswaran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating Stored Procedures
From: Stephan Szabo [EMAIL PROTECTED] To: Philip Van Hoof [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [SQL] Creating Stored Procedures Date: 29 Oct 2002 10:36:17 -0800 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]) I installed 7.3beta3 but how do I do it now? How do I make a function that returns a dataset Greets Zertox ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Selecting * from the base table but getting the inheriteds
Yea it would be easier to have everything in one table filling unused with nulls, but I was trying to avoid that because of the wasted space. But I think I'll do it that way after all :~] Thanks for your help - Original Message - From: Tomasz Myrta [EMAIL PROTECTED] To: James Adams [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 30, 2002 9:05 AM Subject: Re: [SQL] Selecting * from the base table but getting the inheriteds Uz.ytkownik James Adams napisa?: 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? select *, cast(null as varchar) as another_field1, cast(null as int4) as another_field2 from only basetable union select * from descent_table Isn't it easier to just create baseTable with the same columns as descent tables (and fill them with nulls)? Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Database Design tool
Uz.ytkownik Viacheslav N Tararin napisa?: Hi. Can anybody take me reference on Database design tool with PostgreSQL support. pgadmin.postgresql.org Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Creating Stored Procedures
I installed 7.3beta3 but how do I do it now? How do I make a function that returns a dataset Greets Zertox He is an email I received from Joe Conway on the subject a month or so ago Adam Witney wrote: There have been a few emails recently concerning using functions. However I am a little confused as to their use with composite types. I can see how to return a whole row from a table, but is it possible to return multiple fields that do not originate from the same table? Sure. But you either need a named composite type that matches the row you want to return, or you can use a record datatype and specify the column definitions in the sql statement at run time. A composite type exists for each table and view in your database, as well as any stand-alone composite types you define. So, for example: test=# create table foo (f1 int,f2 text); CREATE TABLE test=# create table bar (f3 int,f4 text); CREATE TABLE test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3; CREATE VIEW test=# insert into foo values(1,'a'); INSERT 1105496 1 test=# insert into foo values(2,'b'); INSERT 1105497 1 test=# insert into bar values(1,'c'); INSERT 1105498 1 test=# insert into bar values(2,'d'); INSERT 1105499 1 -- This uses a named composite type based on the view test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4 from foo, bar where f1=f3' language sql; CREATE FUNCTION test=# select * from getfoobar1(); f1 | f2 | f4 ++ 1 | a | c 2 | b | d (2 rows) -- This uses an anonymous composite type specified at runtime test=# create function getfoobar2() returns setof record as 'select f1,f2,f4 from foo, bar where f1=f3' language sql; CREATE FUNCTION test=# select * from getfoobar2() as (f1 int,f2 text,f4 text); f1 | f2 | f4 ++ 1 | a | c 2 | b | d (2 rows) HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Database Design tool
On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote: Hi, (which list should this go to? I guess it is OT on both...) Can anybody take me reference on Database design tool with PostgreSQL support. Dia and dia2sql (or something similar...) Google knows more :-) HTH Johannes Lochmann ---(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] BOOLEAN question
Why not simply: SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE); JLL Josh Berkus wrote: 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] ---(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] BOOLEAN question
Of course, I meant SELECT COALESCE( (SELECT true FROM ... WHERE ... AND boolcol LIMIT 1), FALSE); Jean-Luc Lachance wrote: Why not simply: SELECT COALESCE( (SELECT true FROM ... WHERE boolcol LIMIT 1), FALSE); JLL Josh Berkus wrote: Tom, Perhaps SELECT true = ANY (SELECT boolcol FROM ...); or SELECT true IN (SELECT boolcol FROM ...); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] unnecessary updates
When doing database work over the web, especially when many records are on one page, *many* updates get posted to pg that do not change the record. Eg, the page may contain 50 records, the user changes 1, and submits. I assume that a no-change update takes the same resources as a real update, ie, a new block is allocated to write the record, the record written, indicies are rerouted to the new block, and the old block needs to be vacuumed later. Is this true? In SQL, the only way I know to prevent this thrashing is to write the update with an elaborate where clause, eg, update ... where pk=1 and (c1!='v1' or c2!='v2' or ... ). This adds cost both to the app server and to pg - is the cost justified? Finally, is there anyway to flag pg to ignore no-change updates? This seems to me to me the most efficient way of handling the needless work. thanks chester __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] unnecessary updates
One strategy is to use some sort of middleware that takes care of this. On a project I did a few years ago, I used a perl module that read the record from Postgres and made it into a perl object. The object contained a variable, changed, that reflected whether anything had actually changed in the object. Finally, there was an object method put() that took care of updating the database. put() checked the changed property and simply silently finished unless changed was true. ap -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Wed, 30 Oct 2002, chester c young wrote: When doing database work over the web, especially when many records are on one page, *many* updates get posted to pg that do not change the record. Eg, the page may contain 50 records, the user changes 1, and submits. I assume that a no-change update takes the same resources as a real update, ie, a new block is allocated to write the record, the record written, indicies are rerouted to the new block, and the old block needs to be vacuumed later. Is this true? In SQL, the only way I know to prevent this thrashing is to write the update with an elaborate where clause, eg, update ... where pk=1 and (c1!='v1' or c2!='v2' or ... ). This adds cost both to the app server and to pg - is the cost justified? Finally, is there anyway to flag pg to ignore no-change updates? This seems to me to me the most efficient way of handling the needless work. thanks chester __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] How do I get rid of these messages?
How do I get rid of the messages like NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table 'test' coming out from stderr when I run psql with my create table script? (I don't want to direct them to /dev/null, since I still want to be able to see the *real* sql error messages) Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Database Design tool
My colleague installed a redhat database 2(which is based on postgresql) onto his redhat 7.3 machine and it came with a very nice java query analyzer tool. It is sort of like the query analyzer in MS SQL Server with the graphical output of the execution plan, quite intuitive. I wonder if I can find a standalone java query analyzer package. Does anyone know? Thanks On Wed, 2002-10-30 at 15:42, Kevin Old wrote: Dan, I use Xpga Java Postgresql client and it is awesome. It's at http://www.kazak.ws/xpg/ Kevin On Wed, 2002-10-30 at 10:30, Dan Hrabarchuk wrote: gASQL is a gnome-db client that looks like it has a lot of promise. The only problem is I've never been able to get the application to run properly. I'm using RedHat 8.0 on my desktop. The last official version does not install properly. If I grab a CVS copy, I go through dependency hell. Has anyone ever actually gotten gASQL to work? Dan On Wed, 2002-10-30 at 06:35, Johannes Lochmann wrote: On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote: Hi, (which list should this go to? I guess it is OT on both...) Can anybody take me reference on Database design tool with PostgreSQL support. Dia and dia2sql (or something similar...) Google knows more :-) HTH Johannes Lochmann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Kevin Old [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Database Design tool
Wei, My colleague installed a redhat database 2(which is based on postgresql) onto his redhat 7.3 machine and it came with a very nice java query analyzer tool. It is sort of like the query analyzer in MS SQL Server with the graphical output of the execution plan, quite intuitive. I wonder if I can find a standalone java query analyzer package. Does anyone know? I believe that Red Hat offers the query analyzer as a download. Check their site. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Database Design tool
Wei, Do you have any idea of what the software is called? I could only find the redhat database ISO for download. Announced on PGSQL-ANNOUNCE a couple of weeks ago: (if you're not subscribed to PGSQL-ANNOUNCE, you should be) === The Red Hat Database Graphical Tools, RHDB Administrator and Visual Explain, are now available via anonymous CVS checkout from http://sources.redhat.com/rhdb. The RHDB development team has imported the inaugural version of the tools into the RHDB Project Community Tree and will continually update the tree as internal development progresses. We have also opened development of the tools to the PostgreSQL community and will be accepting bug reports / patches at [EMAIL PROTECTED] The RHDB tools were tested and verified against PostgreSQL 7.2.x clusters. === -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] time limit of a database connection
I use a window client to conncet to a postgreSQL server with windows ODBC. It seems if I haven't done anything on the connection for hours, the connection will be dropped. Is that possible to set the time limit of a connection? Thank you in advance! Jack ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
