[SQL] Function To Log Changes
Hi All, I have been tinkering with a function to log the changes made on any column through a function and trigger. However, I cant think of a way to make this work through pl/pgsql. Any one have any ideas, or is it just not possible? SNIP create or replace function logchange2() returns OPAQUE as ' DECLARE columnname record; c2 VARCHAR(64); BEGIN /* Cycle through the column names so we can find the changes being made */ FOR columnname IN SELECT attname FROM pg_attribute, pg_type WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP c2 := CAST(columnname.attname AS VARCHAR(64)); /* here lies the problem. How would I make plpgsql see OLD.columnname in a dynamic fashion. I know this wont work whats below, but I am just trying to express what I am trying to do */ IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN /* IF CHANGED DO SOMETHING */ RAISE NOTICE ''Update on column %'', c2; END IF; END LOOP; return NULL; END; ' LANGUAGE plpgsql; create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE PROCEDURE logchange2(); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Entered data appears TWICE in table!!?
I'm JUST getting started with the online SQL tutorial at
http://sqlcourse.com. When I create a table and insert data, the data
appears TWICE. A simple example:
***Create the table:
create table rnmrgntable
(first varchar(20),
last varchar(30));
***Insert data:
insert into rnmrgntable
(first, last)
values ('Bill' , 'Smith');
***Then look at the table:
select * from rnmrgntable;
And I get:
firstlast
BillSmith
BillSmith
EVERYTHING I enter appears twice, duplicated on two rows as in this
example. What the heck's going on?
Ron M.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Invalid Unicode Character Sequence found
Yes, indeed very strange. However, it is certainly fixed, because I also tried more advanced version of postgres and this bug was not in there. Regards Natasa > -Original Message- > From: Markus Bertheau [mailto:[EMAIL PROTECTED] > Sent: Saturday, March 27, 2004 11:02 AM > To: Tom Lane > Cc: Bulatovic Natasa; [EMAIL PROTECTED] > Subject: Re: [SQL] Invalid Unicode Character Sequence found > > > В Птн, 26.03.2004, в 22:43, Tom Lane пишет: > > "Bulatovic Natasa" <[EMAIL PROTECTED]> writes: > > > select id, title from docs where title like 'z%'; or > > > select id, title from docs where title like 'Z%'; > > > It reports the following error: > > > ERROR: Invalid UNICODE character sequence found (0xc000) > > > > This is fixed in 7.3.6. > > I remember to have stumbled over this bug, too. I poked > around in the code a bit but found nothing that hinted to > cause of the bug. So I wonder what the cause of this bug was? > > -- > Markus Bertheau <[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] DB question - Merging data from one table to another.
Title: RE: DB question - Merging data from one table to another. Hello, I'm not sure if this is even possible, but I'll throw it by you anyway. Say I have 2 tables: Table1: With columns number and name 1.1 test1 1.2 test2 1.3 test3 1.4 test4 Table2: With column number and results 1.1 pass 1.2 fail 1.3 pass 1.4 fail What I would like to do is add a new column to Table2 called name and populate the name from table 1 and add it to table 2. So, table 2 will look like: 1.1 pass test1 1.2 fail test2 1.3 pass test3 1.4 fail test4 **NOTE: in same cases if you do a "select name from table1 where number='1.1'" you might get multiple matches, I guess we would need to use "distinct" ? I've never interacted two tables before, so I'm not even sure where to start with this. I'm using postgres 7.4. Thanks all.
Re: [SQL] Function To Log Changes
Gavin, > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? It could be done, but would be extremely slow and awkward using current PL/ pgsql syntax.You'd have to query the system tables for a list of columns, and then execute a series of dynamic queries. I recommend instead one of the following two approaches: 1) Simply log the whole row of each archived table and don't worry about logging the individual columns, or 2) Use PL/tcl, PL/Pyton, or C where you can select columnns by ordinal position or other dynamic factor. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] Getting the ranks of results from a query
I have a query of the form select id from member order by age; id - 431 93 202 467 300 In addition to the id, I would like the get the rank of the row-- in other words: id | rank -+--- 431 | 1 93 | 2 202 | 3 467 | 4 300 | 5 How do I do this with postgres? In the past, I have used something like select id, identity(int, 1,1) from member order by age; is there a postgres equivalent? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] order of results
Bruno Wolff III wrote: On Thu, Mar 25, 2004 at 14:23:00 +0100, Gregor Rot <[EMAIL PROTECTED]> wrote: Hi, i have a table called "people" (name:varchar, lastname:varchar). i do a select on it: select * from people where name like '%n1%' or lastname like '%l1%'. i would like the results in this order: first the results that satisfy only the (name like '%n1%') condition, then the ones that satisfy only the (lastname like '%l1%') condition and last the results that satisfy both conditions. Is this possible in only one SQL? (note that the search conditions n1 and l1 differ from search to search. Yes. You can order by true/false results from conditions to get the results in the desired order. Thank you - sorry, but how do you do that? Tnx, Gregor ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Question on pgsql trigger
Hi there, Im having a go at writing my first set of triggers for postgres and Im having trouble with an error message which the trigger produces when it tries to compile/call the function Ive written in pgsql. The error message is: ERROR: syntax error at or near ";" CONTEXT: compile of PL/pgSQL function "text_update" near line 31 I cant find the error! As far as I can see the syntax is fine from whats in the manual. Can anyone see what the problem with the function below is: CREATE FUNCTION text_update() RETURNS TRIGGER AS' DECLARE allText TEXT; currentRecord RECORD; BEGIN IF TG_WHEN = BEFORE THEN RAISE EXCEPTION ''Trigger function text_update should not be called before INSERT/UPDATE/DELETE''; END IF; IF TG_LEVEL = STATEMENT THEN RAISE EXCEPTION ''Trigger function text_update should be called as a row level trigger''; END IF; IF TG_OP = DELETE THEN DELETE FROM cks_messagetext WHERE cks_messagetext.id = OLD.id; RETURN OLD; ELSIF TG_OP = UPDATE THEN FOR currentRecord IN SELECT textdata FROM cks_part WHERE cks_part.type = 1 AND cks_part.sourcemessageid = NEW.id LOOP allText := allText || '' '' || currentRecord.textdata; END LOOP; allText := allText || '' '' || NEW.subject; UPDATE cks_messagetext SET cks_messagetext.textdata = allText WHERE cks_messagetext.id = NEW.id; RETURN NEW; ELSIF TG_OP = INSERT THEN FOR currentRecord IN SELECT textdata FROM cks_part WHERE cks_part.type = 1 AND cks_part.sourcemessageid = NEW.id LOOP allText := allText || '' '' || currentRecord.textdata; END LOOP; allText := allText || '' '' || NEW.subject; INSERT INTO cks_messagetext (id, textdata) VALUES (NEW.id, allText); RETURN NEW; ENDIF; END; 'LANGUAGE plpgsql; Thank you for any help in advance. Jon Poulton [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] oracle varray functionality?
I've run across a custom type in an oracle database that I am porting to PostGreSQL: create or replace type number_varray as varray(1000) of number; Is the int4array example the same as this? create type int4array(input=int4array_in,output=int4array_out, internallength=variable,element=int4); pgu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] group by not returning sorted rows
I have a query: select cities.name as city, buildings.name as building, pagename, log_date , sum(exhibition_count) as tot from logrecords join cities on (logrecords.city=cities.num) join buildings on (logrecords.building=buildings.num) where advertiser = 'Nielsens' and log_date >= '01/01/2004' and log_date <= '01/31/2004' group by cities.name, buildings.name,pagename,log_date ; I have migrated a database from a redhat 7.3 box running [EMAIL PROTECTED] reports]$ rpm -q postgresql postgresql-7.2.3-5.73 To a redhat 9 box running : [EMAIL PROTECTED] reports]$ rpm -q postgresql postgresql-7.4.2-1PGDG and the rows resulting from the query are no longer sorted by log date. Is this a change since 7.2x? I can achieve the results I need by adding an order by clause identical to the group by but this seems counter intuitive since the rows have to be ordered anyway. Any tips appreciated. Bret ---(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] Function To Log Changes
"Gavin" <[EMAIL PROTECTED]> writes: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. plpgsql won't do it, but you could do it in pltcl, I believe. Or resort to C ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DB question - Merging data from one table to another.
On Thu, 1 Apr 2004, malia, sean wrote: > Hello, > > I'm not sure if this is even possible, but I'll throw it by you anyway. > > Say I have 2 tables: > > Table1: With columns number and name > > 1.1 test1 > 1.2 test2 > 1.3 test3 > 1.4 test4 > > Table2: With column number and results > > 1.1 pass > 1.2 fail > 1.3 pass > 1.4 fail > > What I would like to do is add a new column to Table2 called name and > populate the name from table 1 and add it to table 2. So, table 2 will look > like: > > 1.1 passtest1 > 1.2 failtest2 > 1.3 passtest3 > 1.4 failtest4 You may want to consider using a view to do this, especially if you don't have a 1:1 correspondence. I.e. there are a lot of entries in table2 for 1.1 etc... create view bigview as select * from table2 t2 join table1 t1 on (t2.number=t1.number); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] group by not returning sorted rows
On Mon, 5 Apr 2004, Bret Hughes wrote: > select cities.name as city, buildings.name as building, > pagename, > log_date , > sum(exhibition_count) as tot > from logrecords > join cities on (logrecords.city=cities.num) > join buildings on (logrecords.building=buildings.num) > where advertiser = 'Nielsens' and > log_date >= '01/01/2004' and > log_date <= '01/31/2004' > group by cities.name, buildings.name,pagename,log_date ; > > I have migrated a database from a redhat 7.3 box running > [EMAIL PROTECTED] reports]$ rpm -q postgresql > postgresql-7.2.3-5.73 > > To a redhat 9 box running : > [EMAIL PROTECTED] reports]$ rpm -q postgresql > postgresql-7.4.2-1PGDG > > and the rows resulting from the query are no longer sorted by log date. > Is this a change since 7.2x? Yes. > I can achieve the results I need by adding an order by clause identical > to the group by but this seems counter intuitive since the rows have to > be ordered anyway. They no longer need to always be pre-ordered in order to do the group by (this depends on plan). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question on pgsql trigger
--- Jon Poulton <[EMAIL PROTECTED]> wrote: > Hi there, > Im having a go at writing my first set of triggers > for postgres and Im > having trouble with an error message which the > trigger produces when it > tries to compile/call the function Ive written in > pgsql. The error message > is: > > ERROR: syntax error at or near ";" > CONTEXT: compile of PL/pgSQL function "text_update" > near line 31 > > I cant find the error! As far as I can see the > syntax is fine from whats in > the manual. Can anyone see what the problem with the > function below is: > > > CREATE FUNCTION text_update() RETURNS TRIGGER AS' > DECLARE > allText TEXT; > currentRecord RECORD; > BEGIN > IF TG_WHEN = BEFORE THEN > RAISE EXCEPTION ''Trigger function text_update > should not be called before > INSERT/UPDATE/DELETE''; > END IF; > IF TG_LEVEL = STATEMENT THEN > RAISE EXCEPTION ''Trigger function text_update > should be called as a row > level trigger''; > END IF; > IF TG_OP = DELETE THEN > DELETE FROM cks_messagetext WHERE > cks_messagetext.id = OLD.id; > RETURN OLD; > ELSIF TG_OP = UPDATE THEN > FOR currentRecord IN SELECT textdata FROM cks_part > WHERE cks_part.type = 1 > AND > cks_part.sourcemessageid = NEW.id LOOP > allText := allText || '' '' || > currentRecord.textdata; > END LOOP; > allText := allText || '' '' || NEW.subject; > UPDATE cks_messagetext SET cks_messagetext.textdata > = allText WHERE > cks_messagetext.id = NEW.id; > RETURN NEW; > ELSIF TG_OP = INSERT THEN > FOR currentRecord IN SELECT textdata FROM cks_part > WHERE cks_part.type = 1 > AND > cks_part.sourcemessageid = NEW.id LOOP > allText := allText || '' '' || > currentRecord.textdata; > END LOOP; > allText := allText || '' '' || NEW.subject; > INSERT INTO cks_messagetext (id, textdata) VALUES > (NEW.id, allText); > RETURN NEW; > ENDIF; "END IF" is two words? > END; > 'LANGUAGE plpgsql; > > > Thank you for any help in advance. > > Jon Poulton > > [EMAIL PROTECTED] > > > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ---(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
Re: [SQL] group by not returning sorted rows
Bret Hughes <[EMAIL PROTECTED]> writes: > and the rows resulting from the query are no longer sorted by log date. > Is this a change since 7.2x? Yes. 7.4 can use hashing instead of sorting to bring grouped rows together. > I can achieve the results I need by adding an order by clause identical > to the group by but this seems counter intuitive since the rows have to > be ordered anyway. No they don't; you're making an assumption about the implementation that is no longer warranted. The SQL spec doesn't require it either ... output ordering is only guaranteed if you specify ORDER BY, per spec. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Entered data appears TWICE in table!!?
What interface are you using?
--- "Ron M." <[EMAIL PROTECTED]> wrote:
> I'm JUST getting started with the online SQL
> tutorial at
> http://sqlcourse.com. When I create a table and
> insert data, the data
> appears TWICE. A simple example:
>
> ***Create the table:
>
> create table rnmrgntable
> (first varchar(20),
> last varchar(30));
>
> ***Insert data:
>
> insert into rnmrgntable
> (first, last)
> values ('Bill' , 'Smith');
>
> ***Then look at the table:
>
> select * from rnmrgntable;
>
> And I get:
>
> firstlast
> BillSmith
> BillSmith
>
> EVERYTHING I enter appears twice, duplicated on two
> rows as in this
> example. What the heck's going on?
>
> Ron M.
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map
settings
__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
