Re: [SQL] Exceptions when 0 rows affected.
Hi Stef,
Your problem depends on what interface/programming language you're using.
In a Begin End transaction you will have to verify if the returned data is
good for you so then you decide if you run at the end "COMMIT" or
"ROLLBACK".
In php (and where is compatible) I suggest this:
//this is php code:
$error = false;
dbExec("BEGIN");
//I use a switch statement so I can jump out of it with a break at any time
switch (1){
case 1:
...
$sql = "Insert";
if (!dbExec($sql)) {$error = true; break;}//was an error?? we jump out
from switch
...
$sql = "Select ";
if (!dbExec($sql)) {$error = true; break;}
...
$sql = "Update";
if (!dbExec($sql)) {$error = true; break;}
...
}//switch
if ($error) dbExec("ROLBACK"); //an error ocured
else dbExec("COMMIT");//there was no error
Hope this helps.
Best regards,
Andy.
- Original Message -
From: "Stef" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, May 10, 2004 4:08 PM
Subject: [SQL] Exceptions when 0 rows affected.
Hi all,
I've been looking on Google,the archives and documentation,
but cannot find what I'm looking for. Maybe I've read the
answer, but it's still evading me.
I'm working with postgres 7.3.4 using the psql client.
I want to know if it's possible to raise an error in a
transactional BEGIN->END block when e.g. an update
or delete statement affects zero rows.
I want the entire transaction block to rollback when
this happens. I can do it in perl and java , but would
in this instance like to load a file with SQL statements
using psql -f . Is it possible?
Kind Regards
Stefan
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Subqueries returning more than one value?
Hi, I am using a function in a subquery, this works ok: SELECT name, (SELECT p_my_func(1)) AS id FROM test; However I would like to have the function return 2 values into the main query... Something like this: SELECT name, (SELECT p_my_func(1)) AS (id, type) FROM test; Of course this gives the error: ERROR: subquery must return only one column Is there any way around this? Thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Exceptions when 0 rows affected.
Hi all, I've been looking on Google,the archives and documentation, but cannot find what I'm looking for. Maybe I've read the answer, but it's still evading me. I'm working with postgres 7.3.4 using the psql client. I want to know if it's possible to raise an error in a transactional BEGIN->END block when e.g. an update or delete statement affects zero rows. I want the entire transaction block to rollback when this happens. I can do it in perl and java , but would in this instance like to load a file with SQL statements using psql -f . Is it possible? Kind Regards Stefan pgp0.pgp Description: PGP signature
[SQL] Trigger function to know which fields are being updated
I am writing a trigger function. How can I know which fields are being updated in the PL/SQL function? For example I have a table here: CREATE TABLE COMPANY ( COMPANY_ID VARCHAR(10) NOT NULL, NAME VARCHAR(30), ADDRESS VARCHAR(30)); I want to write a trigger to block all update statements without updating NAME. I tried the following code block and it doesn't work: IF TG_OP = ''UPDATE'' THEN IF NEW.NAME IS NULL THEN RAISE NOTICE ''Field NAME must be provided!''; END IF; END IF; Are there any functions like the Oracle's UPDATING() predicate? Bernard Cheung _ Linguaphone : Learning English? Get Japanese lessons for FREE http://go.msnserver.com/HK/46165.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SELECT - ORDER BY Croatian characters ....
Thanks, but the problem is that I'm not using this database alone and there exist also some other users; so I've found this locale setings (lc_collate), but this cannot be changed because some indexes will be probably damaged. so database is not set up properly.. but I've started to work with this database and it cannot be changed .so I've to found some other solution .. so if you know if it's feasible to write some function.. thank you very much ;-) Kornelije On Fri, 7 May 2004, Peter Eisentraut wrote: > Kornelije wrote: > > I'm using PostgreSQL and my database contains Croatian Characters > > (ccz...) so when I pose a query, and I use order by clause, the > > result is not sorted properly. > > You need to initdb your database with the proper locale (hr_HR, > probably). Also, choose the right encoding to go along with the locale > (LATIN2?). > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] update table where rows are selected by inner join?
I have two tables orders and customerpaymentnote, which keep denormalized columns of the status in rows related by orderid. The column duplication is intentional, to ease end-user ad-hoc queries. I don't understand the UPDATE FROM clause at: http://www.postgresql.org/docs/7.4/static/sql-update.html I have a query working (slowly) in MS Access with the pgodbc driver, but I need to know the Postgres version of the SQL. Working (but slow) MS Access version: UPDATE orders INNER JOIN customerpaymentnote ON orders.orderid=customerpaymentnote.orderid SET orders.customerdatetimepaid = customerpaymentnote.customerdatetimepaid, orders.customerchargeaspaid = customerpaymentnote.customerchargeaspaid, orders.customerchargestatus = "Payment-Recieved", orders.orderworkflowbillingstateid = "Payment-Recieved"; Postgres translation, which does not yet work: UPDATE orders SET customerdatetimepaid = customerpaymentnote.customerdatetimepaid, customerchargeaspaid = customerpaymentnote.customerchargeaspaid, customerchargestatus = "Payment-Recieved", orderworkflowbillingstateid = "Payment-Recieved" FROM orders INNER JOIN customerpaymentnote ON orders.orderid=customerpaymentnote.orderid Query result with 0 rows will be returned. ERROR: table name "orders" specified more than once Can anyone suggest a proper translation. I'm open to other query strategies, if an IN(...) statement or something else will speed up this slow query. Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Trigger function to know which fields are being updated
--- Bernard Cheung <[EMAIL PROTECTED]> wrote: > I am writing a trigger function. How can I know > which fields are being > updated in the PL/SQL function? > > For example I have a table here: > > > CREATE TABLE COMPANY ( > COMPANY_ID VARCHAR(10) NOT NULL, > NAME VARCHAR(30), > ADDRESS VARCHAR(30)); > > I want to write a trigger to block all update > statements without updating > NAME. I tried the following code block and it > doesn't work: > > > IF TG_OP = ''UPDATE'' THEN > IF NEW.NAME IS NULL THEN > RAISE NOTICE ''Field NAME must be > provided!''; > END IF; > END IF; That should work. Perhaps "name" is not actually null, but rather an empty string? In that case, your test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME = THEN..." > > Are there any functions like the Oracle's UPDATING() > predicate? > > Bernard Cheung > > _ > Linguaphone : Learning English? Get Japanese > lessons for FREE > http://go.msnserver.com/HK/46165.asp > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] update table where rows are selected by inner join?
On Mon, 10 May 2004, Jeff Kowalczyk wrote: > I have two tables orders and customerpaymentnote, which keep denormalized > columns of the status in rows related by orderid. The column duplication > is intentional, to ease end-user ad-hoc queries. I don't understand the > UPDATE FROM clause at: > http://www.postgresql.org/docs/7.4/static/sql-update.html > > I have a query working (slowly) in MS Access with the pgodbc driver, but I > need to know the Postgres version of the SQL. > > Working (but slow) MS Access version: > UPDATE orders > INNER JOIN customerpaymentnote > ON orders.orderid=customerpaymentnote.orderid > SET orders.customerdatetimepaid = customerpaymentnote.customerdatetimepaid, > orders.customerchargeaspaid = customerpaymentnote.customerchargeaspaid, > orders.customerchargestatus = "Payment-Recieved", > orders.orderworkflowbillingstateid = "Payment-Recieved"; > > Postgres translation, which does not yet work: > UPDATE orders > SET customerdatetimepaid = customerpaymentnote.customerdatetimepaid, > customerchargeaspaid = customerpaymentnote.customerchargeaspaid, > customerchargestatus = "Payment-Recieved", > orderworkflowbillingstateid = "Payment-Recieved" > FROM orders INNER JOIN customerpaymentnote > ON orders.orderid=customerpaymentnote.orderid I think you just want something like: FROM customerpaymentnote WHERE orders.orderid=customerpaymentnot.orderid; The update table (orders in this case) is effectively already included in the "from" list. ---(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] working with schema
Hi all, I'm just experimenting with schema usage. I'm going to use it as a fake 'multi-database' system. Is Postgresql support coding schema name using string variable so I can pass it with parameter? I'm give u an example: I have schema: D200401,D200402.D200403,D200404, etc. I've set my user just like the schema name, so who login with D200401 will be using D200401 schema. When someone using D200401 schema, they sometime want to access another schema, so in my thought I can use variable like this: sPointer='D200403' select * from sPointer.myTable -- Question: How to write it to work properly? Thanks William Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(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] working with schema
> > Hi all, > > I'm just experimenting with schema usage. I'm going to use it as a fake > 'multi-database' system. Is Postgresql support coding schema name using string > variable so I can pass it with parameter? I'm give u an example: > > I have schema: D200401,D200402.D200403,D200404, etc. > > I've set my user just like the schema name, so who login with D200401 will be using > D200401 schema. When someone using D200401 schema, they sometime want to access > another schema, so in my thought I can use variable like this: > > sPointer='D200403' > > select * from sPointer.myTable -- Question: How to write it to work properly? > > Thanks > > > William > > SET search_path to D200401 ; SET search_path to D200402 ; ... should do the job. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] not really SQL but I need info on BLOBs
Denis, Interesting, have you had any experience using a SAN for the images? Do you know anyone that has successfully used a SAN to store images that are accessable to multiple servers? I do not, however the last place I worked was in the beginning stages of putting in a SAN. As it was explained, the raid that would store the images would be seen as 'local' to as many servers (running different client services) as the SAN could support. Thanks for the reply. Ted --- Denis Braekhus <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Theodore Petrosky wrote: > | I am working on a project where the IT department > is > | pushing really hard to have all the images in the > db. > | I don't know what the agenda is. I am hopeful to > come > | up with reasons either why this is good or not > good. > > We have never used BLOB support for images, however > one additional > feature of using BLOBs vs files on a disk would be > having easier setup > if multiple servers/clients need to access those > images. > In the case of using a filesystem you would either > have to provide > access to the images over a network filesystem or > replication, whereas > using the DB for storage does not require anything > more than access to > the DB.. > > We are using filesystem replication for situations > like this (multiple > webservers needing access to exactly the same > images), however there are > multiple other options for this situation. Of course > needing to do > editing on the images aswell provides additional > hurdles. > > Bottom line seems to me to be that if this is all to > be located on one > server there is no good reason to not use filesystem > storage for images, > whereas if you need multiple servers and generally a > more complex setup > you should weigh the pros to the cons.. > > Regards > - -- > Denis > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.2-nr2 (Windows XP) > > iD8DBQFAnieIvsCA6eRGOOARAiwYAKCeaMfnq35nGoQRixKAsec/+k4kwwCdHy91 > EyIqpTqWbZimUFdOjaFdpbI= > =Uzfm > -END PGP SIGNATURE- __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
