Re: [SQL] knowing which columns have beend UPDATEd inside a TRIGGER?
Tom Lane <[EMAIL PROTECTED]> writes: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > Is there a way to know which columns are being UPDATEd or INSERTEd from > > inside a trigger, either in C or pl/pgsql? > > Huh? An INSERT always inserts all columns, by definition. Some of them > might be null and/or equal to their default values, but they're all > there. > > For an UPDATE, you could check to see whether old.col = new.col. > This would miss the case where an UPDATE command is explicitly setting > a column to the same value it already had; dunno if you care or not. Another way, is to have an extra boolean column called "updated", or something like that. When you do the UPDATE, you make sure that always updates the row with a 't' in that column. Then you do whatever you need to do with the newly updated rows, and when done, you run another UPDATE to set all rows to 'f' in the "updated" column. Takes two updates, but might sometimes be easier than comparing the rows to see if they've changed. Tomas
Re: [SQL] knowing which columns have beend UPDATEd inside a TRIGGER?
On Tue, Oct 24, 2000 at 06:51:03PM -0400, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > Is there a way to know which columns are being UPDATEd or INSERTEd from > > inside a trigger, either in C or pl/pgsql? > > Huh? An INSERT always inserts all columns, by definition. Some of them > might be null and/or equal to their default values, but they're all > there. *slap* Doh! Thanks for clearing up my mind about this ;-) > For an UPDATE, you could check to see whether old.col = new.col. > This would miss the case where an UPDATE command is explicitly setting > a column to the same value it already had; dunno if you care or not. That is so obvious I didn't think about it, and it's exactly what I need. Thanks a lot, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Hi. This is my friend, Jack Shit, and you don't know him.
Re: [SQL] help on creating table
Saluton, this is easy: On Fri, Oct 20, 2000 at 06:48:54PM +0800, pgsql-sql wrote: ... > NODE1 >+ --- NODE1_1 >+ --- NODE1_2 >|+ --- NODE1_2_1 >+ --- NODE1_3 create table n (id int4, parent int4, data text); insert into n (id, data) values (1, 'node 1'); insert into n (id, parent, data) values (2, 1, 'node 1.1'); insert into n (id, parent, data) values (3, 1, 'node 1.2'); insert into n (id, parent, data) values (4, 3, 'node 1.2.1'); insert into n (id, parent, data) values (5, 1, 'node 1.3'); (you will probably want to use a serial for id, etc.) The idea is to store each node in a row, and to store both the row's id and the id of the parent node in it. When there is no parent node (your node NODE_1), parent is NULLL. HTH, Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
Re: [SQL] Alternate Database Locations
Well I got it working kinda. You have set up an environment for the new directory and export it under the PGSQL super user account PGDATA2=/home/userid export PGDATA2 Then run initlocation PGDATA2 After that you have to restart your postmaster with the data dir as PGDATA2 postmaster -d PGDATA2 Now you can greate a new db in a new location createdb mydb -D PGDATA2 that is it and you have a new location for ALL your database if you use it ... If you do not add -D PGDATA2 for future db it writes to the default location but you may have problems access it. I am still trying to find out how to get multiple postmasters running on different ports at the same time. Does anyone have any clue how to do that? At 12:04 PM 10/25/00 -0700, Roderick A. Anderson wrote: >On Tue, 24 Oct 2000, Brian C. Doyle wrote: > > > Never mind... I got it working > >OK, don't play your cards so close to the vest. What was the problem (and >solution)? > > >TIA, >Rod >-- >Roderick A. Anderson >[EMAIL PROTECTED] Altoplanos Information Systems, Inc. >Voice: 208.765.6149212 S. 11th Street, Suite 5 >FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [SQL] Alternate Database Locations
On Tue, 24 Oct 2000, Brian C. Doyle wrote: > Never mind... I got it working OK, don't play your cards so close to the vest. What was the problem (and solution)? TIA, Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
[SQL] How to call a shell command in rule
Hi, I want send a e-mail when the rows of mytable reaches 100,000, how? -- 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] Get a time from a char column
Hi !
What I would like is this :
insert into tt1
(select a from tt2)
where tt1.x field is a time, and tt2.a field is a char containing '10:00'.
Doing that, Postgres tells me something like 'x is of type time and a of type
bpchar'. That's true, but I can't find the way to cast my char field in a time.
And in the other hand, this works : insert into tt1 values('10:00')
what seems to be the same...
Does someone have an idea ?
[SQL] Surprising sequence scan when function call used
I have a table, login, which has a field by the same name; there's an index
on that field. I was surprised to discover that a SELECT which compares the
login field to a constant uses an Index scan, but if it is compared to a
function call--for example, lower()--a sequence scan is forced.
Any idea why?
Here are more details:
vdsq=> select version();
version
-
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)
vdsq=> \d login
Table "login"
Attribute |Type |Modifier
--+-+---
-
id | integer | not null default nextval('login_id_seq'::text)
login| char(8) | not null
password | char(8) | not null
jobid| integer | not null
type | smallint| not null
entryid | integer |
lastactivity | timestamp |
trackid | varchar(50) |
roundid | integer |
Indices: idx_login_entryid,
idx_login_jobid,
idx_login_login,
idx_login_password,
idx_login_type,
login_pkey
vdsq=> explain select * from login where login.login = 'foo';
NOTICE: QUERY PLAN:
Index Scan using idx_login_login on login (cost=0.00..582.61 rows=609
width=62)
EXPLAIN
vdsq=> explain select * from login where login.login = lower('foo');
NOTICE: QUERY PLAN:
Seq Scan on login (cost=0.00..1361.86 rows=609 width=62)
EXPLAIN
Re: [SQL] Alternate Database Locations
"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > I am still trying to find out how to get multiple postmasters running on > different ports at the same time. Does anyone have any clue how to do that? Uh, you just do it. Start each postmaster in a different data directory and with a different port number (-D and -P switches) and you're set. Unless you run out of shared memory or some such, in which case some tweaking of kernel parameters is called for... regards, tom lane
[SQL] Add Constraint
Hi all, I am a newbie to Postgresql, but I am familiar with SQL. I am trying to add a constraint to my table using the ALTER TABLE command. The command goes like this : ALTER TABLE USER_SIGNUP ADD CONSTRAINTP_USER_SIGNUP_USER_ID PRIMARY KEY(user_id); But it is returning the error ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented Can anyone guide me in the right direction??? Thanks. Shiva
[SQL] How to Return number of rows updated in stored procedure
Hi, can anybody tell me how to capture the number of rows updated in an update query inside a stored procedure? This doesn't work but hopefully you will see what I mean. CREATE FUNCTION "test" () RETURNS int AS ' DECLARE v_number_of_rows int; BEGIN select into v_number_of_rows update carbon_user set email_confirmed = ''Y'' where email_confirmed = ''Y''; RETURN v_myvar; END; ' LANGUAGE 'plpgsql'; Thanks in advance!!!
Re: [SQL] Surprising sequence scan when function call used
"Will Fitzgerald" <[EMAIL PROTECTED]> writes:
> vdsq=> explain select * from login where login.login = lower('foo');
> Seq Scan on login (cost=0.00..1361.86 rows=609 width=62)
7.0 is a little bit stupid about cross-data-type comparisons (lower()
yields text, not char(n)). This example works OK in current sources,
but until 7.1 comes out you'll need to write something like
where login.login = lower('foo')::char;
Or change the login field to type text...
regards, tom lane
Re: [SQL] How to call a shell command in rule
On Wed, 25 Oct 2000 10:44:48 -0700, Jie Liang said: > Hi, > > I want send a e-mail when the rows of mytable reaches 100,000, how? > > > one way is to make a function using perl and use say mail::sender as the module to send the info use the code snippet in perldoc Mail::Sender another would be to build a cron perl job that would run every so often and check out how many rows then send email > -- > 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] plperl
hi, I followed README tried to install plperl: su-2.04# cd /work/src/pgsql702/src/pl/plperl su-2.04# perl Makefile.PL Writing Makefile for plperl su-2.04# make "../../../src/Makefile.global", line 135: Need an operator "../../../src/Makefile.global", line 139: Missing dependency operator "../../../src/Makefile.global", line 143: Need an operator "../../../src/Makefile.global", line 144: Missing dependency operator "../../../src/Makefile.global", line 148: Need an operator "../../../src/Makefile.global", line 149: Need an operator "../../../src/Makefile.global", line 150: Need an operator "../../../src/Makefile.port", line 1: Need an operator "../../../src/Makefile.port", line 3: Need an operator "../../../src/Makefile.port", line 6: Need an operator "../../../src/Makefile.port", line 8: Need an operator "../../../src/Makefile.port", line 16: Need an operator "../../../src/Makefile.global", line 246: Missing dependency operator "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom "../../../src/Makefile.global", line 248: Need an operator "../../../src/Makefile.global", line 253: Missing dependency operator "../../../src/Makefile.global", line 255: Need an operator "../../../src/Makefile.global", line 284: Missing dependency operator "../../../src/Makefile.global", line 286: Need an operator "../../../src/Makefile.global", line 288: Missing dependency operator "../../../src/Makefile.global", line 290: Need an operator "../../../src/Makefile.global", line 292: Missing dependency operator "../../../src/Makefile.global", line 294: Need an operator "../../../src/Makefile.global", line 296: Need an operator "../../../src/Makefile.global", line 299: Need an operator "../../../src/Makefile.global", line 301: Need an operator "../../../src/Makefile.global", line 304: Need an operator make: fatal errors encountered -- cannot continue su-2.04# what I need to do? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
