[SQL] PLPGSQL examples
Hi Najm, have you tried the test examples shipped in the tarball (src/pl/plpgsql/test)? In addition, you can find *lots* of examples (mostly with Hungarian comments) on ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/strukturak/* PLPGSQL is one of the best things in PostgreSQL. Easy and useful. Feel free to ask if something can not be understood. Zoltan
Re: [SQL] fetching rows
Hello, Nikolay, Don't use cursors; instead, use: " select * from films limit 25 offset 0 ; " and on the next query: " select * from films limit 50 offset 25 ; " and so on. You have to encode the current offset into the NEXT link, either making it into a button inside a form, with a hidden field containing the offset, or making it an HREF with URL-encoded data (HREF="myform.php?mycounter=50"). I don't remember in which version the LIMIT option appeared; if your version doesn't support it, move on to 7.0.2! Have fun! On Mon, 30 Oct 2000, Nikolay Mijaylov wrote: > Let say we have a select that returns 100 rows. > > I can fetch first 25 with simple sql: > > BEGIN WORK; > DECLARE liahona CURSOR FOR SELECT * FROM films; > FETCH [FORWARD] 25 IN liahona; > CLOSE liahona; > COMMIT WORK; > > but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or > can I skip first 25? > > When i ask this you need to know that Im using PHP in web environment and > try do this to get more performens from the script (now the script fetch > all rows, then jump to needed row) > > --- Fight for a free world: no walls, no windows, no gates.
[SQL] INSERT [IGNORE] INTO TABLE
If I try to insert some row which in the table already exist the postgre don't insert it and tell some error. Everything ok. I'll insert only if there is not the same (same key) record, else don't insert and don't tell me errors about it. In MySQL is a switch [IGNORE]. Thanx for help Petr Jezek
Re: [SQL] Need info: pl/pgsql performance
On Mon, 23 Oct 2000, najm Hashmi wrote: > Hi, I would to know if there is any articles or books that talk about > pl/pgsql performance especially versus C. Thanking you in advance for > your help. I can only say what the documentation says about it in the "Overview" section (please read it if you hadn't done it yet). In practical use I didn't feel any disadvantages in speed comparing to simple queries from psql prompt. Recursive PLPGSQL functions may be slow but I don't think it depends on PLPGSQL internals---IMHO the same queries would be slow from C as well. The performance generally depends on the number of queries you send. I don't suggest submitting a large amount of queries to the server waiting for a few rows of results---I think it's much better submitting a few queries and getting lots of rows. If a query in Postgres can be solved in a fast way using SQL queries, from PLPGSQL you can feel the same speed. I didn't experience any differences in speed comparing SQL language functions and PLPGSQL langauge ones, too. For me writing PLGPSQL functions are much easier because its logic is similar to other programming languages, not like SQL's logic. But some people are familiar with the SQL logic. For them I suggest using SQL functions which are in general enough for everything. Unfortunately I didn't see any benchmark about these things. I am also interested in them. Jan, are you still using your own created language? Zoltan
RE: [SQL] INSERT [IGNORE] INTO TABLE
> If I try to insert some row which in the table already exist the > postgre don't insert it and tell some error. Everything ok. > I'll insert only if there is not the same (same key) record, > else don't insert and don't tell me errors about it. > In MySQL is a switch [IGNORE]. Not a good feature for me. What do you do if the record already exists? Update it? Check existence and then insert or update. If you want, I guess you could wrap that inside a stored procedure.
RE: [SQL] INSERT [IGNORE] INTO TABLE
On Tue, 31 Oct 2000, Edmar Wiggers wrote: > > If I try to insert some row which in the table already exist the > > postgre don't insert it and tell some error. Everything ok. > > I'll insert only if there is not the same (same key) record, > > else don't insert and don't tell me errors about it. > > In MySQL is a switch [IGNORE]. > > Not a good feature for me. What do you do if the record already exists? > Update it? > > Check existence and then insert or update. If you want, I guess you could > wrap that inside a stored procedure. > Well, that's a good idea :-) but I'm not sure about how to create such procedure. I would appreciate Your help. Petr Jezek
RE: [SQL] INSERT [IGNORE] INTO TABLE
> > Check existence and then insert or update. If you want, I guess > you could > > wrap that inside a stored procedure. > > > Well, that's a good idea :-) but I'm not sure about how to create > such procedure. I would appreciate Your help. Quite frankly, I'm not sure either. I'm just starting with PostgreSQL, what I really know is Oracle. I believe it would be something like ... [declare routine, with one argument per record field] ... if exists(select 1 from table_name where [field_key] = [arg_key]) then update table_name set field1 = arg1,...,fieldn = argn where field_key = arg_key; else insert into table_name (field1,...,fieldn) values (arg1,...,argn); end if; ... The syntax is probably off, but that's the idea. I used that several times on oracle.
Re: [SQL] INSERT [IGNORE] INTO TABLE
Edmar Wiggers wrote: > > If I try to insert some row which in the table already exist the > > postgre don't insert it and tell some error. Everything ok. > > I'll insert only if there is not the same (same key) record, > > else don't insert and don't tell me errors about it. > > In MySQL is a switch [IGNORE]. > > Not a good feature for me. What do you do if the record already exists? > Update it? > > Check existence and then insert or update. If you want, I guess you could > wrap that inside a stored procedure. Hi, here is an example of using function using pl/pgsql for inserting and checking whether an instance exists or not. CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE oldUser RECORD; USR ALIAS FOR $1; PWORD ALIAS FOR $2; BEGIN SELECT INTO oldUser * FROM users where username=USR AND password= PWORD; IF FOUND THEN RETURN ''f''; ELSE INSERT INTO USERS(username,password) values(USR,PWORD); RETURN ''t''; END IF; END;' LANGUAGE 'plpgsql'; Regards. Najm
RE: [SQL] INSERT [IGNORE] INTO TABLE
>Not a good feature for me. Me neither, but... >Check existence and then insert or update. At least once a week I seem to be responding to this exact same suggestion. Somebody _please_ tell me if I'm the only one in the whole world who worries about race conditions? What's wrong with just making sure there's a proper unique key, and then just inserting the row? The returned error message will tell you if a failure is due to duplicate key, missing non-null column, or an actual disaster. Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
[SQL] How To Increment A Field Value By Function
Hello! I have read pgsql online docs but still need helps. (And pardon me for cross posting to pgsql-novice as I have not received responses to my problem.) I want to increment the value of field SocialSecurityNo in a centrally controlled table SocialSecurityTable. SocialSecurityTable contains only one row and one field - SocialSecurityNo. Because, I think, SocialSecurityNo in SocialSecurityTable should not even be read by any other user when it is being incremented by a user (or the number will duplicate), and setting transaction isolation level seems to be not restrictive enough in this case, I use table lock as follows: CREATE FUNCTION GetAndSetNextNumber () RETURNS TEXT AS ' DECLARE UsedNumber TEXT; BEGIN BEGIN WORK; LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE; SELECT SocialSecurityNo INTO UsedNumber FROM SocialSecurityTable; --Do a lot of calculation on UsedNumber -- and generate the next free SocialSecurityNo -- and assign this free value to UsedNumber. UPDATE SocialSecurityTable SET SocialSecurityNo=UsedNumber; COMMIT WORK; RETURN UsedNumber; END;' LANGUAGE 'plpgsql'; Question A: Is this above function apporpriately designed, or are there more efficient ways than table locking? Question B: I think statement "LOCK SocialSecurityTable IN ROW EXCLUSIVE MODE;" will fail when this user (user A) executed this statement AFTER another user (user B) and user B has not yet COMMITed his transaction? What are the solutions for this (LOCK TABLE fails in function)? Regards, CN
[SQL] Outer Joins
I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw that they do not support outer joins yet. I was intrigued by their solution of using a union query. Something Like: select a.col1, b.col2 from a,b where a.col1 = b.col2 union select a.col1, NULL from a where a.col1 not in (select b.col2 from b) But I was wondering if the following would work (it does in some other databases) select a.col1, b.col2 from a,b where a.col1 = b.col2 or b.col2 is null or maybe even select a.col1, b.col2 from a,b where a.col1 = b.col2 or a.col1 not in (select b.col2 from b) These would seem to be far more efficient than a union query (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!) Marc Rohloff
[SQL] Problem with coalesce..
Hello, Was wondering if such a call is unsupported, or a bug? 7.0.2 (postgresql, linux redhat 7.0) SELECT COALESCE ((SELECT NULL,1) returns a 1, correct.. however.. SELECT COALESCE ((SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'),1) returns a "UNKNOWN expression type 501" SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'; returns a '0' Thanks.. George
