[SQL] conversi ms-sql7 vs postgresql 7.3
Hi.. I have table xx: id debet credit balance 1 10000 0 2 2000 0 0 3 0 2500 0 4 0 100 0 command in ms-sql 7 can use calculate field (column) balance from id=1 to id=4: "update xx set bal=balance=bal+debet-credit" result: id debet credit balance 1 10000 1000 2 2000 0 3000 3 0 2500 500 4 0 100 400 How command sql can use in psotgresql 7.3? thank's a lot betty. ---(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] 7.2 functions that return multiple result sets?
On Sunday 02 February 2003 22:45, mail.luckydigital.com wrote: > Can some one please confirm( with a plpgsql function example please ) a > postgres "7.2" function that can return multiple rows to the client. > > I've gone through the docs and can't find anything to support this -it > seems you can only have one return value or null. > > Yes i'm aware this it is possible in 7.3 - can someone please confirm its > not possible in 7.2 or provide me with an example of how to go about it. IIRC this is a new feature in 7.3, see: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=release.html "Allow functions to return multiple rows (table functions) (Joe)" Ian Barwick [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] Automatic casting
Vicente, > if I try nodo_fecha_activ<= 104422680 then it gives me this error > ERROR: Unable to identify an operator '<=' for types 'numeric' and > 'double precision' This is a known problem that will be fixed in a later version of Postgres. For now, you have to cast. -Josh ---(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] automatic time/user stamp - rule or trigger?
You're doing update right ??? Just update the column... It's even easier if, when you do your updates... You just: UPDATE blah SET field = value,, updatestamp = 'Now()' WHERE condition... GP "Neal Lindsay" <[EMAIL PROTECTED]> wrote in message b1r864$2mpp$[EMAIL PROTECTED]">news:b1r864$2mpp$[EMAIL PROTECTED]... > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. > > Thanks, > -Neal > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Returning records from a function
I've spent the last few hours trying to come up with a function that at the most basic returns the result of a SELECT * FROM . This is easy to do in something like MSSQL or Oracle. For example in MSSQL i can do something like: CREATE PROCEDURE proc_name AS BEGIN SELECT * FROM sometable END How do I do something like that with a Postgresql function/stored procedure? All I really want to know is how to by using a function how can I return the results of a SELECT * query!? The end result of the query I need is more complicated, but I can't even get something that easy to work. Are functions unable to return result sets?? All of the examples i've seen always return a single value, like an int. That doesn't do me any good. I want to use a few stored procedures to create some faster processing for some queries that involve 3-4 tables plus require some input parameters, and return a result set. Any help would be appriciated. I've just recently started using PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking something easy, but for the life of me i just cant figure it out. Thanks, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] 7.2 functions that return multiple result sets?
Can some one please confirm( with a plpgsql function example please ) a postgres "7.2" function that can return multiple rows to the client. I've gone through the docs and can't find anything to support this -it seems you can only have one return value or null. Yes i'm aware this it is possible in 7.3 - can someone please confirm its not possible in 7.2 or provide me with an example of how to go about it. Thank you.
[SQL] Automatic casting
Is there any way of doing nodo_fecha_activ<= 104422680::numeric without casting? nodo_fecha_activ is numeric if I try nodo_fecha_activ<= 104422680 then it gives me this error ERROR: Unable to identify an operator '<=' for types 'numeric' and 'double precision' ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] numeric when compared to literal
When a numeric is compared to a literal < 2^31 - 1 it works well, but compared to a greater literal, it is casted to double precision. Why? How can I correct this without explicit cast? db=> \d tipos Table "tipos" Column | Type | Modifiers ---+-+--- _numeric_ | numeric | _decimal_ | numeric | db=> select * from tipos where _numeric_ <= 2147483648; ERROR: Unable to identify an operator '<=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast db=> select * from tipos where _numeric_ <= 2147483647; _numeric_ | _decimal_ ---+--- 10 |20 (1 row) ---(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] Serialized Transaction Locking Issues
Hello, I'm currently in the midst of working on a serializable transaction which drops indexes on several tables, does a bulk copy, and rebuilds the indexes. Based on what I've read it seemed as though I'd be able to concurrently execute read on queries against these tables, returning results based on the table snapshot from before the serialized transaction began. However, this doesn't work. A simple read-only select query waits until the serialized transaction is finished before returning results. Based on the user documentation, specifically (http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-locking.html#LOCKING-TABLES), it would seem that the only issue in PostgreSQL that could block a simple select call would be an ACCESS EXCLUSIVE lock, which is acquired only by DROP TABLE, ALTER TABLE, and VACUUM FULL, none of which I'm using. In fact, I've noticed this exact behavior with DROP INDEX. Please excuse my futile attempt to outline two concurrent transactions here: testdb=# \d trans_test Table "public.trans_test" Column | Type | Modifiers - ---++--- val integer Indexes: idx_trans_test btree(val) testdb=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET [TRANSACTION 1] BEGIN; BEGIN [TRANSACTION 1] SELECT * FROM trans_test; val - - 1 2 [TRANSACTION 2] SELECT * FROM trans_test; val - - 1 2 [TRANSACTION 1] DROP INDEX idx_trans_test; DROP INDEX [TRANSACTION 2] SELECT * FROM trans_test; ... (Waiting) [TRANSACTION 1] COMMIT; COMMIT (TRANSACTION 2 returns after COMMIT) val - - 1 2 So is this a glitch or am I missing some nuance of a serializable transaction? In either case I'm eager to figure out whats actually going on. Thanks, - -justin _ Sign up for FREE email from bboy.com at http://www.bboy.com _ Select your own custom email address for FREE! Get [EMAIL PROTECTED] w/No Ads, 6MB, POP & more! http://www.everyone.net/selectmail?campaign=tag ---(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] How to return records from a function
Did you look at http://techdocs.postgresql.org/guides/SetReturningFunctions ? You need Postgresql 7.3 to do this. Regards, Tomasz Myrta ---(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] get # of rows while doing SELECT with LIMIT at same time ?
In web applications like say searching it's common to show page sized subsets of a larger result set from a query. It usually takes one query to get the count of the # of results in the query set & another query to get a page sized subset of items to show. SELECT COUNT(*) FROM albums alb, artists art WHERE alb.artist_id=art.artist_id AND art.artist_name = 'U2' SELECT alb.album_name, art.artist_name FROM albums alb, artists art WHERE alb.artist_id=art.artist_id AND art.name = 'U2' LIMIT 0,10 I suspect that since it takes much of the same work to do the count as it does to do the select it'd be faster to be able to get the total count & the limited result set in one query. MySQL has this feature on their to do list & it may already be in MySQL 4.0. Is this something that's possible to do in Postgres or can it be added to the to do list ? - Sam. http://www.mysql.com/doc/en/Nutshell_Other_features.html Functions like SQL_CALC_FOUND_ROWS and FOUND_ROWS() make it possible to know how many rows a query would have returned without a LIMIT clause. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning records from a function
Hi John, It is in the documentation ;-) http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html Also, this tutorial might be helpful: http://techdocs.postgresql.org/guides/SetReturningFunctions good luck, - lex On Sun, 2003-02-02 at 02:20, John C wrote: > I've spent the last few hours trying to come up with a function that > at the most basic returns the result of a SELECT * FROM . > > This is easy to do in something like MSSQL or Oracle. For example in > MSSQL i can do something like: > > CREATE PROCEDURE proc_name AS > > BEGIN > SELECT * FROM sometable > END > > > How do I do something like that with a Postgresql function/stored > procedure? All I really want to know is how to by using a function how > can I return the results of a SELECT * query!? > > The end result of the query I need is more complicated, but I can't > even get something that easy to work. Are functions unable to return > result sets?? All of the examples i've seen always return a single > value, like an int. That doesn't do me any good. > > I want to use a few stored procedures to create some faster processing > for some queries that involve 3-4 tables plus require some input > parameters, and return a result set. > > Any help would be appriciated. I've just recently started using > PostgreSQL and I'm very impressed! I'm sure I'm jsut overlooking > something easy, but for the life of me i just cant figure it out. > > Thanks, > John > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] get # of rows while doing SELECT with LIMIT at same time ?
On Thu, Feb 06, 2003 at 04:01:12AM -0800, Sam Iam wrote: > In web applications like say searching it's common to show page sized > subsets of a larger result set from a query. > > It usually takes one query to get the count of the # of results in the > query set & another query to get a page sized subset of items to show. > > SELECT COUNT(*) FROM albums alb, artists art > WHERE alb.artist_id=art.artist_id AND art.artist_name = 'U2' > > SELECT alb.album_name, art.artist_name FROM albums alb, artists art > WHERE alb.artist_id=art.artist_id AND art.name = 'U2' LIMIT 0,10 > > I suspect that since it takes much of the same work to do the count as > it does to do the select it'd be faster to be able to get the total > count & the limited result set in one query. In OpenACS we just grab a mid-sized chunk and cache it in RAM. Users usually only look at the first few pages, so it suffices for most cases. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + (I)gnore (R)etry (A)bort (M)eltdown ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 7.2 functions that return multiple result sets?
Found this using google from http://archives.postgresql.org/pgsql-sql/2002-01/msg00312.php Depending on what you're doing (and if you're willing to work with the7.2rcs or wait for it), 7.2 allows you to define functions that returncursors that you can then fetch from within the transaction you called thefunction in, so you should be able to do a sequence like:begin;select * from func(param);-- get back name of cursor, say "" --fetch 10 from "";fetch 10 from "";close "";commit;Seems 7.2 is limited to cursors, where 7.3 will do recordsets (and upgrading is SO easy) HTH Chad - Original Message - From: mail.luckydigital.com To: [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 2:45 PM Subject: [SQL] 7.2 functions that return multiple result sets? Can some one please confirm( with a plpgsql function example please ) a postgres "7.2" function that can return multiple rows to the client. I've gone through the docs and can't find anything to support this -it seems you can only have one return value or null. Yes i'm aware this it is possible in 7.3 - can someone please confirm its not possible in 7.2 or provide me with an example of how to go about it. Thank you.
Re: [SQL] Lock timeout detection in postgres 7.3.1
> > I have recently migrated my database from MS Sql > Server to postgresql 7.3.1. In MS SQL SERVER, it is > very easy to set the lock time equals to zero on ROW > LEVEL. So that if some other user try to access the > same data, he/she will get the error immediately. I > have tried to run the same code through VB 6.0 > (windows) using pgsql as database on RED HAT LINUX > 8.0, the only problem i am facing is when ever a user > try to access a pre LOCKED ROW, the program goes into > halt until the first user executes ROLLBACK or COMMIT. > > Is there any way to set the LOCK TIME equals to ZERO > in postgresql 7.3.1? > I'm working on PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 and found a similar behaviour. T1 (within psql): BEGIN; DELETE FROM ; DELETE n T2 (within psql): BEGIN; DELETE FROM ; The documentation says (within Server Runtime Environment) DEADLOCK_TIMEOUT (integer) This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition or not. The check for deadlock is relatively slow, so we don't want to run it every time we wait for a lock. We (optimistically?) assume that deadlocks are not common in production applications, and just wait on the lock for awhile before starting to ask questions about whether it can ever get unlocked. Increasing this value reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. The default is 1000 (i.e., one second), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, so as to improve the odds that the lock will be released before the waiter decides to check for deadlock. This option can only be set at server start. If I get this right, the T2 psql process should terminate within one second, shouldn't it? The postgresql.conf file is as it was right after the installation #deadlock_timeout = 1000 So, I doubt this a bug, but still, there must be a misunderstanding or something else I don't know about. Could someone please enlighten us. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] TIME vs. TIMESTAMP data type
Hi: Are there cases when a TIME data type is a better choice over the TIMESTAMP data type? It seems that PostgreSQL (I'm using 7.2.3) encourage its users to use TIMESTAMP over TIME data type. I said this because of the following: a) More functions for DATE and TIMESTAMP data types such as to_date() and to_timestamp(). Howver, function to_time() does not exist. b) Same amount of storage for TIMESTAMP and for TIME. Time with time zone even need more storage space than a timestamp (12 bytes vs. 8 bytes). c) It's harder to TIMESTAMP to TIME and vice versa, while its easier to cast TIMESTAMP to DATE and vice versa. thank you very much, ludwig __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Lock timeout detection in postgres 7.3.1
--- Christoph Haller <[EMAIL PROTECTED]> wrote: > > I'm working on > PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by > GCC 2.95.2 > and found a similar behaviour. > > T1 (within psql): > BEGIN; DELETE FROM ; > DELETE n > > T2 (within psql): > BEGIN; DELETE FROM ; > > > The documentation says (within Server Runtime > Environment) > DEADLOCK_TIMEOUT (integer) > > This is the amount of time, in milliseconds, to > wait on a lock > before checking to see if there is a deadlock > condition or not. The > If I get this right, the T2 psql process should > terminate within one > second, shouldn't it? > The postgresql.conf file is as it was right after > the installation > #deadlock_timeout = 1000 > > So, I doubt this a bug, but still, there must be a > misunderstanding or > something else > I don't know about. Could someone please enlighten > us. > I don't think there is a deadlock in the example given above. If I'm not mistaken a deadlock occurs if both transactions are waiting for each other to release the lock (i.e T1 waits for T2 to release locks/resources while T2 is also waiting for T1 to release locks/resources. In the above example, T1 doesn't wait for T2 to do something before finishes the transaction (Only T2 is waiting for T1 to finish), hence the condition for deadlock is not met. ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] TIME vs. TIMESTAMP data type
Ludwig Lim wrote: Hi: Are there cases when a TIME data type is a better choice over the TIMESTAMP data type? It seems that PostgreSQL (I'm using 7.2.3) encourage its users to use TIMESTAMP over TIME data type. I said this because of the following: a) More functions for DATE and TIMESTAMP data types such as to_date() and to_timestamp(). Howver, function to_time() does not exist. b) Same amount of storage for TIMESTAMP and for TIME. Time with time zone even need more storage space than a timestamp (12 bytes vs. 8 bytes). c) It's harder to TIMESTAMP to TIME and vice versa, while its easier to cast TIMESTAMP to DATE and vice versa. thank you very much, ludwig Probably you are right, but you can cast into timestamp before using these functions. Do you really need to care amount of storage? Don't forget about INTERVAL type, which is very useful for time calculations. Regards, Tomasz Myrta ---(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] Trigger para fazer log
Oi lista, tenho um trigger que deve validar alguns campos para entrada/saida de funcionarios e que deve fazer um "log" do que vai acontecendo. Por exemplo: quando é o início do expediente, o trigger deverá guardar algo assim no "log": Cadastro da Entrada-1: Data 2003-02-06 Horário: 09:51:00 Se o funcionario tentar burlar o esquema e tentar inserir/atualizar o registro, o trigger deve guardar na tabela log que foi feita essa tentativa e retornar um erro para o meu script PHP. Meu problema: no fim do trigger tenho o seguinte: IF length(m_erro) = 0 THEN ELSE RAISE EXCEPTION ''%'', m_erro; END IF; RETURN null; Quando está "EXCEPTION", o que deveria ser guardado no "log" também é desfeito. Quando coloco "NOTICE", a informação vai para o LOG. Como fazer para o trigger gerar uma "EXCEPTION" e mesmo assim gravar em outra tabela?!?! []'s Mauricio
Re: [SQL] TIME vs. TIMESTAMP data type
Hi Tomasz: --- Tomasz Myrta <[EMAIL PROTECTED]> wrote: > > Probably you are right, but you can cast into > timestamp before using these functions. > Do you really need to care amount of storage? I was just thinking if both TIMESTAMP and TIME have use the same amount of space (I was think TIME might use less space since it doesn't need to store month, year, day as compared to TIMESTAMP), and TIMESTAMP have more functions and is easier to cast, I might as well use TIMESTAMP. > Don't forget about INTERVAL type, which is very > useful for time calculations. > --> I'll check that one out. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Lock timeout detection in postgres 7.3.1
> > > > T1 (within psql): > > BEGIN; DELETE FROM ; > > DELETE n > > > > T2 (within psql): > > BEGIN; DELETE FROM ; > > > > ... > >I don't think there is a deadlock in the example > given above. If I'm not mistaken a deadlock occurs if > both transactions are waiting for each other to > release the lock (i.e T1 waits for T2 to release > locks/resources while T2 is also waiting for T1 to > release locks/resources. In the above example, T1 > doesn't wait for T2 to do something before finishes > the transaction (Only T2 is waiting for T1 to finish), > hence the condition for deadlock is not met. > Yupp, I agree. But from former DBMS I was dealing with, I know this SET TIMEOUT called feature, which if properly set terminated processes like that hanging on T2. Is there something comparable within Postgres? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] TIME vs. TIMESTAMP data type
On 6/2/03 11:04, "Ludwig Lim" <[EMAIL PROTECTED]> wrote: > Are there cases when a TIME data type is a better > choice over the TIMESTAMP data type? Surely this depends on the nature of the data that you want to represent? If you're researching into sleep patterns and want to represent the times each day that subjects say they tend to wake and/or fall asleep, you may want the TIME type, as the important aspect is the time, not the date. The inclusion of a date would be nonsensical. If you want to record *when* an event occurred, you usually want date and time, so TIMESTAMP is more appropriate. Since event timing is a much more frequent requirement than a time-of-day, it's not surprising that the facilities may be better developed for dealing with that type. Julian Scarfe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Trigger para fazer log
Maurício Sessue Otta wrote: Oi lista, tenho um trigger que deve validar alguns campos para entrada/saida de funcionarios e que deve fazer um "log" do que vai acontecendo. Por exemplo: quando é o início do expediente, o trigger deverá guardar algo assim no "log": Cadastro da Entrada-1: Data 2003-02-06 Horário: 09:51:00 Se o funcionario tentar burlar o esquema e tentar inserir/atualizar o registro, o trigger deve guardar na tabela log que foi feita essa tentativa e retornar um erro para o meu script PHP. Meu problema: no fim do trigger tenho o seguinte: IF length(m_erro) = 0 THEN ELSE RAISE EXCEPTION ''%'', m_erro; END IF; RETURN null; Quando está "EXCEPTION", o que deveria ser guardado no "log" também é desfeito. Quando coloco "NOTICE", a informação vai para o LOG. Como fazer para o trigger gerar uma "EXCEPTION" e mesmo assim gravar em outra tabela?!?! []'s Mauricio I can't understand your language ;-) but I suppose, that after "RAISE EXCEPTION" you can't find any information in a log table? RAISE EXCEPTION always aborts transaction and you can't put any log information into tables when doing this. Regards, Tomasz Myrta ---(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] Trigger para fazer log
On Thu, Feb 06, 2003 at 10:06:32AM -0200, Maurício Sessue Otta wrote: > > Quando está "EXCEPTION", o que deveria ser guardado no "log" também é desfeito. >Quando coloco "NOTICE", a > informação vai para o LOG. > > Como fazer para o trigger gerar uma "EXCEPTION" e mesmo assim gravar em outra >tabela?!?! Não dá. Quando você levanta uma exceção (exception) o controle volta para quem chamou o gatilho. Não há como contornar, que eu saiba. Você tem que fazer a inserção na relação desejada antes de levantar a exceção (talvez chamando outra função que o faça). -Roberto P.S.: Tomaz, you understood his question correctly :-) -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + I'm not super man. But I am very dense. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] numeric when compared to literal
Vicente Alabau Gonzalvo <[EMAIL PROTECTED]> writes: > db=> select * from tipos where _numeric_ <= 2147483648; > ERROR: Unable to identify an operator '<=' for types 'numeric' and > 'double precision' 7.3 is more forgiving about this (although there's no free lunch --- some other cases are now less forgiving). regards, tom lane ---(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] changing referential integrety action on existing table
On Thu, 6 Feb 2003, Matthew Nuzum wrote: > I have a table that uses the NO ACTION action for it's referential > integrity. I'd like to change it to CASCADE for the ON DELETE event. > I'm using Postgres 7.2. I think the best way is to drop all three triggers for the constraint and use alter table add constraint to add the constraint again withe the options you want. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] changing referential integrety action on existing table
I have a table that uses the NO ACTION action for it's referential
integrity. I'd like to change it to CASCADE for the ON DELETE event.
I'm using Postgres 7.2.
I noticed that in the output of my pg_dump I have some triggers that
look like:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTriger_*"
Is it possible to use this syntax to change the constraints? The
documentation says it's not intended for general use and isn't very
detailed about it's use.
Here is an example from my dump file:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_43755" AFTER DELETE ON
"packages" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('', 'components', 'packages',
'UNSPECIFIED', 'packageid', 'packageid');
Could I simply change the procedure mentioned from RI_FKey_noaction_del
to RI_FKey_cascade_del?
Do I have to do a DROP TRIGGER first?
I know I can just try it, but last time I got creative with this, it
cost me a couple hours trying to recreate things.
Matthew Nuzum
www.bearfruit.org
[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
