Re: [GENERAL] Simple stored procedure examples?
You can use the following:#1 updating ItemName for all rows to 'fox'update tablename set itemname = 'fox';#2 updating ItemName for row where ItemID = 2 to 'fox'update tablename set itemname = 'fox' where itemid = 2; #3 updating ItemName for row where ItemID = 3 to a param value passed in\set var1 3;update tablename set itemname = 'testing' where itemid = :var1;Hope this helps...Thanks,--- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 11/4/06, novnov [EMAIL PROTECTED] wrote:I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot of bare bones simple example stored procs that I can learn from. It would bevery helpful if someone could show me some simple code.In the pgAdmin interface I've been picking SQL as the language, that's the default it offers.Say I have a table Item, and fields ItemID (int4) and ItemName (varchar).What would be the code for#1 updating ItemName for all rows to 'fox'#2 updating ItemName for row where ItemID = 2 to 'fox' #3 updating ItemName for row where ItemID = 3 to a param value passed inThat would be so helpful...I've tried and get errors like ERROR relationitem does not exist, and of course the table Item and the column ItemName do exist, haven't been able to figure out what the error means.--View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726 Sent from the PostgreSQL - general mailing list archive at Nabble.com.---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostGIS Binary RPM for Red Hat Linux
Hello, On Sat, 2006-11-04 at 13:21 +0530, Sandeep Kumar Jakkaraju wrote: I am unable to find PostGIS Binary RPM for Red Hat Linux ...on the postgis.org website ... Can somebody point me to the url from where i can download ...this ?? I am assuming that you want RPMs for RHEL, not RHL. http://www.mammothpostgresql.org/browser/mammothpostgresql/RHEL4/RPMS/8.1.5-mapserver You will find all the prerequisites there. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Simple stored procedure examples?
On Fri, 3 Nov 2006, novnov wrote:
I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot
of bare bones simple example stored procs that I can learn from. It would be
very helpful if someone could show me some simple code.
In the pgAdmin interface I've been picking SQL as the language, that's the
default it offers.
Say I have a table Item, and fields ItemID (int4) and ItemName (varchar).
What would be the code for
#1 updating ItemName for all rows to 'fox'
#2 updating ItemName for row where ItemID = 2 to 'fox'
#3 updating ItemName for row where ItemID = 3 to a param value passed in
I think something like:
sszabo=# create table Item(ItemID int4, ItemName varchar);
CREATE TABLE
sszabo=# create function f1() returns void as $$update Item set
ItemName='fox';$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f2() returns void as $$update Item set
ItemName='fox' where ItemID=2;$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f3(varchar) returns void as $$update Item set
ItemName=$1 where ItemID=3;$$ language 'sql';
CREATE FUNCTION
sszabo=# insert into Item values (1, 'aaa');
INSERT 0 1
sszabo=# insert into Item values (2, 'bbb');
INSERT 0 1
sszabo=# insert into Item values (3, 'ccc');
INSERT 0 1
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | aaa
2 | bbb
3 | ccc
(3 rows)
sszabo=# select f2();
f2
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | aaa
3 | ccc
2 | fox
(3 rows)
sszabo=# select f1()
sszabo-# ;
f1
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | fox
3 | fox
2 | fox
(3 rows)
sszabo=# select f3('monkey');
f3
(1 row)
sszabo=# select * from Item;
ItemID | ItemName
+--
1 | fox
2 | fox
3 | monkey
(3 rows)
As a note, you'll probably pretty quickly move into things for which SQL
isn't a good fit, so you might want to look at plpgsql as well.
That would be so helpful...I've tried and get errors like ERROR relation
item does not exist, and of course the table Item and the column ItemName
do exist, haven't been able to figure out what the error means.
The names were probably doublequoted when created (possibly automatically
by your creating client application) which means you'd need to double
quote them on use as well. Regular (unquoted) identifiers are case-folded,
so Item != Item.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] UNICODE and UTF-8
Hi,I tried to reproduce the same DB on my local server as my web provider gave me access.On this DB (from provider) i can see via phpAdmin pages that encoding is setup to UNICODE.after reading some documentation, i've seen that i had to create my DB on my local server like that : create database mydatabase with encoding='UNICODE';however, when i do this, my encoding is in UTF-8 via phpAdmin.UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? thanks a lot,Al.
Re: [GENERAL] Simple stored procedure examples?
Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? Shoaib Mir wrote: You can use the following: #1 updating ItemName for all rows to 'fox' update tablename set itemname = 'fox'; #2 updating ItemName for row where ItemID = 2 to 'fox' update tablename set itemname = 'fox' where itemid = 2; #3 updating ItemName for row where ItemID = 3 to a param value passed in \set var1 3; update tablename set itemname = 'testing' where itemid = :var1; Hope this helps... Thanks, --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 11/4/06, novnov [EMAIL PROTECTED] wrote: I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot of bare bones simple example stored procs that I can learn from. It would be very helpful if someone could show me some simple code. In the pgAdmin interface I've been picking SQL as the language, that's the default it offers. Say I have a table Item, and fields ItemID (int4) and ItemName (varchar). What would be the code for #1 updating ItemName for all rows to 'fox' #2 updating ItemName for row where ItemID = 2 to 'fox' #3 updating ItemName for row where ItemID = 3 to a param value passed in That would be so helpful...I've tried and get errors like ERROR relation item does not exist, and of course the table Item and the column ItemName do exist, haven't been able to figure out what the error means. -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7175841 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Simple stored procedure examples?
And here is what shows in the SQL window for that proceedure CREATE FUNCTION proc_UpdateItemName(IN strItemName varchar) RETURNS void AS $BODY$update item set itemname = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; The error is always ERROR relation item does not exist. Not using the param, but it doesn't help to remove it. novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? Shoaib Mir wrote: You can use the following: #1 updating ItemName for all rows to 'fox' update tablename set itemname = 'fox'; #2 updating ItemName for row where ItemID = 2 to 'fox' update tablename set itemname = 'fox' where itemid = 2; #3 updating ItemName for row where ItemID = 3 to a param value passed in \set var1 3; update tablename set itemname = 'testing' where itemid = :var1; Hope this helps... Thanks, --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 11/4/06, novnov [EMAIL PROTECTED] wrote: I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot of bare bones simple example stored procs that I can learn from. It would be very helpful if someone could show me some simple code. In the pgAdmin interface I've been picking SQL as the language, that's the default it offers. Say I have a table Item, and fields ItemID (int4) and ItemName (varchar). What would be the code for #1 updating ItemName for all rows to 'fox' #2 updating ItemName for row where ItemID = 2 to 'fox' #3 updating ItemName for row where ItemID = 3 to a param value passed in That would be so helpful...I've tried and get errors like ERROR relation item does not exist, and of course the table Item and the column ItemName do exist, haven't been able to figure out what the error means. -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7175869 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple stored procedure examples?
novnov wrote: And here is what shows in the SQL window for that proceedure CREATE FUNCTION proc_UpdateItemName(IN strItemName varchar) RETURNS void AS $BODY$update item set itemname = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; The error is always ERROR relation item does not exist. Not using the param, but it doesn't help to remove it. novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? Shoaib Mir wrote: You can use the following: #1 updating ItemName for all rows to 'fox' update tablename set itemname = 'fox'; #2 updating ItemName for row where ItemID = 2 to 'fox' update tablename set itemname = 'fox' where itemid = 2; #3 updating ItemName for row where ItemID = 3 to a param value passed in \set var1 3; update tablename set itemname = 'testing' where itemid = :var1; Hope this helps... Thanks, --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 11/4/06, novnov [EMAIL PROTECTED] wrote: I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot of bare bones simple example stored procs that I can learn from. It would be very helpful if someone could show me some simple code. In the pgAdmin interface I've been picking SQL as the language, that's the default it offers. Say I have a table Item, and fields ItemID (int4) and ItemName (varchar). What would be the code for #1 updating ItemName for all rows to 'fox' #2 updating ItemName for row where ItemID = 2 to 'fox' #3 updating ItemName for row where ItemID = 3 to a param value passed in That would be so helpful...I've tried and get errors like ERROR relation item does not exist, and of course the table Item and the column ItemName do exist, haven't been able to figure out what the error means. -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Is the table item spelled with some capital letters? PostgreSQL treats If the table is called Item with a capital I then select * from item will give you this error same with capitalization of column names and everything else. Oisin. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Simple stored procedure examples?
novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? I think you haven't quoted the field names correctly. dun=# CREATE TABLE Item (id int4, ItemName text); CREATE TABLE dun=# INSERT INTO Item VALUES(1,'aaa'); INSERT 0 1 dun=# UPDATE Item SET ItemName = 'fox'; UPDATE 1 dun=# SELECT * FROM Item; id | ItemName +-- 1 | fox (1 row) If you want to have case-sensitive names, you have to have double quotes. MP ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UNICODE and UTF-8
On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: however, when i do this, my encoding is in UTF-8 via phpAdmin. UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versions said one name, some the other, but they mean the same thing. So maybe you have a different version than your provider? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] UNICODE and UTF-8
Yes, my provider has v8.0.1 and i have installed v8.1.4.thanks for your confirmation.Al.On 11/4/06, Martijn van Oosterhout kleptog@svana.org wrote:On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: however, when i do this, my encoding is in UTF-8 via phpAdmin. UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versionssaid one name, some the other, but they mean the same thing.So maybe you have a different version than your provider?Have a nice day, --Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFFTMEVIB7bNG8LQkwRAhH4AJ9PlOoZeP7zQ1VG6/6bT1aqcUTz9gCeKM5UpoP157BzFSv0LUGu8fU1iUc==PJtK-END PGP SIGNATURE-
[GENERAL] bytea / large object and image
Hi,I create a table with some large object (ref: OID) to store some images.When my PHP will display some data, it will also display the images stored as OID.However, i've read that before i must restore the image by exporting them to local (on server) file. isn't it easier in this case, to simply store the path and file name of file to DB and just read the data to display image on PHP pages ?what is the purpose in this case to store image a bytea / large object ? thanks a lot,Al.
Re: [GENERAL] Simple stored procedure examples?
On Sat, 4 Nov 2006, novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? I used an Item table as well, and I can't think of anything between 8.1 and 8.2 beta that would make a difference. If the admin tool quoted the name (which it probably did given the function declaration you showed next), then you have to both double quote and match the capitalization in order to reference the table (none of Item, item or item will match a table created as Item). If you pull out the create functions and calls from my earlier example, do you get the same failures as with your attempt? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] fetching unique pins in a high-transaction
That second query may not be the best, as it will probably seqscan and grab all the pins before only returning the first one ... A partial index where USED_BY is null would eliminate the need for the seqscan on the table... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] bytea / large object and image
On 4 Nov 2006 at 18:24, Alain Roger wrote: However, i've read that before i must restore the image by exporting them to local (on server) file. You can use bytea type to store binary data directly in the database - if it's any use to you, I can send you some ASP code that does this. There was a thread recently on the merits of doing it this way, as opposed to storing the image on the filesystem and keeping just metadata in the DB - you'll find it in the archives. --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] bytea / large object and image
Hi,
I create a table with some large object (ref: OID) to store some images.
When my PHP will display some data, it will also display the images
stored as OID.
However, i've read that before i must restore the image by exporting
them to local (on server) file.
I'm not sure what you mean by 'exporting to local file'. You don't have
to store each image in a separate file, you can store them in a bytea
column, use a script to load the data and send them to the client.
The point is you can't write them with the other data (HTML tags, text
etc) as the browsers handle images as separate objects using the img
tag. So all you have to do is basically something like this
?php
// load the image data from the database
$sql = 'SELECT image_data FROM images WHERE id = ' . $id;
... do the SQL
// send them to the client
head('Content-type: image/png'); // set the correct mime-type
echo $imageData;
?
That's all.
isn't it easier in this case, to simply store the path and file name of
file to DB and just read the data to display image on PHP pages ?
what is the purpose in this case to store image a bytea / large object ?
This is true in case of 'dumb' databases as for example MySQL, as these
databases handle LOB columns pretty bad.
Tomas
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Catch multiple records when doing Select Into
Hugh, that's a long way to do that. Thanks for the help. SWK Ketema wrote: It would be easier if we could see the context in which you are doing the select into. However I think this may help. Try putting the select into in a loop: declare _result record; _rows integer; begin _rows := 0; for _result in select statement here loop in here put logic to raise your exception if you get more than one result _rows := _rows + 1; if _rows 1 then raise exception 'Hey too many rows'; return next _result; end loop; end; On Oct 31, 4:15 am, SunWuKung [EMAIL PROTECTED] wrote: I have a SELECT INTO varname columname FROM ... statement and I would like to raise an exception when that returns more than one record. GET DIAGNOSTICS rowcount = ROW_COUNT seems to return always one for this. Currently I find no other way to do that than to run this statement twice - first to see how many records it returns, which seems very inefficient. I can see that this will be handled for 8.2, but for 8.1 what is the best practice to do this? Thanks. SWK ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Issue when inserting Slovak characters in database via PHP code
Hi,Sorry to cross post this mail but i'm not able to know from where comes my issue.I have a postgreSQL database in UNICODE (UTF-8 in v8.1.4 and UNICODE in v8.0.1).Via my web application i type a sentence in Slovak language and it is stored into DB without any slovak characters. Instead of that, all particular characters are replace with \303\251 or \303\206 or \304\314 and so on... I was thinking that issue was coming from DB encryption but on 2 different versions of DB (see above) i get the same result.after, i was thinking that it was coming from my web browser, but even if i setup character mode in central europe and Slovak language as default coding...nothing change...i tried on IE and Firefox. Last step, i tried to type directly from my PhpPgAdmin (direct typing sentence there to DB), and i realize that when i click on save...the changes appear in DB aswritten above (e.g. : \303\251,...)My latest test was to write via PhpPgAdmin (directly to DB) the UNICODE of slovak character contained within my sentence...so i used #253, #237 and so on... if i do that, those code are correctly saved into DB and when my PHP code show web pages, all sentences are correct.I can not imagine to write a special interface to convert slovak characters to unicode everytime that user would like to type something new. Something else must be badly setup...Please, could you tell me where to search because i'm confused now...thx.Alain
Re: [GENERAL] Issue when inserting Slovak characters in database via PHP code
2006/11/4, Alain Roger [EMAIL PROTECTED]: Hi, Sorry to cross post this mail but i'm not able to know from where comes my issue. I have a postgreSQL database in UNICODE (UTF-8 in v8.1.4 and UNICODE in v8.0.1). Via my web application i type a sentence in Slovak language and it is stored into DB without any slovak characters. Instead of that, all particular characters are replace with \303\251 or \303\206 or \304\314 and so on... I was thinking that issue was coming from DB encryption but on 2 different versions of DB (see above) i get the same result. after, i was thinking that it was coming from my web browser, but even if i setup character mode in central europe and Slovak language as default coding...nothing change...i tried on IE and Firefox. Sounds like all your tests were conducted through a web interface: if the encoding you enter your data in (a function of your environment) is invalid, you might get the data you see in your database. Cheers, t.n.a. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UNICODE and UTF-8
On Nov 4, 2006, at 11:34 , Martijn van Oosterhout wrote: On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: however, when i do this, my encoding is in UTF-8 via phpAdmin. UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versions said one name, some the other, but they mean the same thing. So maybe you have a different version than your provider? Somewhere around release 8, the encoding UTF8 was made to mean what UNICODE meant before. Unicode is not an encoding so UTF-8 is the proper terminology. Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Geometric Datatypes
What is an example of how geometric datatypes are being used? Bob Pawley
[GENERAL] Win32 Silent installer ADDLOCAL is ignored
The ADDLOCAL parameter that allows to only install certain modules for the PostgreSQL via the silent installer for win32 does not. It ends up installing everything. Does anyone have a workaround or fix for this? Here is my command:msiexec /i postgresql-8.1-int.msi /qr /log install.log INTERNALLAUNCH=1 ADDLOCAL=server,plsql,jdbc PL_PGSQL=0 DOSERVICE=1 DOINITDB=1 SERVICENAME=PostgreSQL ALLUSERS=[ALLUSERS] ADDLOCAL=ALL SERVICEDOMAIN=Test SERVICEACCOUNT=postgres SERVICEPASSWORD=pass SUPERUSER=test SUPERPASSWORD=test BASEDIR=c:\PostgreSQLThe end result installs everything including nls,pljava,psql,jdbc,docs,binfiles,includefiles,libfiles,msvclibsP.SurtzJoin Excite! - http://www.excite.comThe most personalized portal on the Web!
Re: [GENERAL] explosion of tiny tables representing multiple
Dear Martijn, Wow, didn't know about arrays. Did lots of sql, but, as I think about it, that was 7 years ago, and we didn't know about arrays then Are their performance problems with arrays? We will not likely be working with more than 50,000 - 100,000 records. Ben In message [EMAIL PROTECTED] Benjamin Weaver [EMAIL PROTECTED], pgsql-general@postgresql.org writes: On Thu, Nov 02, 2006 at 04:36:49PM +, Benjamin Weaver wrote: Dear PostGreSQL experts, I am working with text objects. A text object will have lots of fields that are potentially multiple. There may be more than one author, more than one modern editor, more than one edition number, etc. Have you considered using arrays? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -- Benjamin Weaver Faculty Research Associate, Imaging Papyri Projects, Herculaneum Society, Oxford email: [EMAIL PROTECTED] phone: (0)1865 288260 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to get joins to work
Please note that natural joins may be dangerous in production code. See the following thread for more detailed information... http://forums.oracle.com/forums/thread.jspa?threadID=440287 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] dividing integers not producing decimal fractions
Alexander Staubo wrote: On Nov 2, 2006, at 23:54 , [EMAIL PROTECTED] wrote: I'm trying to produce a decimal fraction by dividing integer fields like this fragment of the query: ...((cntoh0.count + cntoh1.count)/ttl_deptcat.ttlcount) as Ratio... ... How do I get fractions when dividing integers? Cast as something? Exactly. Just as in most computer languages, incidentally: # select 1 / 2 as x; x --- 0 (1 row) # select 1 / 2::float as x; x - 0.5 (1 row) Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq You're right (I dug around in the documentation and edjoocated myself). However: sales=# select 1/2; ?column? -- 0 (1 row) and: sales=# select 1/2::float; ?column? -- 0.5 (1 row) but: sales=# select 1/2*4::float; ?column? -- 0 (1 row) or: sales=# select (1/2)*4::float; ?column? -- 0 (1 row) or try: sales=# select (1/2)* 4::numeric(6,3); ?column? -- 0.000 (1 row) I'll just avoid this next time by not throwing integer around so quickly. (At least until I become something of a postgres obi-wan...) thanks, r ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] I can not add up columns when using a left outer join. Any ideas as to why?
Thanks Richard! All is working fine now... -Original Message- From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] Sent: Friday, November 03, 2006 10:50 AM To: robert.balzli Cc: General PostgreSQL List Subject: RE: [GENERAL] I can not add up columns when using a left outer join. Any ideas as to why? COALESCE works great! Is there a way to default the LEFT JOIN to return zero and not NULL? Then we could use COALESCE on strings only. Most of our columns are integers not strings. This would make our queries simpler for 99% of the columns. Thanks, Robert Balzli PS: I will join the pgsql-general users group today... No. you will need to have: select a, b, coalesce(a,0) + coalesce(b,0) from your table; Regards, Richard Broersma Jr. PS: Also, don't forget to use reply all so that everyone one on the list can participate. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] bytea / large object and image
Raymond O'Donnell wrote: On 4 Nov 2006 at 18:24, Alain Roger wrote: However, i've read that before i must restore the image by exporting them to local (on server) file. You can use bytea type to store binary data directly in the database - if it's any use to you, I can send you some ASP code that does this. bytea is not always a good idea. It depends on the size of the data you are storing. If you are storing pretty much anything greater than 200k I would suggest moving to pg_largeobject instead. Joshua D. Drake There was a thread recently on the merits of doing it this way, as opposed to storing the image on the filesystem and keeping just metadata in the DB - you'll find it in the archives. --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- SPI Liason, PostgreSQL Fundraising Group Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate Find out about PostgreSQL Fundraising: http://fundraising.postgresql.org/ Read the PostgreSQL docs: http://www.postgresql.org/docs/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] opening a channel between two postgreSQL-servers?
Hi. a handy thing in mysql is FEDERATED tables that allows one to open a channel from one MySQL-server to another MySQL-server. it helps a lot when writing stored procedures that transfer data to other servers. you can do the transfer without any extarnal temporary files or external applications that read from one server and insert into another server. Does PG have anything similar? if yes, which chapter in the docs for 8.1 will tell me more? no wars please. i just want to know if such a possibility exists in PG. /stig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Simple stored procedure examples?
Thanks again everyone. I thought pgSQL might be case sensitive so I tried all variations (mentioned all of this in prev msgs) Oh...I just figured out what it was. I did created the proc successfully. The pgAdmin III interface saved it, moved it from the procedures hive to the functions hive. Each time the proc vanished on me I figured that pgAdmin had timed out or had a bug or that I'd hit the cancel button in error. So pgSQL is case sensitive and that include keywords like UPDATE and SET. There what worked, for the record: -- Function: proc_UpdateItemName() -- DROP FUNCTION proc_UpdateItemName(); CREATE OR REPLACE FUNCTION proc_UpdateItemName() RETURNS void AS $BODY$UPDATE Item SET ItemName = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; ALTER FUNCTION proc_UpdateItemName() OWNER TO postgres; Mikko Partio wrote: novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the capitalization of the table and field names (really Item and ItemName). I wonder if Item is a reserved word in pgsql? I think you haven't quoted the field names correctly. dun=# CREATE TABLE Item (id int4, ItemName text); CREATE TABLE dun=# INSERT INTO Item VALUES(1,'aaa'); INSERT 0 1 dun=# UPDATE Item SET ItemName = 'fox'; UPDATE 1 dun=# SELECT * FROM Item; id | ItemName +-- 1 | fox (1 row) If you want to have case-sensitive names, you have to have double quotes. MP ---(end of broadcast)--- TIP 6: explain analyze is your friend -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7176256 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] help with a query
Hi all! This is most certainly a lame question but perhaps someone is gracious enough to lend me a hand ;-) I have the following setup in a table: The first record which is to be found (ok easy enough :D) with a timestamp meets a certain condition (ignition off) The following record is for the event of ignition on again with a timestamp. So the question here is: how can I compute the time difference between these two records in a single query? Better yet: finding all the records that meet the first condition (ignition off) and the immediately following records as to compute the time difference. ;-) Thanks in advance! Pedro Doria Meunier (351) 91 302 49 72 - (351) 96 247 99 12 [EMAIL PROTECTED] MSN - [EMAIL PROTECTED] ICQ - 308-182-126 Skype: pdoriam
Re: [GENERAL] Geometric Datatypes
On Sat, Nov 04, 2006 at 05:01:12PM -0800, Bob Pawley wrote: What is an example of how geometric datatypes are being used? They could be used for simple GIS applications (e.g., storing latitude/longitude as points), although PostGIS would be more suitable for non-trivial needs. Are you asking about PostgreSQL's native geometry types specifically or are you also interested in how PostGIS is being used? If the latter then see their case studies: http://postgis.refractions.net/documentation/casestudies/ -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] opening a channel between two postgreSQL-servers?
On Sat, Nov 04, 2006 at 10:12:00PM +0100, stig erikson wrote: a handy thing in mysql is FEDERATED tables that allows one to open a channel from one MySQL-server to another MySQL-server. it helps a lot when writing stored procedures that transfer data to other servers. you can do the transfer without any extarnal temporary files or external applications that read from one server and insert into another server. Does PG have anything similar? Not in the stock installation but you can establish a connection between one PostgreSQL server and another with contrib/dblink, or with just about any other data source using David Fetter's dbi-link. http://pgfoundry.org/projects/dbi-link/ PostgreSQL has several server-side languages such as PL/Perl, PL/Python, PL/Tcl, PL/Ruby, PL/php, PL/Java, PL/R, etc. In general a server-side function written in one of those languages can do anything a standalone application could do, such as connecting to another database, even a different DBMS (you could connect from PostgreSQL to MySQL, Oracle, SQL Server, etc.). -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Geometric Datatypes
I am looking at PostgreSql native geometry. I am wondering if it can be used in conjunction with a basic drawing package. If so, is there a recommended drawing package to use? Are there any examples of this type of use? Bob - Original Message - From: Michael Fuhr [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Postgresql pgsql-general@postgresql.org Sent: Saturday, November 04, 2006 5:25 PM Subject: Re: [GENERAL] Geometric Datatypes On Sat, Nov 04, 2006 at 05:01:12PM -0800, Bob Pawley wrote: What is an example of how geometric datatypes are being used? They could be used for simple GIS applications (e.g., storing latitude/longitude as points), although PostGIS would be more suitable for non-trivial needs. Are you asking about PostgreSQL's native geometry types specifically or are you also interested in how PostGIS is being used? If the latter then see their case studies: http://postgis.refractions.net/documentation/casestudies/ -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to get joins to work
On Nov 4, 2006, at 5:44 , Anonymous wrote: Please note that natural joins may be dangerous in production code. See the following thread for more detailed information... http://forums.oracle.com/forums/thread.jspa?threadID=440287 All that thread shows is that people are using natural join without understanding what it means. The result is the same in *any* language: using syntax that you don't understand will probably result in unexpected results. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Timeout Value on network error
On Mon, Oct 30, 2006 at 11:46:01AM -0800, [EMAIL PROTECTED] wrote: We're using a remote Postgres server to view video data. It works well, but we're having a hard time making our app robust when the network fails. For example, PQexec does not return consistently when the cable is disconnected. Network connections use TCP, which was designed to be robust over unreliable networks. A TCP connection isn't supposed to fail right away in the face of network problems like disconnected cables; instead, TCP retransmits packets with the hope that connectivity will soon be re-established. Is there a timeout value that we can configure, so all PQ* functions return consistently? You could use asynchronous command processing with poll() or select(). http://www.postgresql.org/docs/8.1/interactive/libpq-async.html -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] ERROR: tuple concurrently updated
Hello, I got this error the other day, I was under the impression that vacuum could get a concurrently updated tuple. I could be wrong. It is possible for somebody to quickly explain this situation? Message follows; vacuumdb: vacuuming of table school.person in database sqlfilter failed: ERROR: tuple concurrently updated Thanks Russell Smith ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] ERROR: tuple concurrently updated
Russell Smith [EMAIL PROTECTED] writes: I got this error the other day, I was under the impression that vacuum could get a concurrently updated tuple. I could be wrong. It is possible for somebody to quickly explain this situation? Message follows; vacuumdb: vacuuming of table school.person in database sqlfilter failed: ERROR: tuple concurrently updated Was this a VACUUM ANALYZE, and if so might there have been another ANALYZE running concurrently on that table? If so, this is just a reflection of concurrent attempts to update the same pg_statistic row. It's harmless since the ANALYZE that didn't fail presumably stored pretty nearly the same results. There is some interlocking to prevent the error in CVS HEAD, though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] opening a channel between two postgreSQL-servers?
I believe contrib/dblink may be what you're after... On Sat, 4 Nov 2006, stig erikson wrote: Hi. a handy thing in mysql is FEDERATED tables that allows one to open a channel from one MySQL-server to another MySQL-server. it helps a lot when writing stored procedures that transfer data to other servers. you can do the transfer without any extarnal temporary files or external applications that read from one server and insert into another server. Does PG have anything similar? if yes, which chapter in the docs for 8.1 will tell me more? no wars please. i just want to know if such a possibility exists in PG. /stig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
