[SQL] subqueries as values in updates
hello, list. are subqueries alloved as values in an update command ? e.g. update a set a.attribute1 = a.attribute1 + (select sum(b.attribute1) from b where b.attribute2=a.attribute2); If yes, how is the correct syntax ? If not, is there a hope for it in a future version ?
RE: [SQL] subqueries as values in updates
Hi, The syntax you used works fine for me. francis=# select version(); version --- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row) Hope this helps Francis Solomon > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > Sent: 08 December 2000 13:00 > To: liste SQL > Subject: [SQL] subqueries as values in updates > > > hello, list. > are subqueries alloved as values in an update command ? > e.g. > > update a set a.attribute1 = a.attribute1 + > (select sum(b.attribute1) from b where b.attribute2=a.attribute2); > > If yes, how is the correct syntax ? > If not, is there a hope for it in a future version ?
Re: [SQL] FOREIGN KEY errors.
There was a bug (which should be fixed for 7.1) that got the arguments wrong for the alter time check of the existing data. I think I should be able to get a patch together to fix it once I get a copy of the 7.0.3 source. Can you send the table schema as well so I can test it out? Stephan Szabo [EMAIL PROTECTED] On Thu, 7 Dec 2000, Joseph Shraibman wrote: > When trying to alter a table and add a foreign key, I am getting this > error if the table has any data in it: > > playpen=# alter table message add FOREIGN KEY (pod,originator) > REFERENCES usertable (podkey,userkey); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) > for FOREIGN KEY check(s) > ERROR: constraint : table usertable does not have an attribute > originator > > If I do the alter before I put any data in the table: > > playpen=# alter table message add FOREIGN KEY (pod,originator) > REFERENCES usertable (podkey,userkey); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) > for FOREIGN KEY check(s) > CREATE > > playpen=# select version(); >version > - > PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 > (1 row) > > Reversing the order creates a different message: > playpen=# alter table message add FOREIGN KEY (originator,pod) > REFERENCES usertable (userkey,podkey); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) > for FOREIGN KEY check(s) > ERROR: constraint : table usertable does not have an attribute > pod > > > Am I just misunderstanding how to use FOREIGN KEY? Then why would it > work one time and not the other? > > http://www.postgresql.org/docs/aw_pgsql_book/node159.html doesn't have > any column names after 'refrences '. > > > -- > Joseph Shraibman > [EMAIL PROTECTED] > Increase signal to noise ratio. http://www.targabot.com >
[SQL] plpgsql
> Hi, there, Is there any way to handle exception ( such as cannot insert duplicate key on a unique index) in plpgsql function? I don't want it abort whole transaction instead I want to do something else if it happened, but I don't want to use a select stmt first to waste the time. In Orcale, in plsql we can say, declare begin do something exception do something else end; How to this exception section in plpgsql -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] How to...
Hi, I'am wondering if it is possible to retrieve the last added record of a table? I don't think there is a default SQl-query to do so. Is there a PostgreSQL way? gr, Willem
Re: [SQL] How to...
> Hi, > > I'am wondering if it is possible to retrieve the last added > record of a table? > I don't think there is a default SQl-query to do so. Is there > a PostgreSQL way? What is it you want to do? If you want to find out what auto- generated ID will be or was inserted, you can use a sequence function, like currval() on the sequence. If you're just working in psql, you're shown the OID of insert as it happens. You could SELECT ... WHERE oid= to get the record back. Some interfaces (like DBD::Pg) provide functions to get this oid, so you could get the record that way. If you don't mean the last insert period, but rather the last insert just to this table, you could add a TIMESTAMP column DEFAULT CURRENT_TIMESTAMP and just select the record w/the latest timestamp. HTH, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
[SQL] Re: "drop constraint trigger" on PostgreSQL
Bryan Field-Elliot wrote:
Searching the archives,
it seems you once tried to find out how to "drop constraint trigger" on
PostgreSQL; did you ever figure it out? Thank
you,Bryan
I wrote the following function and installed it in my database.
I can then call it with a select (yuk) to drop a constraint trigger:
-- Drop all constraint triggers with a given constraint name
-- calling sequence: drop_contrig(constrname)
create function drop_contrig(text) returns text as '
set d(tgname) {}
spi_exec -array d "select c.relname,t.tgname
from pg_class c, pg_trigger t where c.oid = t.tgrelid and tgconstrname
= \'$1\'" {
spi_exec "drop trigger
\\"$d(tgname)\\" on $d(relname)"
}
if {$d(tgname) == {}} {return "No constraint
trigger $1 found"}
return "Drop trigger $d(tgname) on $d(relname)"
' LANGUAGE 'pltcl';
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
