[SQL] Escaping the $1 parameter in stored procedures
I'm running Postgres 7.3.2 in Redhat 9.0. I'm trying to execute a function below defined as a stored procedure ALTER TABLE tms_schedule DROP CONSTRAINT "$1"; However, postgres thinks the "$1" is a parameter value. How do I tell postgres to treat it as a literal $1? TIA, Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Escaping the $1 parameter in stored procedures
Found a solution:
-- my_constraint(0) = turn off constraint
-- my_constraint(1) = turn ON constraint
CREATE OR REPLACE FUNCTION my_constraint(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
cmd VARCHAR;
BEGIN
IF $1 = 0
THEN
RAISE NOTICE ''Turning OFF constraints'';
cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$1"'';
EXECUTE cmd;
cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$2"'';
EXECUTE cmd;
ELSE
RAISE NOTICE ''Turning ON constraints'';
ALTER TABLE $tName
ADD FOREIGN KEY(key1) REFERENCES table1;
ALTER TABLE $tName
ADD FOREIGN KEY(key2) REFERENCES table2;
END IF;
RETURN ''OK'';
END;'
LANGUAGE plpgsql;"
[EMAIL PROTECTED] (robert) wrote in message news:<[EMAIL PROTECTED]>...
> I'm running Postgres 7.3.2 in Redhat 9.0.
>
> I'm trying to execute a function below defined as a stored procedure
>
>ALTER TABLE tms_schedule DROP CONSTRAINT "$1";
>
> However, postgres thinks the "$1" is a parameter value. How do I tell
> postgres to treat it as a literal $1?
>
> TIA,
> Robert
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Re: BLOB HOWTO??
Maybe the functions lo_import and lo_export is what you're looking for? regards, robert gravsjo Olivier PRENANT wrote: > Hi, > > Beeing very impressed by TOAST, I wonder how I can insert BLOB from a flat > file. > > It seems quite easy with php; But how can I do it from psql?? > > TIA >
[SQL] Newbie ex-Oracle person's question: Oracle ROWID = PSQL OID, Oracle ROWNUM = PSQL ???
Oracle has a ROWNUM pseudo column that works like this ... TEST>select callid, rownum from cs_calls where rownum < 5; CALLID ROWNUM -- -- 7806 1 7807 2 7809 3 6443 4 4 rows selected. ... which can be quite handy. Is there something like this in PSQL? By the way, having used Oracle since 1987 it's a pleasure to use PSQL. Someone actually thinks about the implemented features. For example, Oracle's implementation of ROWNUM gives them in the order the rows were BEFORE the ORDER BY, which is not of much use as adding an ORDER BY jumbles them up. Duh! Thanks in advance for any responses. ---(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] how do i import my sql query result to a file
not sure I understand the question, but from inside psql you can do: \o FILENAMEsend all query results to file or |pipe Robert Treat On Thu, 2002-07-18 at 17:47, Joseph Syjuco wrote: > how do i import results of my select query to a file > thanks > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] No command history in psql
ldd /path/to/psql should tell you if its finding the readline libraries. you might also want to do ldconfig -v | grep 'history' and ldconfig -v | grep 'readline' to see what libraries you have. you can then verify that the libs you have are actually where your telling pg they are supposed to be. Robert Treat On Tue, 2002-07-23 at 10:53, Carmen Wai wrote: > Hello: > > I am upgrading to postgresql version 7.2.1. I found that the psql has not > included the readline library automatically and doesn't have any readline > and history command function. So I configure the postgresql with option > --with-includes=/usr/local/include and --with-libs=/usr/libs/ so that it > should be able to search for the corresponding library and header files > (libreadline.a, history.h, readline.h). But it still fails Does anyone > get any idea?? > > Thanks a lot! > > _ > Send and receive Hotmail on your mobile device: http://mobile.msn.com > > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] how do I change regional setting for dates?
You can do "show all" inside psql, which will show you all of your current run time settings, including a line that should read "Time Zone is unset" or whatever it is set to. This isn't abundantly clear in the docs, so I'll add some notation there, but if you check out http://www.postgresql.org/idocs/index.php?sql-set.html and http://www.postgresql.org/idocs/index.php?timezones.html it will explain how to update your internal timezone as needed. Robert Treat On Wed, 2002-07-24 at 15:38, Ligia Pimentel wrote: > I live in Guatemala, and our timezone is GMT -6, but my server (postgres 7.1 > on a linux redhat 6.2) is storing the dates in the format > > 2002-07-24 00:00:00-04 ( I this understand represents GMT -4). > > My linux servers gives me the the date and time correctly, so I know is not > a matter of the date of the server, and I checked and its GMT-6 (which is > right) . > > How do I change the regional setting in postgres? > > > Thanks, > > Ligia > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Is this valid?
I'm going to ask the crazy question of what language/interface are you using to interact with postgres? Based on my interpretation of your question I'd say that won't break (though one of your queries might fail) but then again I may be totally misreading what you wrote... Robert Treat On Mon, 2002-08-12 at 19:21, Wei Weng wrote: > I am not sure if this is the right mailing list I talk to. Please let me > know if I had violated any unwritten rules. :) > > I have a global variable PGconn* m_pgconn that is the connection handle > for connecting to the postgresql database. Can I access/use the handle > from multiple threads? Say I have a thread that does some insertion > through this handle/database connection(m_pgconn) and also another > thread that do some insertion *on the same table* through this > handle(m_pgconn), will that break? > > Thanks > > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Event recurrence - in database or in application code ????
On Tue, 2002-08-20 at 22:15, Mark Stosberg wrote: > > Hello Darrin, > > > I've been happy with this solution. I think the Perl turned out to be > fairly easy to understand and maintain, the SQL that needs to be used > ends up being fairly straightforward, and the performance is good > because the selects to view the calendar are fairly simple. The one > drawback is that sometime before 2028, I have to remember to add some > rows to the calendar table. :) > You need to add rows as well as re-populate a bunch of info for recurring dates that are not listed forward right? > -mark > > http://mark.stosberg.com/ > > > On Tue, 20 Aug 2002, Darrin Domoney wrote: > > > One of the features that I am attempting to implement in the system that I > > am building is > > the capability to schedule events (with recurrence). My question to those of > > you that are > > more experienced in postgresql is whether you would implement this > > functionality in the > > database level using triggers or at the application code level (PHP). > > > > > > Any suggestions, etc gratefully appreciated. > > I would strongly recommend you look at the "WebCalendar" project on sourceforge. Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
In psuedo-code : create rule on mytable on delete return null Robert Treat On Thu, 2002-09-26 at 15:00, Rajesh Kumar Mallah. wrote: > Hi , > > I have a created a database and a table in it, > > I want to prevent "DELETES" on the table in this > database by everyone except superuser postgres. > even by me (the creator of this database and table) > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > but i always end up with having the permission > > > can any one tell me how the prevention can be accomplished? > > thanks in advance. > > regds > mallah. > > > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---(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
[SQL] celko nested set functions
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, Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] psql history
On Mon, 2002-10-28 at 14:57, [EMAIL PROTECTED] wrote: > Hi everibody, > i have installed Postgres 7.2.2 from a tarball, but using psql i can not > have the history of the last command. > When i used Postgres from rpm this useful element worked very well!> > Why that? > I didn't see any other response to this, but your problem is that for some reason when you built the file from the tarball, it didn't pick up on your readline utilities. You might want to check your configure output, it might hold a clue as to what went wrong. If not, do a search on the archives (probably the general list would be more fruitful) as this comes up quite often. Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] HA PostgreSQL
On Fri, 2002-11-01 at 13:26, Charles H. Woloszynski wrote: > I am trying to develop a plan for a high-availability (HA) > implementation of a database using PostgreSQL. One wrinkle; the data we > receive is performance data, and occassional loss of some measurements > is Ok for us. [I know, this is not in the main stream of database users > :-)]. > > I have looked ar rserv and pg-replicator, and they seem to be targeted > at replication without specific HA support. Replication is great for > lots of things; but I need HA more than ACID replication. > > I have seen a proposed solution that uses *rsync* on the database files > between machines and linux-ha to roll over the network access to the > available machine. My question is pretty simple; can something as > *simple* as rsync make a full copy of the database consistently between > the machines? That seems just too easy. > Well, some folks have argued that it is likely to cause extreme data corruption, my understanding being that if even one file is off your going to end up in trouble. Granted, I don't know that this has ever been proven one way or the other, but I buy the argument enough not to rely on it. > If I replace the process with something that uses pg_dump and rsync that > file (ok, now that seems more likely to generate a consistent database > image) that and restore that into the slave, does this work? Obviously, > this approach is kinda a hammer approach; the poor active server will be > dumping till the cows come home. it does seem more likely to give you a consistent db image, but depending on the size of your database your going to have some delay between snapshots depending how long it takes to dump the database. On a small database this might be acceptable, on a large enough database it will be unworkable. I suppose you could run concurrent pg_dump's, but I'd hate to think what that might do to the poor db server. Don't forget you'll have an issue actually loading the data onto the secondary sever as well. Personally I think you'd be better off setting up some type of master/slave replication and devise some networking fail over scripts to switch hosts if need be. > > Any and all feedback and comments are greatly appreciated. And, as > always, thanks in advance, > If you haven't looked at dbbalancer yet, you might want to. Someone mentioned it just a few days ago in a very similar thread on the general list (iirc) Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [SQL] Database Design tool
There are actually two sections on the techdocs site now relating to this at http://techdocs.postgresql.org/oresources.php look under the sections ERD Tools and Database Design Not that I'm not looking forward to your article Josh ;-) Robert Treat On Tue, 2002-11-05 at 11:33, Josh Berkus wrote: > Thomas, > > > > DBVisualizer is pretty good. > > > > Is there a page at PostgreSQL.org where all these links are listed? > > I've been saving each bit of mail that goes by on this thread...but > > a page on Pg would be very nice. ;-) > > I was planning on writing an article; so far, I have a list of about 14 > interfaces. > > When done, the article will go up at TechDocs or the Postgres Wiki > (currently under development). > > -Josh > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(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: [GENERAL] [SQL] Database Design tool
Well, actually ERWin does work with PostgreSQL via ODBC. The version I tried was I think the last release of the 3.x series, and I had enough trouble with it to decide against using it. I am told the 4.x series improved things, though if your already familiar with erwin you probably could get by. Instead I decided to use Case Studio 2.x, which is a pretty good replacement imo. (For the record though, it is neither free nor does it run on linux). Robert Treat On Tue, 2002-11-05 at 15:16, Kaare Rasmussen wrote: > > Thanks. This gives me a few more tools to look at. Boy, do we have an > > answer for the folks who complain "there are no GUI tools for Postgres". > > While there are a number of GUI tools for PostgreSQL, there's still no real > database design tool like ErWin or so. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] changing numeric into int
There is no way to do this short of maybe hacking at the system tables, and even then thats likely to cause problems. If you cant recreate the table, your best bet is to create two new columns and copy the data in, delete the data from the old columns, rename the columns accordingly, and perhaps add a rule to always set the old columns to NULL. Robert Treat On Fri, 2002-11-08 at 02:41, Huub wrote: > Hi, > > I want to change 2 columns in the same table from numeric into int. Can > I do this without deleting the old table and creating a new one? Data > stays the same.. > > Thanks > > Huub > > > ---(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] Permission on insert rules
On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote: > Josh Berkus wrote: > > > > Luis, > > > > > Just a question. > > > I'm writing some rules to insert/update some data in my database, and I > > > gave all the privileges on that view to the user, and only select on the > > > tables. > > > When that user inserts data using the view, I thought that was user > > > postgres that will do the rest ! But I got permission denied on those > > > tables. > > > The idea was to create a layer, with the views, giving to that user > > > permission on views to insert and update, and not to tables. > > > Is this possible ? > > > > This is a known problem. > > > > I know that permissions for Functions has been addressed in 7.3. However, I > > am not sure about permissions for updatable views. Tom, Bruce? > > Views have always had their own permissions. > If the functions can fire as there creator instead of there caller, then I would think as long as the creator has insert/update views on the base table, you should be able to do updateable rules and give only permissions to the view for the caller. (Though maybe you have to use triggers rather than rules to do this?) Does that sound right? Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Permission on insert rules
This should be a test case for what Luis wants, although it works in 7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll need to post some code: create table parent (id int, name text, misc text); create view child as select id,name from parent; create rule jammasterjay as on insert to child do instead insert into parent values (new.id,new.name); insert into parent values (1,'one','wahad'); insert into parent values (2,'two','ithnain'); insert into parent values (3,'three','thalata'); select * from parent; select * from child; insert into child (4,'four'); select * from parent; create user mellymel; grant select on child to mellymel; grant insert on child to mellymel; ** reconnect as mellymel ** select * from parent; (generates error) select * from child; insert into child values (5,'five'); select * from child; (has all 5 rows) Robert Treat On Tue, 2002-11-12 at 12:29, Josh Berkus wrote: > Luis, > > > That's what I already made. The problem is when I do the update, I > > permission denied in all the tables for update and insert. The user > > that's making this operation only have select privilege. > > Any way, I'm using version 7.2.1-2 for debian. > > I can't reproduce the problem, and permissions did not get fixed > between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing > something, somewhere. > > Please post: > > 1) The table definitions for the tables being updated. > 2) The view definition and permissions > 3) The Rules statements defined on the view > 4) A copy of your database session where your update is denied, > including the exact error message received. > > Without that information, no futher help is available. > > -Josh Berkus > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RE: [SQL] System´s database table
There's no sense waiting as 7.3 is about to go RC1, and only the most improbable circumstances would lead to a system catalog change at this point. (If you do make a diagram, please post it to the group ) Robert Treat On Wed, 2002-11-13 at 16:28, Jean-Luc Lachance wrote: > Thanks, I know about that. > > I was just hoping for a nice diagram. > I guess I will have to wait for 7.3 anyhow if I do not want to waste my > time doing one for 7.2 > > JLL > > Paul Ogden wrote: > > > > It's not ERD but I've found the information in the Developer's Guide > > regarding system catalogs to be useful in the past. > > > > This http://www.postgresql.org/idocs/index.php?catalogs.html will > > get you started. > > > > Thanks, > > > > Paul Ogden > > Claresco Corporation > > > > > -Original Message- > > > From: [EMAIL PROTECTED] > > > [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Jean-Luc Lachance > > > Sent: Wednesday, November 13, 2002 12:37 > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: [SQL] System´s database table > > > > > > > > > While we are on the subject, > > > is there any ERD of the system's table somewhere? > > > > > > JLL > > > > > > > > > Josh Berkus wrote: > > > > > > > > Pedro, > > > > > > > > > I´m looking for the name of the table that contains all > > > databases in my > > > > system. I already see this in the postgre manual, but i´m > > > forgot where > > > > > > > > pg_database > > > > > > > > -- > > > > -Josh Berkus > > > > Aglio Database Solutions > > > > San Francisco > > > > > > > > ---(end of broadcast)--- > > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > ---(end of broadcast)--- > > > TIP 4: Don't 'kill -9' the postmaster > > > > ---(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]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] celko nested set functions -- tree move
I think you should take a closer look at Greg's function. It is uses lfts as parameters in the function mainly just to make the function implementation independent; I was able to easily adapt it to my schema which uses unique id's for each object in the tree hierarchy. After looking your function over, I also have some concerns about moving children to new parents with lft and rgt smaller than the child, because the math is different depending on which way your moving in the tree. It's possible that your use of treeid's and universe id's makes up for this, though it's a little hard to discern without seeing the schema, perhaps you can post schema and some test data? I'm also curious how many nodes you have in your tree, and at how many levels. It seems like your function would have performance issues over large trees since it requires 3 select statements, 3 updates, and a lock table. Compare this with Greg's function which requires 2 selects and 1 update, with no lock. As a final note, you might want to rewrite your select statements like: SELECT rgt, universeid, treeid FROM list_objects WHERE objid_auto=t_newparent INTO newparentrgt, newparentuid, newparenttid; I think it's more readable and probably a little more efficient since you are doing less variable assignment. Robert Treat On Tue, 2002-11-26 at 00:13, Martin Crundall wrote: > I'm not sure that keying off lft is safe in a multi-user environment. I > opted to create and use an objid on the tree definition table, since its > identity is static. I also found that when trees get active, allowing for > tree IDs increased operation speed quite a bit (i actually push this to > two levels--a 'universe id' and then a 'tree id'). Here's my version. > Clearly not as elegantly written, but nothing's gone awry yet. > > -- > --- > --Title: trackmyproject_tree_move() > -- Function: moves a tree branch in the hierarchy from one parent to > -- another. > --parms: srcobj the branch/object to be moved > -- newparentthe new parent for the object to be moved > -- Returns: zero > -- > --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Ran out of connections
Once your done scoping other things out, you might also want to look at increasing the number of allowed connections (in postgresql.conf). The defaults can be low for high traffic systems. Robert Treat On Wed, 2002-12-04 at 17:29, Steve Crawford wrote: > You probably didn't need to reboot - I suspect you could have probably > restarted PostgreSQL and Apache (quick version) or killed the extra postgres > processes. > > > Cheers, > Steve > > > On Wednesday 04 December 2002 2:08 pm, Mike Diehl wrote: > > Hi all. > > > > Twice this week, I've come to work to find my Postgres server out of > > connections... effectively freezing my web server. > > > > > > Can anyone tell me how to fix this? The out put of the ps command can be > > seen at http://dominion.dyndns.org/~mdiehl/ps.txt > > > > Thanx in advance, > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] working around setQuerySnapshot limitations in functions
I have a function called move_tree() which moves a node from 1 parent to another (in a nested set tree) by computing some necessary values, then updating the nodes as needed. The problem I have is that when the function is called multiple times simultaneously, each function does a setQuerySnapshot at the start of the function, and then cannot see the changes made by the other function calls. I had thought to do a lock on the table at the start of my function, but due to the fact setQuerySnapshot is called at the function start (before I can acquire a lock) I can't guarantee no changes have been made before the function tries to make changes. ISTM the only way around this is to do a lock on the table before calling the function. The major downside to that it requires me to put that logic inside any apps that use the function, rather than being able to keep it inside the database. Does anyone see another work-around? Robert Treat ---(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] unsubscribe
If you just want to take a break, your better off sending "set ALL nomail-14d" to majordomo. Robert Treat On Thu, 2002-12-19 at 07:39, Christoph Haller wrote: > > David and all others on the list, > who want to turn off mailing over xmas and new year, > please send your unsubscribe to > > [EMAIL PROTECTED] > > Otherwise it will not be processed. > > Regards, Christoph > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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
Re: [SQL] Sorry, to many clients already
On Mon, 2003-01-06 at 09:12, cristi wrote: > When I'm trying to connect I have this error message: > > Something unusual has occured to cause the driver to fail.Please report this > exception: java.sql.SQLException: Sorry, to many clients already. > > > What should I do? > I might suggest posting to the -jdbc list in case this is something different, but generally speaking the "too many clients" error means that your application is attempting to connect to the db and the db is responding that it already has created the maximum number of connections that are allowed. This might be an indication of other problems, but if you simply want to raise the limit you'll need to modify max_connections in the postgresql.conf Robert Treat ---(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] Postgresql Bug List?
One could subscribe to pgsql-bugs if you wanted to look into any new bugs that come down the pipe. Robert Treat On Wed, 2003-01-08 at 23:30, Bruce Momjian wrote: > > No bugzilla, but do have a TODO list. See the developers FAQ. > > --- > > Wei Weng wrote: > > Is there a bugzilla kind of thing for Postgresql? > > > > I would like to help out on the development, but have no idea where to > > start... > > > > Thanks > > > > > > Wei > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL function parse error ?
On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote: > What i'm saying is that i know that some of my colleagues, nice guys for > that matter, and good programmers, will come screaming > to me "what's with the b.s. error ?!?", and when i'll tell them that the sql > parser belives that's an inexisting operator, they'll start > cursing at it, just like i did. > Does oracle or mysql or whichever db you like allow the use of $ in user defined operators? If so, how do they know the difference? For what it's worth, some policy should be enforced, because it shouldn't > matter how many spaces you put between the operator > and the operand, as writing SELECT * is the same as SELECT > *. > I rest my case. > Thats an invalid comparison. The problem is not that foo > $1 doesn't work, as your example put forth. The problem is that foo>$1 doesn't work, which by comparison would be SELECT* which would also not work. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL function parse error ?
On Fri, 2003-01-10 at 04:13, Radu-Adrian Popescu wrote: > > Robert, my dear fellow... > > How about checking your facts before contradicting anyone ? Shame on you ! > Have you actually tried to do a SELECT* from foo ? Pathetic ! At least you started out all nice and flowery... > Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy > everyone on this list a chase of Crystal. > Robert, even > select*from errors; > works on all three of them ! (Sorry about the colors, pasted from TOAD). > > There you go. Now go sit in the corner ! :) Would an acceptable defense be that select*fromfoo doesn't work? Bah, off to the corner I go... > Also, as I tried to make it quite clear, the point is not whether Oracle or > mysql allow the > use of $ in operators, but whether you have to write cumbersome syntax to > get things working. And the > answer is definitely _no_. But the point I was trying to make is that maybe the reason the answer is no is that they don't support $ in operators. You'll note that a clause like mytime My point here is that common sense (and the use of $1, $2,... with operator > > is going > come up a lot, opposed to the user-defined operator >$, which takes > precedence when parsing a special > case of the SQL command) should prevail over backwards compat. Loot at C++ > for instance, the standard > broke a lot of C++ apps that were written poorly. > Would you suggest the parser should assume >$1 equals > $1? That seems likely to break a lot of cases where >$ was being used. OTOH, if your saying that support of >$1 is more important than support of >$ 1 that's a different argument. That's what you are saying (afaict) and that's also the path that the developers have taken in trying to resolve the issue. > It seems that - strangely - instead of trying to acknowledge not necessarily > incorrect but awkward behavior, > some people on this list have tried to put me down. I think people were trying to explain to you the reasons for the current behavior, at least that's what I was trying to attempt to do. > What's even more scary is receiving answers like "SQL queries are like bash > commands", > or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was > simply pointing out that > i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from > ... is invalid SQL. > yeah, my bad on that one. but your argument was still a non starter because we aren't debating support for > $1 (like in your example), but support for >$1. Actually you should be thanking in me, since while my supposition was wrong, my example helps bolster your case somewhat. :-) > The good news is some people seem to have gotten the point and are doing > something about it - and this > makes me feel like maybe, maybe I've helped the community just a little bit. > After all, we all want to see > postgresql up there where it belongs. > > That being said, I do hope that superficial replies trying to prove me wrong > will stop, as they actually don't help > anyone. > Maybe I need to re-read some of the other posts, but I think your taking this too personally. My email was simply trying to help frame the issue properly, because I saw you making an invalid argument in your own defense. Furthermore you need to realize that when someone makes a claim that a certain feature needs to work in a different fashion, or needs to be added at the expense of another feature, that it is only natural and a good thing that the proposal be given a little scrutiny to make sure it stands up. At this point yours does so in my book, though I still would like to see answered is whether oracle or others support >$ as an operator, or if the sql spec has anything to say on the matter. Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] postmaster -i & establishes three connections why?
Hi, > > I've just upgraded my cygwin to 1.3.20-1 and postgresql to 7.3.2 > > And I found that when run > > postmaster -i & > > There are three connections being established and why is that? > > Administrator@DemoServer ~ > $ LOG: database system was shut down at 2003-02-13 17:33:26 EST > LOG: checkpoint record is at 0/841330 > LOG: redo record is at 0/841330; undo record is at 0/0; shutdown TRUE > LOG: next transaction id: 484; next oid: 16976 > LOG: database system is ready > $ ps -l > PIDPPIDPGID WINPID TTY UIDSTIME COMMAND > 2292 12292 2292 con 500 17:43:20 /usr/bin/bash > 232022922292 2324 con 500 17:43:22 > /usr/local/bin/ipc-daemo > n > 209222922292 2404 con 500 17:43:22 > /c/progra~1/apache~1/apa > che/cgi-bin/printspool > 231222922292 2452 con 500 17:43:24 > /usr/bin/postgres > 204823122292 2048 con 500 17:43:28 > /usr/bin/postgres > 188420482292 1884 con 500 17:43:28 > /usr/bin/postgres > 250422922504 1960 con 500 17:43:36 /usr/bin > Are you sure those are connections. On server start you should get three process going, the main postmaster, the stats collector, and the stats buffer Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to increase Column width of table
http://fts.postgresql.org/db/mw/msg.html?mid=1071582 On Tue, 2003-03-25 at 10:18, Christoph Haller wrote: > > > > Atul here, i have one table and i would like to increase the length > of > > existing column and the sql statement is > > > >Exisiting Column is "vehicle_make" varchar(30) > > > >SQL: alter table commute_profile alter column "vehicle_make" > > varchar(100) > > > >But this gives error. > > > >ERROR: parser: parse error at or near "varchar" > > > I'm afraid there is no one-step-solution to this. > You'll have to go the hard way as > > CREATE new_table ( > ... > ... varchar(100), > ... > ); > INSERT INTO new_table as SELECT * FROM old_table; > DROP old_table; > ALTER TABLE new_table RENAME TO old_table; > And don't forget about re-creating related indexes etc. > > Regards, Christoph ---(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] [ADMIN] Perl Book
On Tue, 2003-05-27 at 10:30, Jodi Kanter wrote: > I also need a Perl programming book to use as a reference but also as a > means to learning the product. I am a DBA but fairly new to coding in > Perl. > Does anyone have a recommendation? > Thanks > Jodi > You might want to give "PostgreSQL" by Korry Douglas (Sam's Publishing) a spin, as it has information regarding the internals of postgresql, database administration, and several chapters on different interfaces to postgresql (inlcuding about 50 pages on perl and dbi). Also, not sure how relevant it is, but oreilly has a number of bio-informatics books, several of which deal specifically with perl. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] control structures in plpgsql
Does anyone know if there is support for "IF x OR y THEN" syntax in
plpgsql? The docs just say IF [boolean expression] then. which loosely
interpreted could allow for an OR, but I couldn't seem to get it to
work. TIA,
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] control structures in plpgsql
On Thu, 2003-06-12 at 10:24, Stephan Szabo wrote:
>
> On 12 Jun 2003, Robert Treat wrote:
>
> > Does anyone know if there is support for "IF x OR y THEN" syntax in
> > plpgsql? The docs just say IF [boolean expression] then. which loosely
> > interpreted could allow for an OR, but I couldn't seem to get it to
> > work. TIA,
>
> Can you give a full example of what you're trying? I'm not seeming to
> have a problem in 7.3 of saying something with an OR in it apart from
> needing to put parentheses around things for ordering in more complicated
> cases.
>
You beat me to my "please disregard the previous email" email ;-)
It works (in 7.2 btw), I was just programming in the wrong language
inside my plpgsql function.
Thanks for taking a look though.
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] help with "delete joins"
create table foo (a int, b int, c int, d text); create table bar (a int, b int, c int); insert into foo values (1,2,3,'a'); insert into foo values (1,2,4,'A'); insert into foo values (4,5,6,'b'); insert into foo values (7,8,9,'c'); insert into foo values (10,11,12,'d'); insert into bar values (1,2,3); insert into bar values (7,8,9); insert into bar values (10,11,12); what i want to do is: delete * from foo where not (foo.a = bar.a and foo.b=bar.b and foo.c=bar.c) ; so i end up with postgres=# select * from foo; a | b | c | d ---+---+---+--- 1 | 2 | 4 | A 4 | 5 | 6 | b (2 rows) but thats not valid sql, is there some way to accomplish this? Robert Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] help with "delete joins"
On Mon, 2003-06-30 at 18:26, Robert Treat wrote:
> create table foo (a int, b int, c int, d text);
>
> create table bar (a int, b int, c int);
>
> insert into foo values (1,2,3,'a');
> insert into foo values (1,2,4,'A');
> insert into foo values (4,5,6,'b');
> insert into foo values (7,8,9,'c');
> insert into foo values (10,11,12,'d');
>
> insert into bar values (1,2,3);
> insert into bar values (7,8,9);
> insert into bar values (10,11,12);
>
> what i want to do is:
>
> delete * from foo where not (foo.a = bar.a and foo.b=bar.b and
> foo.c=bar.c) ;
>
> so i end up with
>
> postgres=# select * from foo;
> a | b | c | d
> ---+---+---+---
> 1 | 2 | 4 | A
> 4 | 5 | 6 | b
> (2 rows)
>
> but thats not valid sql, is there some way to accomplish this?
>
ok, i have a solution from the other end:
create table baz as select * from foo except (select foo.* from foo,bar
where foo.a = bar.a and foo.b=bar.b and foo.c=bar.c);
but i'd still be interested in a delete based method :-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] help with "delete joins"
On Mon, 2003-06-30 at 20:35, Josh Berkus wrote:
> Robert,
>
> > delete * from foo where not (foo.a = bar.a and foo.b=bar.b and
> > foo.c=bar.c) ;
> >
> > so i end up with
> >
> > postgres=# select * from foo;
> > a | b | c | d
> > ---+---+---+---
> > 1 | 2 | 4 | A
> > 4 | 5 | 6 | b
> > (2 rows)
> >
> > but thats not valid sql, is there some way to accomplish this?
>
> Um, your example result doesn't match your pseudo-query.
the end of a long day that started with 4 hours of sleep... no wonder I
couldn't get my head around this one. I actually did want the results of
the psuedo query, not the results I posted :-\
> Assuming that you
> want to delete everything that DOES match, not everything that DOESN'T, do:
>
> DELETE FROM foo
> WHERE EXISTS ( SELECT bar.a FROM bar
> WHERE bar.a = foo.a AND bar.b = foo.b
> AND bar.c = foo.c );
I was almost there with my original query... a NOT on your/stephan's
query gets me what I really want. :-) Thanks guys.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Immutable attributes?
On Tue, 2003-07-01 at 05:59, Troels Arvin wrote:
> Hello,
>
> I have a table like this:
>
> create table test (
> "test_id" serial primary key,
> "created" timestamp with time zone
> default current_timestamp
> check(created = current_timestamp),
> "some_datum" int not null
> );
>
> My question concerns the "created" attribute: I want this to reflect when
> the tuple was craeated; and I want to make sure that the timestamp is not
> wrong. That will work with the above schema. However, I also want to make
> sure that the "crated" attribut for a tuple is not changed once it has
> been set.
>
> I'm thinking about implementing it through a trigger, but is there a
> better way to create such "immutable" attributes?
>
I don't know if it's "better", but this is one of the things people find
the RULE system really handy for. Check the docs, I believe there are
examples of this.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] obtuse plpgsql function needs
given
create table t1 (f,f1,f2,f3);
create table t2 (f,f4,f5,f6);
i'm trying to create a function concat() that does something like:
select f,concat() as info from t1;
which returns a result set equivalent to:
select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
or
select f,concat() as info from t2;
returns equivalent
select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
I'm starting to believe this is not possible, has anyone already done
it? :-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] obtuse plpgsql function needs
On Tue, 2003-07-22 at 19:33, elein wrote:
> You'll need to pass the values down to your
> concat function (which I suggest you don't call concat)
> and have it return a text type.
>
> What exactly is your problem? I must be missing something.
>
The problem is that I need the function to be generic so that I don't
have to pass the values down to the function, it just grabs the values
automagically based on the table it's being called against.
Robert Treat
> elein
>
> On Tue, Jul 22, 2003 at 06:31:52PM -0400, Robert Treat wrote:
> > given
> >
> > create table t1 (f,f1,f2,f3);
> > create table t2 (f,f4,f5,f6);
> >
> > i'm trying to create a function concat() that does something like:
> >
> > select f,concat() as info from t1;
> >
> > which returns a result set equivalent to:
> > select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
> >
> > or
> > select f,concat() as info from t2;
> > returns equivalent
> >
> > select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
> >
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] obtuse plpgsql function needs
On Wed, 2003-07-23 at 09:06, Robert Treat wrote:
> On Tue, 2003-07-22 at 19:33, elein wrote:
> > You'll need to pass the values down to your
> > concat function (which I suggest you don't call concat)
> > and have it return a text type.
> >
> > What exactly is your problem? I must be missing something.
> >
>
> The problem is that I need the function to be generic so that I don't
> have to pass the values down to the function, it just grabs the values
> automagically based on the table it's being called against.
>
> Robert Treat
>
Hmm... I neglected to mention that I was trying to find a solution that
didn't use ctid or oid. If I abandon that path I think it will be
straightforward...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] time delay function
On Tue, 2003-07-22 at 04:22, Christoph Haller wrote:
> >
> > Pseudo code:
> >
> > begin trans
> > select * from table1
> > WAIT FOR 20 SECS
> > update table1 set blah = 'blah'
> > end transcation
> >
> > In pgplsql, Im looking for something like a function that I can use to
> make the process to wait for 20 secs before con
> tinuing to execute the next sql statment?
> >
> AFAIK there is no such thing.
> But probably you can write a C function,
> which waits for N seconds and which can
> be called by your plpgsql function.
> Regards, Christoph
>
seems like this would work:
21343=# create or replace function wait(integer) returns bool as '
21343'# declare
21343'# ahora timestamptz;
21343'# delay alias for $1;
21343'# go timestamptz;
21343'# begin
21343'# select now() + (delay || ''seconds'')::interval into go;
21343'#
21343'# loop
21343'# select timeofday() into ahora;
21343'# exit when ahora > go;
21343'# end loop;
21343'#
21343'# return true;
21343'# end;
21343'# ' language 'plpgsql';
CREATE
21343=#
21343=# select now(); select wait(5); select now();
now
---
2003-07-23 15:45:46.754845-04
(1 row)
wait
--
t
(1 row)
now
---
2003-07-23 15:45:51.758621-04
(1 row)
21343=#
21343=# select now(); select wait(10); select now();
now
---
2003-07-23 15:45:58.713646-04
(1 row)
wait
--
t
(1 row)
now
--
2003-07-23 15:46:08.71887-04
(1 row)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] obtuse plpgsql function needs
Questions for the group:
1) any way to do this without the ctid/oid? Sounds like I could do
select a,b,msgmaker(*) from t1 where a=b; in pltcl (which was an early
inclination I abandoned, perhaps prematurely)
2) would it be faster in pltcl? seems like it would if i didn't have to
do the catalog lookups, but is pltcl inherently faster anyways?
thanks for the input so far.
Robert Treat
On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> SELECT a,b,c,msgmaker('t1',ctid) FROM t1 WHERE a=b;
>
>
> CREATE OR REPLACE FUNCTION msgmaker(text,tid) RETURNS text AS '
>
> DECLARE
>
> mytable ALIAS FOR $1;
> mytid ALIAS FOR $2;
> myctid TEXT;
>
> myquery TEXT;
> mylen SMALLINT := 20;
> yourlen SMALLINT;
> mydec SMALLINT;
> myinfo TEXT;
> myrec RECORD;
> biglist TEXT := \'Error\';
>
> BEGIN
>
> myquery := \'
> SELECT length(attname) AS lenny FROM pg_attribute
> WHERE attnum >=1
> AND attrelid = (SELECT oid FROM pg_class WHERE relname = \'\'\' || mytable ||
> \'\'\')
> ORDER BY 1 DESC LIMIT 1\';
>
> FOR myrec IN EXECUTE myquery LOOP
> mylen := myrec.lenny;
> END LOOP;
>
> myquery := \'
> SELECT attname, atttypid, atttypmod FROM pg_attribute
> WHERE attnum >=1
> AND attrelid = (SELECT oid FROM pg_class WHERE relname = \'\'\' || mytable ||
> \'\'\')
> ORDER BY attname ASC\';
>
> myinfo := \'SELECT \';
>
> FOR myrec IN EXECUTE myquery LOOP
> myinfo := myinfo || \'\'\'- \' || myrec.attname || \': \';
> yourlen := LENGTH(myrec.attname);
> LOOP
> myinfo := myinfo || \' \';
> yourlen := yourlen + 1;
> EXIT WHEN yourlen > mylen;
> END LOOP;
> myinfo := myinfo || \'\'\' || COALESCE(\';
> IF myrec.atttypid = 1184 THEN
> myinfo := myinfo || \'TO_CHAR(\' || myrec.attname || \',\'\'Mon DD,
> HH24:MI\'\')\';
> ELSIF myrec.atttypid = 16 THEN
> myinfo := myinfo || \'CASE WHEN \' || myrec.attname || \' IS TRUE THEN
> \'\'True\'\' ELSE \'\'False\'\' END\';
> ELSIF myrec.atttypid = 17 THEN
> myinfo := myinfo || \'ENCODE(\' || myrec.attname || \',\'\'hex\'\')\';
> ELSIF myrec.atttypid = 1700 THEN
> SELECT substr(rtrim(format_type(myrec.atttypid, myrec.atttypmod),\')\'),
> position(\',\' IN format_type(myrec.atttypid, myrec.atttypmod))+1) INTO mydec;
> myinfo := myinfo || \'TO_CHAR(\' || myrec.attname || \',\'\'FM990\';
> IF mydec > 1 THEN
> myinfo := myinfo || \'.\';
> LOOP
> myinfo := myinfo || \'0\';
> mydec := mydec - 1;
> EXIT WHEN mydec < 1;
> END LOOP;
> END IF;
> myinfo := myinfo || \'\'\')\';
> ELSE
> myinfo := myinfo || myrec.attname;
> END IF;
> myinfo := myinfo || \'::text,\'\'\'\'::text) || \'\'n\'\' || \\n\';
> END LOOP;
>
> SELECT mytid INTO myctid;
>
> myinfo := myinfo || \'\'\'n\'\' AS info FROM \' || mytable || \' WHERE ctid =
> \'\'\' || myctid || \'\'\'\';
>
> FOR myrec IN EXECUTE myinfo LOOP
> biglist := myrec.info;
> END LOOP;
>
> RETURN biglist;
>
> END;
>
> ' LANGUAGE 'plpgsql';
>
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> FOR myrec IN EXECUTE myinfo LOOP
> biglist := myrec.info;
> END LOOP;
>
One other thing, I hate when I have to do things like the above, can we
get a TODO like:
allow 'EXECUTE var INTO record' in plpgsql
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
On Wednesday 23 July 2003 19:06, Bruce Momjian wrote:
> Robert Treat wrote:
> > On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> > > FOR myrec IN EXECUTE myinfo LOOP
> > > biglist := myrec.info;
> > > END LOOP;
> >
> > One other thing, I hate when I have to do things like the above, can we
> > get a TODO like:
> >
> > allow 'EXECUTE var INTO record' in plpgsql
>
> So the TODO would be?
>
> Allow PL/pgSQL EXECUTE to return a single record outside a loop
that's what I wrote, but not what I meant :-) I do like the sound of it
though, but really what I meant to say was:
EXECUTE var1 INTO var2
but this assumes a number of things, namely that executing var1 will return
only one field, and one row. I guess that would be:
Allow PL/pgSQL EXECUTE to return a single variable outside a loop
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
I don't seem to have any plsql specfic documentation, and the rest of my
oracle documentation isn't specfific enough. Anyone else?
Robert Treat
On Thursday 31 July 2003 00:12, Bruce Momjian wrote:
> Does Oracle have a syntax for this?
>
> ---
>
> Robert Treat wrote:
> > On Wednesday 23 July 2003 19:06, Bruce Momjian wrote:
> > > Robert Treat wrote:
> > > > On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> > > > > FOR myrec IN EXECUTE myinfo LOOP
> > > > > biglist := myrec.info;
> > > > > END LOOP;
> > > >
> > > > One other thing, I hate when I have to do things like the above, can
> > > > we get a TODO like:
> > > >
> > > > allow 'EXECUTE var INTO record' in plpgsql
> > >
> > > So the TODO would be?
> > >
> > > Allow PL/pgSQL EXECUTE to return a single record outside a loop
> >
> > that's what I wrote, but not what I meant :-) I do like the sound of it
> > though, but really what I meant to say was:
> > EXECUTE var1 INTO var2
> > but this assumes a number of things, namely that executing var1 will
> > return only one field, and one row. I guess that would be:
> > Allow PL/pgSQL EXECUTE to return a single variable outside a loop
> >
> > Robert Treat
> > --
> > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faqs/FAQ.html
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] Postgresql slow on XEON 2.4ghz/1gb ram
If you went from a dual processor box running windows to a single
processor box running windows, I wouldn't be surprised to see a slow
down. I'd recommend switching from Windows to Linux/BSD over a hardware
upgrade any day.
Robert Treat
On Wed, 2003-08-06 at 18:04, Maksim Likharev wrote:
> What OS, if Linux what kernel
>
> -Original Message-
> From: Wilson A. Galafassi Jr. [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 06, 2003 6:51 AM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] Postgresql slow on XEON 2.4ghz/1gb ram
>
>
>
> Hello.
> I have this problem: i'm running the postgre 7.3 on a windows 2000
> server with P3 1GHZ DUAL/1gb ram with good performance. For best
> performance i have change the server for a XEON 2.4/1gb ram and for my
> suprise the performance decrease 80%. anybody have a similar experience?
> does exist any special configuration to postgre running on a Xeon
> processor? Any have any idea to help-me? Excuse-me my bad english.
> Very Thanks
> Wilson
> icq 77032308
> msn [EMAIL PROTECTED]
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] createlang plpgsql failing on redhatlinux7.2
rather do an rpm -qa | grep post to see if postgresql-pl-7.3.4-2PGDG is
installed, it is the rpm for procedural languages in 7.3.* and didn't
exist in the 7.2.* rpmset.
Robert Treat
On Fri, 2003-09-12 at 10:12, Richard Huxton wrote:
> Please don't post html-only messages to the list, it makes quoting hard...
>
> On Friday 12 September 2003 14:49, vijaykumar M wrote:
> > We have a machine with RedhatLinix 7.2, on top of this i upgraded
> > the postgresql7.3.3 version. After creating a database, i'm trying to
> > create a language for that database by using createlang plpgsql
> > <dnname> it throws an error " failed with some
> > missing files".
>
> Hmm - we need to find out why the files are missing. First thing to try is
> "locate plpgsql.so" and see if that can find your library. If so, make sure
> you have its location added to /etc/ld.so.conf and re-run "ldconfig".
>
> If the file genuinely doesn't exist, did you install from RPM or source?
> If RPM, try "rpm -ql postgresql-server" to see what files it thinks it
> installed.
> If source, can you remember what options you chose to compile with, or do you
> have the source-directories still around? If you still have the source,
> there'll be a file called configuration.cache or similar that should hold
> your config options.
>
> > so, i copied /usr/local/pgsql/plpgsql.so
> > from Redhatlinux8.0 machine and tried again to create a language.
> > this time i got an error like 'Load of file
> > /usr/lib/pgsql/plpgsql.so failed :/lib/i686/libc.so.6: version GLIB_2.3 not
> > found (required by /usr/lib/pgsql/plpgsql.so).
>
> RedHat changed their version of glibc between 7.2 and 8.0 - you can't just
> move libraries between these versions.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---(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
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] createlang plpgsql failing on redhatlinux7.2
On Friday 12 September 2003 12:18, Richard Huxton wrote:
> On Friday 12 September 2003 16:49, Robert Treat wrote:
> > rather do an rpm -qa | grep post to see if postgresql-pl-7.3.4-2PGDG is
> > installed, it is the rpm for procedural languages in 7.3.* and didn't
> > exist in the 7.2.* rpmset.
>
> Ah, but the .so isn't in the -pl RPM (I'm guessing plpgsql is considered
> "core").
>
> $ rpm -qif /usr/lib/pgsql/plpgsql.so
> Name: postgresql-serverRelocations: (not relocateable)
> Version : 7.3.4 Vendor: (none)
> ...
Right you are Richard, my apologies. I'll go crawl back under my rock now :-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] off subject - pg web hosting
http://www.iniquinet.com When grilled further on (Thu, 6 Nov 2003 16:39:14 -0800 (PST)), chester c young <[EMAIL PROTECTED]> confessed: > can anybody recomend web hosting that provides postgresql? I have > found a couple, but their pricing is several times the going rate using mySql. > -- 15:58:42 up 99 days, 9:18, 4 users, load average: 2.00, 2.00, 2.00 pgp0.pgp Description: PGP signature
Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Generally speaking you can send articles to me or to [EMAIL PROTECTED]
for inclusion on the techdocs site.
I'll try to update the links you mentioned below as well. thanks.
Robert Treat
On Thursday 04 December 2003 12:52, Clint Stotesbery wrote:
> Hi Christoph,
> Thanks for the links but the techdoc links for converting from Oracle to
> PostgreSQL has 2 links that don't go to their intended targets anymore, one
> is in the 7.3 docs which is really limited (only covers simple things), and
> the Ora2Pg one I don't really get that well.
>
> As far as updateable views, that's why you need instead of triggers.
> Regular triggers can't be done on views. So if I make an instead of trigger
> on a view that's for updates then I have an updateable view. I figured it
> out last night and I was along the right track in my original post with
> using an instead of rule to call a function. The trick is that I have to
> pass in all the old.col and new.col stuff into the function that I call
> from the rule. In Oracle since the instead of stuff is a trigger I had
> access to the new.col and old.col stuff. To do it in PostgreSQL rules I had
> to pass it all in. I'm going to write a doc about the problems I've
> encountered during my conversion project and then submit it to the Postgres
> people I think (to who though?). My programming and tech writing background
> should help I hope. Thanks for the suggestions Christoph.
> -Clint
>
>
> Original Message Follows
> From: Christoph Haller <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> CC: [EMAIL PROTECTED]
> Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
> Date: Thu, 04 Dec 2003 17:16:32 MET
>
> Not sure if this is of any help ...
> AFAIK there are no updatable views in pg.
> But aside from that, I cannot see nothing what could not be
> done by a pg trigger function:
> CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
> ON table FOR EACH { ROW | STATEMENT }
> EXECUTE PROCEDURE func ( arguments )
>
> Also try
>
> http://techdocs.postgresql.org/#convertfrom
>
> Converting from other Databases to PostgreSQL
>
> and/or
>
> http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search
>
> HTH
>
> Regards, Christoph
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
> _
> Get holiday tips for festive fun.
> http://special.msn.com/network/happyholidays.armx
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Just to follow up I managed to track down these missing articles and have
updated the links on the website.
Robert Treat
On Thursday 04 December 2003 12:52, Clint Stotesbery wrote:
> Hi Christoph,
> Thanks for the links but the techdoc links for converting from Oracle to
> PostgreSQL has 2 links that don't go to their intended targets anymore, one
> is in the 7.3 docs which is really limited (only covers simple things), and
> the Ora2Pg one I don't really get that well.
>
> As far as updateable views, that's why you need instead of triggers.
> Regular triggers can't be done on views. So if I make an instead of trigger
> on a view that's for updates then I have an updateable view. I figured it
> out last night and I was along the right track in my original post with
> using an instead of rule to call a function. The trick is that I have to
> pass in all the old.col and new.col stuff into the function that I call
> from the rule. In Oracle since the instead of stuff is a trigger I had
> access to the new.col and old.col stuff. To do it in PostgreSQL rules I had
> to pass it all in. I'm going to write a doc about the problems I've
> encountered during my conversion project and then submit it to the Postgres
> people I think (to who though?). My programming and tech writing background
> should help I hope. Thanks for the suggestions Christoph.
> -Clint
>
>
> Original Message Follows
> From: Christoph Haller <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> CC: [EMAIL PROTECTED]
> Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
> Date: Thu, 04 Dec 2003 17:16:32 MET
>
> Not sure if this is of any help ...
> AFAIK there are no updatable views in pg.
> But aside from that, I cannot see nothing what could not be
> done by a pg trigger function:
> CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
> ON table FOR EACH { ROW | STATEMENT }
> EXECUTE PROCEDURE func ( arguments )
>
> Also try
>
> http://techdocs.postgresql.org/#convertfrom
>
> Converting from other Databases to PostgreSQL
>
> and/or
>
> http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search
>
> HTH
>
> Regards, Christoph
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
> _
> Get holiday tips for festive fun.
> http://special.msn.com/network/happyholidays.armx
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] grouping by date
When grilled further on (Mon, 05 Jan 2004 17:14:26 +), teknokrat <[EMAIL PROTECTED]> confessed: > How can I group by date given a timestamp column? > I just found this out this weekend. Try 'date_trunc'. Look at secion 9.8.2 of the documentation. I'm using something like: SELECT date_trunc( 'hour', "when" ) AS "date" FROM readings GROUP BY "date"; Where "when" is my timestamp columnn. Cheers, Rob -- 21:55:40 up 11 days, 11:46, 4 users, load average: 2.09, 2.03, 2.01 pgp0.pgp Description: PGP signature
[SQL] Problem with NOT IN portion of query.
Hey All, Probably doing something stupid, and I'm too tired to see what. The query I'm trying to execute is: SELECT date_trunc( 'hour', "when" )::timestamp AS period FROM readings WHERE period NOT IN (SELECT "time" FROM hour.summary_period) GROUP BY period ORDER BY period; Where the table definitions are: CREATE TABLE readings ( "when" TIMESTAMP DEFAULT now() NOT NULL PRIMARY KEY ); CREATE SCHEMA hour; CREATE TABLE hour.summary_period ( "time" TIMESTAMP NOT NULL ); The error is: ERROR: column "period" does not exist When I remove the NOT IN (and associated WHERE), the query works fine. Any help? Cheers, Rob -- 20:55:35 up 14 days, 10:45, 4 users, load average: 2.01, 2.04, 2.05 pgp0.pgp Description: PGP signature
Re: [SQL] Problem with NOT IN portion of query.
When grilled further on (Mon, 12 Jan 2004 07:28:09 +0100), Tomasz Myrta <[EMAIL PROTECTED]> confessed: > Dnia 2004-01-12 05:04, U¿ytkownik Robert Creager napisa³: > > > > SELECT date_trunc( 'hour', "when" )::timestamp AS > > period FROM readings WHERE period NOT IN (SELECT "time" FROM > > hour.summary_period) GROUP BY period ORDER BY period; > > > > The error is: > > > > ERROR: column "period" does not exist > > > Your problem has nothing to "NOT IN". > Your query works fine, when you remove column alias from WHERE clause - > it's beacause WHERE clause is executed *before* data output (and before > column aliases). You can still use column aliases in "GROUP BY" and > "ORDER BY". Thanks for the info. So now I have: SELECT p.period FROM (SELECT date_trunc( 'hour', "when" )::timestamp AS period FROM readings GROUP BY period) AS p WHERE p.period NOT IN (SELECT "time" FROM hour.summary_period) ORDER BY p.period; which appears works as expected. Anything obviously goofy with the above query? Cheers, Rob -- 05:57:10 up 14 days, 19:47, 4 users, load average: 2.17, 2.07, 2.04 pgp0.pgp Description: PGP signature
[SQL] How can I get the last element out of GROUP BY sets?
I'm trying to produce summary data from a table (using PGSQL 7.4.1): CREATE TABLE readings( "when" timestamp, value integer ); The summary will be based on various time periods. I've been using date_trunc( 'hour', "when" ) and GROUP BY for the min/max/average readings with no problems. But, one piece of data I need is the last value for each GROUP BY period. Alas, I cannot figure out how to do this. If I wanted to loop from a script, I could, for instance, execute the following for each GROUP BY period (filling in ? appropriately): SELECT date_trunc( 'hour', "when" ), value FROM readings WHERE date_trunc( 'hour', "when" )::timestamp = ? ORDER BY "when" DESC LIMIT 1 But, I figure there's probably some what to do this in SQL. Any help? Thanks, Rob -- 21:12:24 up 21 days, 11:00, 4 users, load average: 2.23, 1.69, 1.28 pgp0.pgp Description: PGP signature
Re: [SQL] How can I get the last element out of GROUP BY sets?
When grilled further on (Mon, 19 Jan 2004 00:44:30 -0500), Tom Lane <[EMAIL PROTECTED]> confessed: > Robert Creager <[EMAIL PROTECTED]> writes: > > ... one piece of data I need is the last value for each GROUP BY > > period. Alas, I cannot figure out how to do this. > > SELECT DISTINCT ON (rather than GROUP BY) could get this done for you. > I had my whine all ready as to how I still couldn't figure it out, when I figured it out: ... SELECT p.period, etday FROM (SELECT DISTINCT ON ( period ) date_trunc( 'hour', "when" ) AS period, etday FROM readings ORDER BY period, "when" DESC) AS p ... Thanks for the tip. Cheers, Rob -- 08:10:55 up 21 days, 21:58, 4 users, load average: 2.15, 2.06, 2.02 pgp0.pgp Description: PGP signature
Re: [SQL] Implementation of a bag pattern using rules
When grilled further on (Mon, 09 Feb 2004 13:49:17 +), Mark Gibson <[EMAIL PROTECTED]> confessed: > I probably didn't make this clear enough: > Nah. After re-reading your e-mail, I say what I missed the first time. 'Bout 1 hour before my normal thinking time... Cheers, Rob -- 20:20:54 up 1 day, 7:11, 3 users, load average: 2.32, 2.18, 2.17 pgp0.pgp Description: PGP signature
Re: [SQL] max timestamp
When grilled further on (10 Feb 2004 10:14:04 -0800), [EMAIL PROTECTED] (Michael Sterling) confessed: > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. > Well, one gross and ugly way is: SELECT MAX( "when" ) FROM readings, (SELECT DATE_TRUNC( 'day', "when" ) AS period FROM readings GROUP BY period) AS p WHERE DATE_TRUNC( 'day', "when" ) = p.period GROUP BY p.period; given the table readings looks something like: CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY ); I'm curious to see other, 'better' solutions. Cheers, Rob -- 12:52:57 up 20:36, 2 users, load average: 2.08, 2.17, 2.18 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
Re: [SQL] umlimited arguments on function
On Monday 09 February 2004 14:49, Christian Hergert wrote:
> Is it possible to write a function (in sql or plpgsql) that allows for
> an unknown amount of arguments? Something similar to C's printf()
> function.
>
AFAIK no, but I know of two possible work arounds... first is to write
multiple copies of the function to accept the different number of argument
combinations you need. the other thing i've done in the past is to create
the function as accepting a array as one of its inputs and then handle the
array structure as needed. (bonus points for doing the second method in 7.3
where its much more challenging.) HTH,
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Getting the week of a date
When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530), "Kumar" <[EMAIL PROTECTED]> confessed: > Dear Friends, > > Postgres 7.3.4 on RH Linux7.2. > > While this works for month and why not for week > date_trunc (obviously) doesn't support week. I ran into this a while ago, and came up with this function. I left the function signature the same as date_trunc, even though I don't use the first argument. I did only minor testing (10 years or so), so no guarantee about it's correctness. And it's kind of slow... CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp ) RETURNS timestamp AS ' DECLARE reading_time ALIAS FOR $2; year timestamp; dow integer; adjust text; week text; BEGIN year := date_trunc( ''year''::text, reading_time ); week := date_part( ''week'', reading_time ) - 1 || '' week''; dow := date_part( ''dow'', year ); -- If the dow is less than Thursday, then the start week is last year IF dow <= 4 THEN adjust := 1 - dow || '' day''; ELSE adjust := 8 - dow || '' day''; END IF; RETURN year + adjust::interval + week::interval; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; -- 05:37:49 up 1 day, 13:20, 2 users, load average: 0.09, 0.36, 0.63 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
Re: [HACKERS] [SQL] Materialized View Summary
On Tue, 2004-02-24 at 12:11, Richard Huxton wrote:
> On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:
> >
> > I've written a summary of my findings on implementing and using
> > materialized views in PostgreSQL. I've already deployed eagerly updating
> > materialized views on several views in a production environment for a
> > company called RedWeek: http://redweek.com/. As a result, some queries
> > that were taking longer than 30 seconds to run now run in a fraction of a
> > millisecond.
> >
> > You can view my summary at
> > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
have you done much concurrency testing on your snapshot views? I
implemented a similar scheme in one of my databases but found problems
when I had concurrent "refresh attempts". I ended up serializing the
calls view LOCKing, which was ok for my needs, but I thought potentially
problematic in other cases.
>
> Interesting (and well written) summary. Even if not a "built in" feature, I'm
> sure that plenty of people will find this useful. Make sure it gets linked to
> from techdocs.
Done. :-)
>
> If you could identify candidate keys on a view, you could conceivably automate
> the process even more. That's got to be possible in some cases, but I'm not
> sure how difficult it is to do in all cases.
>
it seems somewhere between Joe Conways work work arrays and polymorphic
functions in 7.4 this should be feasible.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] [SQL] Materialized View Summary
On Wed, 2004-02-25 at 03:19, Jonathan M. Gardner wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I'm not sure if my original reply made it through. Ignore the last one if > it did. But I liked the last one :-) > > On Tuesday 24 February 2004 1:48 pm, Robert Treat wrote: > > On Tue, 2004-02-24 at 12:11, Richard Huxton wrote: > > > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: > > > > I've written a summary of my findings on implementing and using > > > > materialized views in PostgreSQL. I've already deployed eagerly > > > > updating materialized views on several views in a production > > > > environment for a company called RedWeek: http://redweek.com/. As a > > > > result, some queries that were taking longer than 30 seconds to run > > > > now run in a fraction of a millisecond. > > > > > > > > You can view my summary at > > > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.h > > > >tml > > > > have you done much concurrency testing on your snapshot views? I > > implemented a similar scheme in one of my databases but found problems > > when I had concurrent "refresh attempts". I ended up serializing the > > calls view LOCKing, which was ok for my needs, but I thought > > potentially problematic in other cases. > > > > We are running into some small problems with deadlocks and multiple > inserts. It's not a problem unless we do a mass update to the data or > something like that. I'm interested in how you solved your problem. > Well, I have two different cases actually. In one case I have a master table with what are essentially 4 or 5 matviews based off of that. I don't allow updates to the matviews, only to the master table, and only via stored procedures. This would work better if locking semantics inside of pl functions worked properly, but currently we have the application lock the table in exclusive access mode and then call the function to make the data changes which then fires off a function to update the matviews. Since it's all within a transaction, readers of the matviews are oblivious to the change. IMO this whole method is a wizardry in database hack jobs that I would love to replace. The second case, and this one being much simpler, started out as a view that does aggregation across several other views and tables, which is pretty resource intensive but only returns 4 rows. I refresh the matview via a cron job which basically does a SELECT * FOR UPDATE on the matview, deletes the entire contents, then does an INSERT INTO matview SELECT * FROM view. Again since it's in a transaction, readers of the matview are happy (and apps are only granted select on the matview). Concurrency is kept because the cron job must wait to get a LOCK on the table before it can proceed with the delete/update. I have a feeling that this method could fall over given a high enough number of concurrent updaters, but works pretty well for our needs. > I am playing with an exclusive lock scheme that will lock all the > materialized views with an exclusive lock (see Section 12.3 for a > reminder on what exactly this means). The locks have to occur in order, > so I use a recursive function to traverse a dependency tree to the root > and then lock from there. Right now, we only have one materialized view > tree, but I can see some schemas having multiple seperate trees with > multiple roots. So I put in an ordering to lock the tables in a > pre-defined order. > > But if the two dependency trees are totally seperate, it is possible for > one transaction to lock tree A and then tree B, and for another to lock > tree B and then tree A, causing deadlock. > > Unfortunately, I can't force any update to the underlying tables to force > this locking function to be called. So we will probably call this > manually before we touch any of those tables. Yeah, I ran into similar problems as this, but ISTM you could do a before update trigger on the matview to do the locking (though I'd guess this would end in trouble due to plpgsql lock semantics, so maybe i shouldn't send you down a troubled road...) > > In the future, it would be nice to have a hook into the locking mechanism > so any kind of lock on the underlying tables can trigger this. > > Also, building the dependency trees is completely manual. Until I can get > some functions to actually assemble the triggers and such, automatic > building of the trees will be difficult. > I just noticed that your summary doesn't make use of postgresql RULES in any way, how much have you traveled down that path? We had cooked up a scheme for our second case
Re: [SQL] designer tool connect to PostgreSQL
take a look at http://techdocs.postgresql.org/guides/GUITools
Robert Treat
On Tue, 2004-03-09 at 02:53, BenLaKnet wrote:
>
> Rekall ...
> http://www.totalrekall.co.uk/ <http://www.totalrekall.co.uk/>
> (commercial website)
> http://www.rekallrevealed.org/ <http://www.rekallrevealed.org/>
> (free sources)
>
> for designing different RDBMS like mysql, postgresl, oracle and other
> ...
> free with sources ...
> but packages for windows or linux are not free.
>
> Ben
>
> Yasir Malik a écrit :
>
> Well, there's phpPgAdmin. It's available at
>
> http://phppgadmin.sourceforge.net/ <http://phppgadmin.sourceforge.net/>
>
>
>
> Yasir
>
>
>
> On Mon, 8 Mar 2004 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> wrote:
>
>
>
>
>
> Date: Mon, 8 Mar 2004 10:13:53 +0800
>
> From: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
> To: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>
> Subject: [SQL] designer tool connect to PostgreSQL
>
>
>
> Hi,
>
>
>
> i use postgresql as my database. does anyone know the designer tool that
>
> can connect to postgeSQL ??? meaning to say the tools
>
> can handle design task like create table , etc . appreciate if u can
> give
>
> the specific URL. thanks in advance.
>
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] inverse of "day of year"
When grilled further on (Fri, 19 Mar 2004 09:06:17 -0300), Martin Marques <[EMAIL PROTECTED]> confessed: > Is there a function that would give me the date for a given day of year? > > Something like the inverse of "EXTRACT(doy FROM date)"? > Something like: select date_trunc( 'year', now() ) + (extract( doy from now() ) - 1) * '1day'::interval; Later, Rob -- 06:46:54 up 8 days, 9:58, 2 users, load average: 2.00, 2.11, 2.09 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
[SQL] partial unique constraint
Trying to come up with the proper syntax to meet the following criteria:
create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
note the above syntax is not correct, but should demonstrate what i'm
trying to do; I want to add a unique constraint such that we only allow
one case of bar and baz = true... i can have unlimited bar and baz =
false, and there can be multiple bar and baz = true if the bars are
different... did some doc reading and mail list searching but a valid
syntax for this seems to be escaping me...
btw I'm pretty sure I could do this with an external trigger, but am
wondering about a constraint oriented approach
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] partial unique constraint
On Tue, 2004-04-06 at 11:17, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > Trying to come up with the proper syntax to meet the following criteria:
> > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> The correct way to do it is with a separate CREATE INDEX command:
I think I had initially abandoned looking at that type of solution after
having run across this paragraph in the docs while looking for the
proper constraint syntax:
"Note: The preferred way to add a unique constraint to a table is ALTER
TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
constraints could be considered an implementation detail that should not
be accessed directly."
The subliminal mind is a powerful force eh? Perhaps that paragraph
should be modified... but I'm not sure if it should expanded to include
thoughts along the lines of Stephan's response or maybe just drop the
"should not be accessed directly" bit...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] explain analyze results are different for each iteration
I'm trying to benchmark some complex sql queries. One query, in particular, is causing problems -- its cost values can vary from 228 to 907, its Total Runtimes from 60 ms to 5176 ms. The query plans show that the optimizer is choosing different plans for different iterations of the same query. Does anyone have any idea what's going on here or maybe how I can force Postgres to prefer the faster plan? The query plans are very long (11 tables joined) but I can include them if it might help. Thanks for any advice, Roberto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Query from different Database
When grilled further on (Thu, 7 Oct 2004 16:52:38 +0800 (MYT)), Abdul Wahab Dahalan <[EMAIL PROTECTED]> confessed: > I'm looking for a solution to make a query from two different databases. If > anybody has an experience or know how to solve it, please help me. Thanks. > I believe that the contrib module dblink will do what you want, but I've never used it. Cheers, Rob -- 08:01:41 up 14 days, 10:28, 2 users, load average: 3.27, 2.64, 2.34 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgp0AX9QfGI92.pgp Description: PGP signature
Re: [SQL] diff databases
On Saturday 18 December 2004 09:23, [EMAIL PROTECTED] wrote:
> Hi,
>
> I'm looking for a tool which can compare structure of two databases
> and produce sql commands (ALTER, DROP, CREATE, etc. if needed) which
> could be used then to convert structure of one database to the other.
>
> I would like to use such a tool for postgresql databases especially.
>
Someone just asked this on the admin list the other day... do some archive
checking for more thorough response. Thats said, the best (and just about
only) tool I have seen is by (iirc) ems hi-tech, see thier website for
details.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Single row tables
Hi, with what constraint or how can I ensure that one of my tables has exact one record or 0..1 records? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Implementing queue semantics (novice)
Hi, since I am new to writing stored procedures I'd like to ask first bevore I do a mistake. I want to implement some kind of queue (fifo). There are n users/processes that add new records to a table and there are m consumers that take out these records and process them. It's however possible for a consumer to die or loose connection while records must not be unprocessed. They may rather be processed twice. This seems to me as a rather common problem. But also with atomicy-holes to fall into. How is this commonly implemented? I can imagine an 'add' and a 'get' function together with one aditional 'processed' timestamp-column? Thanks for helping me do the right. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Syntax error while altering col-type
Hi, I am perplexed. I tried to change the type of a column using the syntax I found in the [ALTER TABLE] section: ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4; This shuld be no problem since the current type acutally is int4 and the names are copy'n'pasted. The server responds as follows: ERROR: syntax error at or near "TYPE" at character 47 It seems like it doesn't like the "type". My compiler-experience tells me that the column identifier is somehow broken. But it exists! What went wrong? ---(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] Syntax error while altering col-type
> -Original Message- > > That's exactly the error you'd get on a pre-8.0 system that doesn't > support altering a column's type. Are you looking at 8.0 > documentation > but running a 7.x server? What does "SELECT version();" show? Yes, that's it. I am looking into an 8.0 doc while running 7.4. I'm a little bit perplexed now... is it really the case that pre 8.0 systems aren't able to change col-types? thx > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Syntax error while altering col-type
> > It really is. In fact, the feature was (IIRC) somewhat > controversial, because there are all sorts of decisions that need to > be made about what to do with incompatible types. What if you change > from int8 to int4? What about varchar(4) to char(4)? Just to name > two simple-minded examples. See the -general and -hackers archives > for plenty of previous discussion of this stuff. > but that's the reason, the USING clause exists. It however still remains a problem if triggers, constraints or referential integrity or vews are involved. so far ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Column with recycled sequence value
Hi, suppose I have a let's say heavy used table. There's a column containing UNIQUE in4 values. The data type musn't exceed 32-Bit. Since however the table is heavy used 2^32 will be reached soon and then? There are far less than 4G-records saved thus these values may be reused. How can this be accomplished? I already defined a sequence but then? I appreciate your help. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Inserting or Deleting conditionally
Hi, coming from imperative programming paradigma, I'm currently trying to express something like that in _SQL_: It should be atomic and like an API for a user. I'm therefore writing functions: CRETE FUNC... c := SELECT x,y,z FROM table_a WHERE... IF COUNT(c)=1 then INSERT / DELETE ... WHERE x = c.x ENDIF LANGUAGE SQL; How is this correctly formulated? Or is there no other way than PL/xxx? Thanks ---(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] Returning a bool on DELETE in a proc.
Hi, I'm currently writing a function which encapsulates a delete and should return a bool as indicator for success. I tried: DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; SELECT TRUE; but this makes me not happy. How can I distingruish wehter DELETE affected 0 or more rows and return that while DELETE is not capable of returning any value? And is the whole function executed if the DELETE fails or will it stop bevore the select? I had a read throu SQL-functions while nothing matched me. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Looking for examples of S/P
In order to learn SQL-Stored Procedure techniqes I'm looking for a series of examples. Where can I find examples of SQL and PL/pgSQL based stored procedures? Or any of you who wants to donate some? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] automatic table locking on too many locked records?
Hi all and Michael. An MS-SQL experienced developer warned me that on MS-SQLsvr a whole table gets locked if a certain percentage or amount of records are locked due to an update. And then shortly nothing goes. Does there exist a similar behaviour on pgSQL? Get tables locked if too many records are beein updated? Or something elses? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] returning a record from PL/pgSQL
I just tried hard to return a single record fromout a plpgsql-function. While the (otherwise excelent) documentation didn't give me an answer, I found out that this works: select into ret false, balance, balance; return ret; while ret is a composite type. This construction however tastes not good to me. Is there a nicer way? An example for PL/pgSQL which returns not just a scalar but also a composite type should be added to the documentation. i.e. as second part of the RETURN statement. ---(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] What's the equivalent in PL/pgSQL
Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Accessing objects over db-borders
Hi, Consider one postmaster that manages multiple databases (logical names) Is it possible (and how) to access a stored procedure or view/Table which resides in DB aaa if your DB-connection has currently the context on DB bbb? So: I login on db bbb as a user who has rights on both DBs (aaa,bbb). Can I do a select on a table of DB aaa? If this is possible I suspect referential integrity not to work, does it? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How can I use large object on PostgreSQL Linux Version?
Are you using Fedora with SELinux or just SELinux? -Original Message-From: Premsun Choltanwanich [mailto:[EMAIL PROTECTED]Sent: Montag, 07. Februar 2005 05:41To: pgsql-sql@postgresql.orgSubject: [SQL] How can I use large object on PostgreSQL Linux Version? For first information, I already have Large Object that work fine on PostgreSQL Windows Version. Now I plan to migrate all of Windows version to Linux version. But I got some problem that make it cannot work fine like on Windows version. I found that at least Large Object that now failed and no data can be imported to Linux version. The problem I found was shown like: ERROR: could not access file "$libdir/lo": No such file or directory ERROR: could not access file "$libdir/dbsize": No such file or directory ERROR: could not access file "$libdir/admin": No such file or directory In my idea, it look like the error was related on $libdir. Maybe some setting about $libdir is wrong but I don't know How can I check it? and How can I correct it??
[SQL] parsing a string with a hexadecimal notation
I intend to retrieve an int value in an integer variable from a string with
a hexadecimal notation of a number.
Which function is appropriate to do
i int4
i = ???('BEAF')
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] parsing a string with a hexadecimal notation
> So it should work to do "SELECT int4($1::bit varying)" and then pass > 'xBEEF' as the string value for the parameter. > > regards, tom lane Thanks, that worked for me ---(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] Constraint doesn't see a currently insertet record
Hi,
yes that's my problem.
I've got a table and I put lots of contraints on it so that data stays
consistent. One constraint calls a fcn to do some kind of count() over that
table but it omits the 'to be inserted record'. What to do?
concrete problem:
Suppose a table
i | tel | status | ...
..| \d+ | 1-7| ...
status can be active, deleted or some more values
There may be not two rows where tel is equal and status is not deleted.
Other: As long as status=deleted there may be duplicate tel columns.
How would I make that sure?
My try was to create a check constraint like
("status" = 7) OR (("status" <> 7) AND (num_of_equal_tel_status_not_7("tel")
<= 1))
where num_of_equal_tel_status_not_7 is:
SELECT count(*) FROM "this table" WHERE "tel"=$1 AND "status"<>7
Note: status=7 means deleted
This works well until two recoreds are inserted which are status<>7.
The second gets inserted because the new record is not yet visible.
Another Idea was to make a trigger. But BTW how do I access a trigger
parameter if my trigger function must not have any parameter??
Ideas?
Tahks for any
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] More efficient OR
At least for between, I read that pgSQL rewrites it to a (a -Original Message-
> From: Keith Worthington [mailto:[EMAIL PROTECTED]
> Sent: Mittwoch, 16. Februar 2005 17:36
> To: PostgreSQL SQL
> Cc: Sean Davis; Scott Marlowe
> Subject: Re: [SQL] More efficient OR
>
>
> > > Hi All,
> > >
> > > In several of my SQL statements I have to use a WHERE clause
> > > that contains mutiple ORs. i.e.
> > >
> > > WHERE column1 = 'A' OR
> > > column1 = 'B' OR
> > > column1 = 'C'
> > >
> > > Is there a more efficient SQL statement that accomplishes the
> > > same limiting functionality?
> > >
> > > Kind Regards,
> > > Keith
> > >
> >
> > Scott wrote:
> > The in() construct is (nowadays) basically the same as
> > ORing multiple columns;
> >
> > where column1 in ('A','B','C')
> >
> >
> > Sean Davis wrote
> > Could 'in' or 'between' do what you want? I know that using 'in'
> > is equivalent to what you have below. Could 'between' be more
> > efficient--you could do explain analyze on various options to see
> > what the actual plan would be.
> >
> > Sean
>
> Thanks Scott and Sean for the post.
>
> It sounds like IN will save some typing and code space but
> not decrease the
> execution time.
>
> BETWEEN won't work for my real life query because the
> limiting values are
> quite disparate.
>
> Kind Regards,
> Keith
>
> ---(end of
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
---(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] Relation in tables
You may possibly solve the problem with the inheritted tables with the RULE-System of pgsql. But this seems oversized to me. You could rather create several tables, each with its matching rights/privileges and 'connect' them via an 1:1 relation. The 'real' way such thing is normally done is to write stored procedures which are accessible (Executable) only by those departments that may. If you give these procs SECURITY DEFINER, you can restrict access to the actual tables to only the procedure's owner > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Mittwoch, 16. Februar 2005 17:43 > To: pgsql-sql@postgresql.org > Subject: [SQL] Relation in tables > > > Hello all... > I am starting in Postgresql... > And I have a question: > I am developing a DB system to manage products, but the > products may be > separated by departaments (with its respectives coluns)... Like: > > CREATE TABLE products( >id serial primary key, >desc valchar(100), >... > ); > > Okay, but the products is typed by "amount departament" and > this departament > should not have access to other coluns like "values, Money, etc...". > The "finances departament" may modify the data into products > table, but this > departament should not have access to coluns like "amounts, etc...". > > I' ve tried to create the products table with INHERITS but > its not right... > look: > > CREATE TABLE prod_amounts ( >amount_min numeric, >amount_cur numeric, >amount_max numeric, >... > ) INHERITS products; > > CREATE TABLE prod_values ( >buy_value money, >sen_value money, >... > ) INHERITS products; > > Okay, but the problem is: I can INSERT a prod_amounts normaly > and automaticaly > the products table will be filled, but when i try to modify > the data in > prod_amounts (references in products) there is no data > I think its not right ( I am sure :/ ). > How Can I do it??? > How Can I References Between Prod_amounts and Prod_Values > automaticaly? > remembering the Amounts departament may not access the Values > departament data > and the Values departament may not access the amounts data... > And the products > will be registred (typed) in Amount departament > > Thanks for all. > > ---(end of > broadcast)--- > TIP 9: the planner will ignore your desire to choose an index > scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] dblink versus schemas. What to use in this case?
Hi all, I have got two database schemas. They're rather independend. Thus they are in two databases. However there is one function that needs access to the other database. As I found out, I have two choices: *Using schemas and put the schemas tighter together (via interdependencies). Dumping distinct schemas is possible, however quistionable if a restore will work with the dependencies. *Using dblink. Dblink gives me a loose binding of the two databases. Some of us care about the 'contrib' status of dblink. Speed (connect, query, disconnect may sloww down) and it's deadlock resolv capabilities. However the deadlock thingy is just a question of interest. What should I do? to make one fcn of one DB access another DB's tables/fcns Thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] VIEW / ORDER BY + UNION
Otherwise you can treat this as a subselect and suround it with another select. Like select * from () order by orderno; C:\> -Original Message- C:\> From: Bruno Wolff III [mailto:[EMAIL PROTECTED] C:\> Sent: Mittwoch, 23. Februar 2005 18:20 C:\> To: WeiShang C:\> Cc: pgsql-sql@postgresql.org C:\> Subject: Re: [SQL] VIEW / ORDER BY + UNION C:\> C:\> C:\> On Thu, Feb 17, 2005 at 23:46:59 +0800, C:\> WeiShang <[EMAIL PROTECTED]> wrote: C:\> > Hi, I have created a view like this : C:\> > C:\> > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where C:\> > t1.orderno=t2.orderno); C:\> > C:\> > if I create a SQL statment: C:\> > C:\> > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) C:\> > UNION C:\> > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) C:\> > UNION C:\> > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno); C:\> > C:\> > Will the whole result will be sorted by the field orderno? C:\> C:\> If this isn't a made up example, you don't want to do this. You C:\> should use IN or OR to select records corresponding to the days C:\> of interest and then use ORDER BY to select the ordering. C:\> C:\> ---(end of C:\> broadcast)--- C:\> TIP 7: don't forget to increase your free space map settings C:\> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Junk queries with variables?
In pgadmins SQL-window SQL is the 'language' of choice. Or it is rather the only language. Thus if you intend to program plTk or PL/pgSQL, there's no way around defining a function. (At first you have to define a new language in your schema) C:\> -Original Message- C:\> From: Steve - DND [mailto:[EMAIL PROTECTED] C:\> Sent: Donnerstag, 24. Februar 2005 07:13 C:\> To: pgsql-sql@postgresql.org C:\> Subject: [SQL] Junk queries with variables? C:\> C:\> C:\> I really have to be missing something here and this C:\> probably a *really* noob C:\> question. I don't have a problem running little junk C:\> queries in the pgAdmin C:\> query window(SELECT blah FROM blah, INSERT INTO blah, C:\> etc...), but I can't C:\> figure out how to run queries with variables outside of a C:\> function. I just C:\> want to use variables without having to go about creating C:\> and dropping a C:\> function for every stupid little query I need to write. Example: C:\> C:\> amount int4 := 1000; C:\> earliest_date timestamp := current_timestamp; C:\> C:\> SELECT ... C:\> C:\> I always get the error: "ERROR: syntax error at or near C:\> "amount" at C:\> character 1". What have I done wrong, or am I missing? C:\> C:\> Thanks, C:\> Steve C:\> C:\> C:\> C:\> ---(end of C:\> broadcast)--- C:\> TIP 9: the planner will ignore your desire to choose an C:\> index scan if your C:\> joining column's datatypes do not match C:\> ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Maintaining production DBs, making one schema look like the other
OK, the usual thing: There exists a DB-schema. It is on one hand already in production usage. On the other hand it is still being developed as functions and non-structural stuff are concerned. I found out that EMS Database Comparer helps to replicate the schema differences in form of SQL-statements. From my view this seems to be what I want. What is the psql-way to address this problem? How does one maintain two schemas with PostgreSQL/ are there other tools? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Lambda expressions in SQL
Coming from functional programming, I often wish to write something like that: (LAMDA "expesiveFcn"(x y z) as exfcn update "Tbl5" SET "Column" = exfcn ) In this case "expensiveFcn" is VOLATILE... Is there a way? At least: substituting the lambda by a select doesn't work with update as subquery ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How does the planner treat a table function.
Hi, we have got some tables (uw?) and functions. One function is defined like get_abc(): SELECT a,b,c from table_x; What happens if I query something like SELECT a,b from get_abc() where a=5; while table_x is rather big? Will PSQL at first query all records of table_x and then apply a where a=5 OR will PSQL integrate it to a shorter query? (In case there will be of course an intex over a to prevent sequential search) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Inserting values in arrays
I have the following issue.
Given the following tables:
CREATE TABLE test ( details varchar[]);
CREATE TABLE test2 ( textvalue1 varchar,
textvalue2 varchar);
INSERT INTO test2 VALUES ('Hello1',
'World1');
INSERT INTO test2 VALUES ('hello2',
'World2');
I would like to insert a row in test
for each row of the test2 table i.e.
INSERT INTO test (details) SELECT test2.textvalue1,
test2.textvalue2 FROM test2
and I am expecting the following rows
in test
{'Hello1', 'World1'}
{'Hello2', 'World2'}
The above syntax is giving an error.
How can this be done in postgres ?
Postgres version I am using is 7.3.4
Regards
Robert
Re: [SQL] Inserting values in arrays
Richard,
So the solution can be:
(i)
either write a function to insert the values into the array one by one
(ii)
or else upgrade to 7.4 (or 8) to use the ARRAY syntax.
Thanks a lot.
Regards
Robert
Richard Huxton
03/15/2005 09:08 AM
To
[EMAIL PROTECTED]
cc
pgsql-sql@postgresql.org
Subject
Re: [SQL] Inserting values in arrays
[EMAIL PROTECTED] wrote:
> CREATE TABLE test ( details varchar[]);
> CREATE TABLE test2 ( textvalue1 varchar, textvalue2 varchar);
> INSERT INTO test2 VALUES ('Hello1', 'World1');
> INSERT INTO test2 VALUES ('hello2', 'World2');
> and I am expecting the following rows in test
> {'Hello1', 'World1'}
> {'Hello2', 'World2'}
> Postgres version I am using is 7.3.4
Well, from 7.4 you can do:
INSERT INTO test SELECT ARRAY[textvalue1, textvalue2] FROM test2;
INSERT 0 2
richardh=> SELECT * FROM test;
details
-
{Hello1,World1}
{hello2,World2}
(2 rows)
I think in 7.3 you might have to write your own function to assemble the
array. I'm not an array expert though, so might be worth checking the
mailing list archives.
--
Richard Huxton
Archonet Ltd
[SQL] save me from an unconstrained join
It actually does what I want... but it offends my database
sensibilities... :-)
Heres the basics of the tables involved:
CREATE TABLE bds_filesize (
bds_filesize_id serial
name text NOT NULL,
byte_limit integer NOT NULL,
slots integer NOT NULL
);
CREATE TABLE software (
software_binary_id serial,
binary_file oid,
filename text,
filesize integer,
checksum text
);
query:
select
software_binary_id, min(byte_limit)
from
bds_filesize, software_binary
where
byte_limit > filesize GROUP BY software_binary_id;
Basically each software is assigned a "class" based on the size of its
binary into a predetermined range of classes that are defined as
relative filesizes. The above query really does work... but istm I ought
to be joining those tables somehow... any ideas?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] Postgres 7.3 migrate to 8.0 date problems.
On Mon, 2005-03-28 at 15:48, Scott Marlowe wrote:
> On Mon, 2005-03-28 at 13:44, Thomas Seeber wrote:
> > Hi,
> >
> > We were upgrading from postgres 7.3 -> 8.0 and having a little
> > problems importing dates from some of our data sources. Say we have a
> > date like '2004-17-05'. In postgres 7.3, postgres would intrept this
> > as Year Day Month automatically. In the documentation, from postgres
> > 7.4 on this has to be specified in the Datestyle option and YDM is not
> > an option. Other data we have is coming in on the YMD formate which
> > would be more expected. I realize that this change is better for data
> > integrity, however we have alot of legacy systems where being able to
> > mimic the 7.3 behaviour would be desireable. Any ideas?
>
> Fix the data? I had to write a lot of scripts to keep dates like that
> OUT of my last PostgreSQL installation, which was running 7.2 Which is
> why, as the guy who whinged and moaned until this behavioural change was
> made, I feel for you, but honestly, the lackadaisical manner of handing
> that particular format (-DD-MM) never really seemed right even to
> the people who fought me on the idea of changing the default behaviour
> of DD/MM/ versus MM/DD/.
>
> While the US uses MM/DD/ and Europe uses DD/MM/, and there may
> be some arguments for handling a sloppy version of one of those,
> computer folk (and the government) who want easily ordered dates use
> -MM-DD, I've never seen a good argument made for the usage of
> -DD-MM before.
>
> Are you sure that the other dates in your data set are what you think
> they are? Because if the two numbers are both <=12, then you'll get one
> "date" and if the wrong one is >12 you'll get another. That can't be
> good.
>
Would it be possible to use a BEFORE trigger to reformat the -DD-MM
date to -MM-DD ? The error I see on 7.4 is ERROR: date/time field
value out of range: "2005-14-01" so ISTM you could do some data
manipulation if you wanted.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] btree and is null in a static expression
Hi,
I've written a function but I don't understand the the plan, the planner
makes.
If variables are replaced, the function looks like that:
select a,b,c from "Tbl1" where (a='454') or ('454' is null);
a has got an btree-Index.
explain verbose tells me that Postgres wants to do a SEQSCAN
If the last subexpression is omitted like
select a,b,c from "Tbl1" where (a='454') or false;
The planner wants expecedly utilize an index.
How comes that, while it is obvious that the last subexpression evaluates to
false and thus can be omitted or can even be statically evaluated
I read about btree-Indexes and that they can't be used when null comes into
play. But there?
Thanks
---(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] User Defined Functions Errors
Have a try with RAISE NOTE or RAISE EXCEPTION keep in mind that exceptions should be exceptional. So a good idea of whether to use them is to ask 'Do I expect such error' or 'is an explicit error useful for the caller'. I'ts often better to just return an empty relation |-Original Message- |From: A. Kulikov [mailto:[EMAIL PROTECTED] |Sent: Montag, 18. April 2005 20:32 |To: pgsql-sql@postgresql.org |Subject: [SQL] User Defined Functions Errors | | |How to I return an error from inside a user defined function? For |example the following: | |CREATE or REPLACE FUNCTION drop_node (integer) RETURNS text |AS ' | |DECLARE | |mleft INTEGER; |mright INTEGER; | |BEGIN | |-- Check if the desired node exists |SELECT lft, rgt FROM structure WHERE id = $1 INTO mleft, mright; | |IF mleft IS NULL THEN | RETURN ''No entry found with an id of ''||$2; |END IF; | |-- Drop the node and its subtree |DELETE FROM structure WHERE lft >= mleft AND rgt <= mright; | |-- Close the gap |UPDATE structure SET rgt = rgt - (mright - mleft + 1) WHERE |rgt > mright; |UPDATE structure SET lft = lft - (mright - mleft + 1) WHERE |lft > mleft; | |RETURN ''ok''; | |END; |' |LANGUAGE 'plpgsql'; | |Should be terminated with an error @ RETURN "No Entry found"; instead |of returning the error text. | |best regards and thanks, | |Alex |-- |The mind is essential -- http://essentialmind.com/ | |---(end of |broadcast)--- |TIP 4: Don't 'kill -9' the postmaster | ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] can a function return a virtual table?
That was a nice answer - rather compleete. However at least I am questioning myself for a long time about what happens if one does a select from a SRF. The function may return millions of records (i.e. select * from x where a>1). Is this data streamed through the query process or does postgres create a temporary table. An "explain select * from srf()" just returns a function invocation. :-/ How does this work? |-Original Message- |From: Michael Fuhr [mailto:[EMAIL PROTECTED] |Sent: Dienstag, 19. April 2005 04:43 |To: Kai Hessing |Cc: pgsql-sql@postgresql.org |Subject: Re: [SQL] can a function return a virtual table? | | |On Wed, Apr 13, 2005 at 03:44:25PM +0200, Kai Hessing wrote: |> |> This is the question i'm telling myself. It is because we |don't really |> delete table entries, just setting a status field to '-1'. So a valid |> select would look like: SELECT xyz, abc FROM (SELECT * FROM |tablex WHERE |> status > -1); | |I'll pick a nit and point out that the above isn't a valid query: | |test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); |ERROR: subquery in FROM must have an alias |HINT: For example, FROM (SELECT ...) [AS] foo. | |In this simple example you could omit the subquery altogether: | |SELECT xyz, abc FROM tablex WHERE status > -1; | |> It would be much nicer to have to write something like: |SELECT xyz, abc |> FROM active(tablex); where the function 'active(x)' returns a virtual |> table with all entries from table x where status is > -1. But sadly I |> have no idea how write such a function. Good old O'reilly |can't help (or |> i'm to dumb *g*). | |See the documentation for writing set-returning functions (SRFs). |The following links should get you started (if you're using a version |of PostgreSQL older than 8.0, then see the documentation for that |version): | |http://www.postgresql.org/docs/8.0/interactive/queries-table-ex |pressions.html#QUERIES-TABLEFUNCTIONS |http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html#AEN29503 |http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- |structures.html#AEN32823 | |Another possibility would be to use views. You'd need to create a |view on each table. | |http://www.postgresql.org/docs/8.0/interactive/tutorial-views.html |http://www.postgresql.org/docs/8.0/interactive/sql-createview.html | |Yet another possibility would be to move the inactive rows to a |separate table. You could reconstruct the original table with a |UNION of the active and inactive tables. | |http://www.postgresql.org/docs/8.0/interactive/queries-union.html | |-- |Michael Fuhr |http://www.fuhr.org/~mfuhr/ | |---(end of |broadcast)--- |TIP 8: explain analyze is your friend | ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
