Re: [SQL] catch an 'update where false' ?
Hi thanks all for your answers ! to Lane Van Ingen: my purpose is to write a trigger on update so that 'users' might then us update without having to perform the if found. I don't know if I was clear about that idea, but the checks on where update will or not succeed, is what I'm trying to hide from the upper-level users for this specific table. If what your sugesting works inside such a trigger, please show my some code of how to do that, beacause it's precisely what I fail to do. to Alvaro Herrera : according to my book, triggers are only implemented whit the FOR EACH ROW clause in postgres, and not available in the FOR EACH STATEMENT case. has this changed in some new version ? which shall I use then ? thanks to both ! Alvaro Herrera wrote: > On Fri, Aug 12, 2005 at 05:13:24PM +0200, santiago wrote: > > Triggers FOR EACH ROW are called once for each updated row. I think you > could try with a "FOR EACH STATEMENT" trigger. > Lane Van Ingen wrote: > Don't know where you are doing this, but I can think of a couple ways: > (1) Do a 'select count(*) ' on what you are trying to > update first to see if it returns a count greater than 0 > (2) If in pl/pgsql functions, you can use the 'IF FOUND' or 'IF NOT FOUND' > construct to see if update was successful > > NOTE: I saw that in version 8.1 you will be able to test a PostgreSQL- > supplied variable, but it is not available until 8.1 release. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to encrypt a column
Hi, I'm interested in encrypting column in table. Are there any example using "C" to create the encrypted column, inserting and retreiving data to/from it? Thank you __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[SQL] sql function: using set as argument
Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay --- Akshay Mathur SMTS, Product Verification AirTight Networks, Inc. (www.airtightnetworks.net) O: +91 20 2588 1555 ext 205 F: +91 20 2588 1445
[SQL] insert into / select from / serial problem
I am using a insert statement like:
INSERT INTO newtable
SELECT field1, field2 FROM anothertable
newtable structure is: serial, varchar, varchar
What syntax do I use to insert the serial field? Ive tried something
like:
INSERT INTO newtable
SELECT nextval('newtable_id_seq'), field1, field2 FROM anothertable
but that doesnt work
Any ideas? Greatly appreciated!
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Select problems
Hi: I've just migrated from Linux/PostgreSQL 7.2 to Windows/PostgreSQL 8.0.3. I have a large view. When I make: "Select . WHERE mydate = 'anydate'" the view lasts 19 seconds to complete. But, when I make: "Select . WHERE mydate >= 'anydate'" the view lasts 7 minutes. With PostgreSQL 7.2 they both last for equal time. I did a vacuum analyze with no success. Please, could you help me? Thanks in advance _ Need software for your hardware? Click here http://www.asg.co.za ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Parentheses in FROM clause and evaluation order.
Hi, I thought that the parenthesis in the table expression (FROM clause), could be used to indicate the desired evaluation order. But, I tried with a couple of samples and the explain command returned me the same result; no matter what parentheses association I used. I am using only INNER JOINs. In fact, I thought that the whole table expression was gonna be evaluated before the WHERE filter. Does the stantard says something about this evaluation order when the parentheses are present? Does PostgreSQL implements this behavior? Thanks in advance. salu2 dario estepario ... ---(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
[SQL] trigger between to different database
Hi everybody, I have two databasese: database1 and database2. I want to set a trigger on a table of database1 which will insert some values to a table of database2. Is it possible with postgresql? How can I find detailed information about that? Thanks in advance, haris ---(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: [SQL] sum but not grouped by?
let ff your table... try this: select a.id, a.date, a.hours, (select sum(b.hours) from ff as b where a.id = b.id group by b.id ) as a from ff as a; bye, Luca Henry Ortega ha scritto: I have the ff data: id | date | hours AAA07-01-2005 3 AAA07-02-2005 4 BBB07-01-2005 6 BBB07-02-2005 2 BBB07-03-2005 7 Would it be possible to get the ff: id | date | hours | id_total AAA07-01-2005 3 7 AAA07-02-2005 4 7 BBB07-01-2005 6 15 BBB07-02-2005 2 15 BBB07-03-2005 7 15 So it's like SUM OF, but not Grouped By? Is this possible at all? Thank you for any help. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] insert into / select from / serial problem
"tgh002" <[EMAIL PROTECTED]> writes: > I am using a insert statement like: > INSERT INTO newtable > SELECT field1, field2 FROM anothertable > newtable structure is: serial, varchar, varchar > What syntax do I use to insert the serial field? I think you want to just let it default, which you'd do with, say, INSERT INTO newtable (fielda, fieldb) SELECT field1, field2 FROM anothertable regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] trigger between to different database
On Tuesday 09 Aug 2005 12:11 am, Haris Saybasili wrote: > Hi everybody, > > I have two databasese: database1 and database2. I want to set a > trigger on a table of database1 which will insert some values to a > table of database2. > > Is it possible with postgresql? How can I find detailed information > about that? more sensible to use schemas -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger between to different database
On Mon, Aug 08, 2005 at 09:41:22PM +0300, Haris Saybasili wrote: > > I have two databasese: database1 and database2. I want to set a > trigger on a table of database1 which will insert some values to a > table of database2. You could use contrib/dblink but you wouldn't have transaction semantics. For example, suppose you insert a record into database1 and the trigger inserts a record into database2. If you then roll back the transaction in database1, there's no simple way to roll back the changes in database2. Another possibility would be to use LISTEN/NOTIFY, which does work with transactions (NOTIFY messages aren't sent until and unless the transaction commits). But this has problems too: you have to find out what record(s) to insert, and what should happen if the insert in database2 fails? Should the changes in database1 be rolled back somehow, even though they've already been committed? Do you really need separate databases? The problems described above would go away if you used multiple schemas in the same database instead of multiple databases. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] catch an 'update where false' ?
On Sun, Aug 14, 2005 at 03:13:15PM +0200, santiago wrote: > according to my book, triggers are only implemented whit the FOR EACH > ROW clause in postgres, and not available in the FOR EACH STATEMENT > case. has this changed in some new version ? which shall I use then ? Yes, it was added in release 7.4. -- Alvaro Herrera () It does it in a really, really complicated way why does it need to be complicated? Because it's MakeMaker. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
