Re: [SQL] Triggers
I try pgmail and that is well running ... fo security, do not use attachment files with your mail ... try pgmail, you need to use pl/tclu ... Ben Sai Hertz And Control Systems wrote: Dear Uzo , Hi, does postgresql support the ability to email as in SQL Server? I want to create a trigger which on input of a record will send out an email. Is this possible? http://pgmail.sourceforge.net/ is what you need. Regards, Vishal Kashyap ---(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] Insert into
El lun, 12-01-2004 a las 10:12, [EMAIL PROTECTED] escribió: > > Hi, > / > > My SELECT STATEMENT : > > dwnc=> insert into cust_lo_dim > dwnc-> (lo_no,lo_date,rcvdate,lo_status) > dwnc-> select c.lono,c.lodate,c.rcvdate,c.status > dwnc-> from custlo_temp c ; > ERROR: column "lo_date" is of type date but expression is of type > text > You will need to rewrite or cast the expression > Questions : > > 1) How to rewrite /cast the expression above ??? same goes to > others column . Insert into cust_lo_dim (lo_no, lo_date, rcvdate, lo_status) select c.lono, c.lodate::date, c.rcvdate::date, c.status from custlo_temp c; > 2) lo_key is the column which values comes from sequence clo_seq. what > should i do first b4 insert into cust_lo_dim ??? Nothing. Are you getting some error? -- Gabriel Dovalo signature.asc Description: Esta parte del mensaje =?ISO-8859-1?Q?est=E1?= firmada digitalmente
[SQL] problem with function trigger
Hi I'm trying to update a table column with a pl/pgsql function and a trigger. But I didn't managed to make it work so far. Here's my function code : CREATE FUNCTION public.calcul_impact() RETURNS opaque AS ' DECLARE id_line integer; quantity integer; single_price real; total_cost real; amort integer; month integer; impact real; BEGIN SELECT INTO id_line id_line_table FROM table WHERE id_line_table = NEW.id_line; SELECT INTO single_price single_price_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line; SELECT INTO month month_previ FROM table WHERE id_line_table = NEW.id_line; SELECT INTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line; IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost; ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 - month)*(total_cost/(amort*12))); END IF; END IF; END IF; END IF; IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN UPDATE table SET impact_previ = impact WHERE id_line_table = NEW.id_line; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE PROCEDURE calcul_impact(); I always get the error : Error SQL : ERROR: record "new" has no field named "id_ligne" Has anyone an idea about what's wrong ? thanks for answering me
[SQL] Problems with postgresql 7.4.1 configuration - URGENT
Hi, this all started because I wanted to install pltclu so that I could gain access to pgmail using tcl. I have re-run the build and even though I have specified --with-tcl as one of the components of the build, tcl is not installed in the /lib directory. Has the tcl file been renamed in the /lib directory and is no longer called pltcl.so? Could anyone mail me the pltcl.so file so that using createlang pltclu I can gain access to email facilities using pgmail? I am running OS X 10.3.2 regards Uzo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Email function using c instead of tclu
Hi, I have tried without any success to get this pgmail for tclu resolved. Does anyone have or know of the same sort of function as pgmail but supporting c, as this is one of the installed languages I have access to under postgresql. So for instance: CREATE FUNCTION sendemail(x,x) LANGUAGE 'c'; regards Uzo ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problems with postgresql 7.4.1 configuration - URGENT
beyaRecords - The home Urban music wrote: > this all started because I wanted to install pltclu so that I could > gain access to pgmail using tcl. I have re-run the build and even > though I have specified --with-tcl as one of the components of the > build, tcl is not installed in the /lib directory. Has the tcl file > been renamed in the /lib directory and is no longer called pltcl.so? It should be under $prefix/lib/postgresql/. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with postgresql 7.4.1 configuration - URGENT
beyaRecords - The home Urban music <[EMAIL PROTECTED]> writes: > I am running OS X 10.3.2 Last I checked, OS X had tcl but not tk, so you have to configure --with-tcl --without-tk to get it to build pltcl. Try again (and pay some attention to the error messages this time ;-)) 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] Problems with postgresql 7.4.1 configuration - URGENT
Dear Uzo , Could anyone mail me the pltcl.so file so that using createlang pltclu I can gain access to email facilities using pgmail? I am running OS X 10.3.2 I am not sure if this happens in OS X you may do something equivalent of su -c"gmake install-all-headers" add the libs path to /etc/ld.so.conf ldconfig then try creating the language Hope this helps Regards, Vishal Kashyap ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Copying rows between tables?
I know I can select from one table into a new table, but is there a way to select from one table into an existing table (aside from copying everything to a file, editing the file and then copying from that file)? Without destroying existing entries, of course... I have an application where old records are archived into 'archive' tables. Occasionally there is a need to copy some of these old records into the 'active' table. Thanks for any pointers! Steve -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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] Copying rows between tables?
- Original Message - From: "Steve Wampler" <[EMAIL PROTECTED]> To: "Postgres-SQL" <[EMAIL PROTECTED]> Sent: Tuesday, January 13, 2004 18:23 Subject: [SQL] Copying rows between tables? > > I know I can select from one table into a new table, but is > there a way to select from one table into an existing table > (aside from copying everything to a file, editing the file > and then copying from that file)? Without destroying existing > entries, of course... INSERT INTO table1(row1, row2) SELECT row1, row2 FROM archive_table; > > I have an application where old records are archived into > 'archive' tables. Occasionally there is a need to copy > some of these old records into the 'active' table. > > Thanks for any pointers! > Steve > -- > Steve Wampler -- [EMAIL PROTECTED] > The gods that smiled on your birth are now laughing out loud. > > ---(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 ---(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] Copying rows between tables?
On Tue, Jan 13, 2004 at 09:23:48AM -0700, Steve Wampler wrote: > > I know I can select from one table into a new table, but is > there a way to select from one table into an existing table > (aside from copying everything to a file, editing the file > and then copying from that file)? Without destroying existing > entries, of course... insert into desttable (col1, col2, col3) select col1, col2, col3 from sourcetable where somecol = somevalue; -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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] can insert 'null' into timestamp type field from command line but not from input file?
The table is like this: maxware=# \d test; Table "public.test" Column |Type | Modifiers +-+--- a | timestamp without time zone | b | integer | = The following insert command works fine: maxware=# insert into test (a,b) values (null,'1'); But importing from an input data file does not seem to work as shown below: = maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as '|'; ERROR: invalid input syntax for integer: "null" CONTEXT: COPY tbl_spcase, line 1, column col_id_spcase: "null" == The input.data file just has one line. But no matter what I did, neither of the following input format worked. 1. null|1 2. |1 3. ''|1 How should I represent blank value for the field that's of timestamp type? Thanks in advance for any help, Bing ---(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] can insert 'null' into timestamp type field from command
I've figured it out, that is using 'null as ' with the COPY command. Bing > The table is like this: > > > maxware=# \d test; >Table "public.test" > Column |Type | Modifiers > +-+--- > a | timestamp without time zone | > b | integer | > = > > The following insert command works fine: > > maxware=# insert into test (a,b) values (null,'1'); > > But importing from an input data file does not seem to work as shown > below: > > = > maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as > '|'; > ERROR: invalid input syntax for integer: "null" > CONTEXT: COPY tbl_spcase, line 1, column col_id_spcase: "null" > == > > The input.data file just has one line. But no matter what I did, neither > of the following input format worked. > > 1. null|1 > 2. |1 > 3. ''|1 > > How should I represent blank value for the field that's of timestamp type? > > Thanks in advance for any help, > > Bing > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] can insert 'null' into timestamp type field from command
On Tue, 13 Jan 2004, Bing Du wrote: > The table is like this: > > > maxware=# \d test; >Table "public.test" > Column |Type | Modifiers > +-+--- > a | timestamp without time zone | > b | integer | > = > > The following insert command works fine: > > maxware=# insert into test (a,b) values (null,'1'); > > But importing from an input data file does not seem to work as shown below: > > = > maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as '|'; > ERROR: invalid input syntax for integer: "null" > CONTEXT: COPY tbl_spcase, line 1, column col_id_spcase: "null" > == > > The input.data file just has one line. But no matter what I did, neither > of the following input format worked. > > 1. null|1 > 2. |1 > 3. ''|1 Here's a simple way to find out. use pg_dump to dump the table: psql db=>create table test (dt timestamptz, id int); db=>insert into test (dt, id) values (NULL,22); db=>\q pg_dump db -t test -- Data for TOC entry 3 (OID 705319) -- Name: test; Type: TABLE DATA; Schema: public; Owner: marl8412 -- COPY test (dt, id) FROM stdin; \N 22 \. Note that a null is imported / exported as \N ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Copying rows between tables?
Dear Steve Wampler I have an application where old records are archived into 'archive' tables. Occasionally there is a need to copy some of these old records into the 'active' table. source_table(id: bigserial,uname: char,x1: char,x2: char,x3: char,x4: char) dest_table(id: bigserial,uname: char,x11: char,x21: char,x31: char,x41: char) you will do some thing like insert into dest_table(id,uname,x11,x21) (select id,uname,x1,x2 from source_table) Shootback if this helps Regards, Vishal Kashyap ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Configure issues
Hi, I am in the process of re-building postgresql 7.4.1, and on running configure I get the following output, of interest to me is the error message about ant even though it is installed in /library/ant, so i don't know why it is complaining. Any ideas?: % ./configure --with-perl --with-python --with-tcl --without-tk --with-java --with-openssl=/usr/local/ssl --without-readline --enable-debug checking build system type... powerpc-apple-darwin7.2.0 checking host system type... powerpc-apple-darwin7.2.0 checking which template to use... darwin checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking how to turn off strict aliasing in gcc -no-cpp-precomp... -fno-strict-aliasing configure: using CFLAGS=-O2 -fno-strict-aliasing -g checking whether the C compiler still works... yes checking how to run the C preprocessor... gcc -no-cpp-precomp -E checking allow thread-safe client libraries... no checking whether to build with Tcl... yes checking whether to build with Tk... no checking whether to build Perl modules... yes checking whether to build Python modules... yes checking whether to build Java/JDBC tools... yes checking for jakarta-ant... no checking for ant... no checking for ant.sh... no checking for ant.bat... no checking whether works... no configure: error: ant does not work Uzo ---(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] Configure issues
beyaRecords - The home Urban music wrote: > I am in the process of re-building postgresql 7.4.1, and on running > configure I get the following output, of interest to me is the error > message about ant even though it is installed in /library/ant, so i > don't know why it is complaining. Any ideas? Apparently it is not in your path. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Rule won't let me NOTIFY, no matter how hard I try
Here's the setup: I wanted to write a rule that would fire on an update to one table, and do an update to another table, followed by a notify. My first attempt wasn't acceptable to PG (7.3.4): create rule "my_rule" as on update to table_A where new.col_A != old.col_A do (update table_B ...; notify "my_signal"; ); ... because you can't have a "notify" statement in a rule that fires on update (only select, update, and delete, I guess). Second attempt was to "hide" the notify in a function: create function fn_notify(TEXT) returns VOID as ' execute ''notify " || $1 || "''; ' language 'plpgsql'; Oddly enough, this works IF and ONLY IF the rule "my_rule" fires and the internal update statement does not update any rows. If it actually updates a row, then I get this error: WARNING: plpgsql: ERROR during compile of fn_notify near line 5 ERROR: syntax error at or near "" What gives? Must I use a trigger to get around this? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Historical logging of pg_stat_activity ?
hello everyone, Yes, yet agian i have a rather crazy idea, go with me on this and hopefully it will make a bit more sense than normal :) Currently, i have a rather extensive application that gets a lot of queries, i was wanting to track the general 'usage' pattern overtime, but of course, that would require a sort of 'historical view' of pg_stat_activity. If there was such a thing, then it would let me isolate hotspots or queries that were often used and allow me to focus on them first for speed benefits, as well as allowing me to produce some nifty histograms :) So, the long and short, is there a pg_stat table that contains the most 'used' queries and also (i know i am shooting for the moon here) execution time ? I know i -could- log_timestamp and log_statement and then parse out (using perl) the most called ones for each day and then sort them all and ... but thats a bit of a pain if there is already such a thing in existence. regards Stef ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
