[SQL] SQL Date help
Hi, I am a newbie to SQL and postgresql too. please guide me in the following... 1.How i can create date while i am inserting into the table ,in which the field is date data type.(date should not be a current date and its should be created as in java date(int,int,int) 2. How i can compare the date field. For example , how i can get the all the records between to two dates please help in this regards i thank you regards waheed _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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
Calling Java from psql (was Re: [SQL] requesting help)
On Mon, Mar 26, 2001 at 09:53:52AM +0200, Poul L. Christiansen allegedly wrote: > Log into PostgreSQL using psql and run the help command: > "\h create trigger". > > See also: > http://www.postgresql.org/users-lounge/docs/6.5/postgres/sql-createtrigger.htm > > HTH, > Poul L. Christiansen > > On Sun, 25 Mar 2001, [iso-8859-1] Mohamed ebrahim wrote: > > > Hi > > I am an user of postgresql. I want to do some > > manipulation on table and i want to do some java stuff > > after inserting a row into table(i.e like sending > > email). I want to fire the trigger to do this after > > the insertion took place. I know how to insert into > > the table but i want to know that it is possible to > > java stuff after inserting rows each time. Please help > > me in this stuff. I will be thankful to you. > > > > Thank you > > Ebrahim Has anybody ever tried calling Java code from a pgsql trigger written in C? Shouldn't this be possible using JNI? I'm not exactly a Java expert myself, but this is the way PHP allows you to call Java code from your PHP scripts. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(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] SQL Date help
On Mon, Mar 26, 2001 at 12:24:07PM +0400, waheed_rahuman wrote:
>
>
> Hi,
>
> I am a newbie to SQL and postgresql too.
> please guide me in the following...
Any problem with documentation?
> 1.How i can create date while i am inserting into the table ,in which the
> field is date data type.(date should not be a current date and its should be
> created
> as in java date(int,int,int)
INSERT INTO xxx VALUES (now());
INSERT INTO xxx VALUES ('2000-01-01');
> 2. How i can compare the date field.
via standard operators, en example '<' '>' '=' 'between' ..etc.
> For example ,
> how i can get the all the records between to two dates
SELECT WHERE col BETWEEN '2000-01-01' AND '2001-01-01';
Please(!), see
http://www.postgresql.org/users-lounge/docs/7.0/user/datatype.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators2373.htm
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Functions and Triggers
Hello there! here is what i want to realize: a trigger, that puts a now() in the last_updated field, on any update of that table. i read the manual but i wasnt able to make a working function. what is the return value here? is there any or is it void? has somebody a example for me that is similary to my problem? thank you alot! Greetings Norbert -- Norbert Schollum --- equinux Aktiengesellschaft Informationstechnologien Gabelsbergerstr. 30 80333 München - Germany Tel. 089/520465-0 Fax. 089/520465-299 mailto:[EMAIL PROTECTED] http://www.equinux.de ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Still don't know how to build this string ?
Hello there I have still the same problem. Any help would really be appreciated ! Thanks ... jr Is it possible (and I think it is) to do the following : I have a table with diameters and types. I need to build a comma separated string. typ diam 01800 01840 01870 011120 02760 02780 02800 02900 031200 031234 031352 select diam from zylinder where typ='01' should produce the string "800,840,870,1120" PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Still don't know how to build this string ?
From: <[EMAIL PROTECTED]> > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 [snip] > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" Try the following as a starting point: CREATE FUNCTION comma_join(text,text) RETURNS text AS ' BEGIN IF $1>\'\' AND $2>\'\' THEN RETURN $1 || \',\' || $2; ELSE RETURN $1 || $2; END IF; END; ' LANGUAGE 'plpgsql'; CREATE AGGREGATE joinall ( sfunc = comma_join, basetype = text, stype = text, initcond = '' ); \d dia Table "dia" Attribute | Type | Modifier ---+-+-- typ | integer | diam | integer | SELECT typ,joinall(diam::text) FROM dia GROUP BY typ; typ | joinall -+ 1 | 800,840,870,1120 2 | 760,800,900,1200,1234,1352 (2 rows) Note the explicit cast of diam into text. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Functions and Triggers
From: "Norbert Schollum" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 26, 2001 10:24 AM Subject: [SQL] Functions and Triggers > Hello there! > > here is what i want to realize: > > a trigger, that puts a now() in the last_updated field, on any update of > that table. > > i read the manual but i wasnt able to make a working function. > what is the return value here? is there any or is it void? "opaque" - a special value for triggers. > has somebody a example for me that is similary to my problem? Yep - see the postgres notes from techdocs.postgresql.org - I've got an example there of exactly this (it's in the "automating" chapter - sorry, forget the precise URL) Also have a look at the Cookbook (linked to from same place) which might well have more examples. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Help
Hi, Thanks for your valuable information. I tried the cron. i typed cron -e and entereed into the input area. but i don't know how to save the cron file. I pressed ctrl+z and came out from cron. but i edit the cron file i found nothing on it.(i.e using pico filename.) Please tell me some description how to save the file in cron and to achive this. I will be thankful to you. Ebrahim > I am a user postgresql. I want to update a table > automatically when we reach monthend. i.e i want to > update some table on 31 of every month automatically > without any user attention. I want to know how to do > this. If anyone knows how to do this please mail me. >i > will be ever thankfull to him. >Joe wrote: > >Probably the easiest way to do this is to write a >script and run it >from >cron. For example, if your update query is in a file >called >$HOME/bin/monthend.sql: > >insert into mymonthendtable(f1, f2, f3) >values(123,'03/31/2001',12345.67); > >your script (call it $HOME/bin/monthend.sh) might >look like: > >#!/bin/sh >psql -U postgres mydatabasename < >$HOME/bin/monthend.sql > >then run (see "man 5 crontab" for more on cron) >crontab -e > >and add an entry like > ># run at 2:15 AM on the 30th of every month >15 2 30 * * $HOME/bin/monthend.sh > >Hope this helps, > >Joe > __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Still don't know how to build this string ?
The following function will convert a given list into a comma delimited string (the argument should be the typ): CREATE FUNCTION dima_list(int4) RETURNS text AS ' DECLARE recrecord; list text; BEGIN list := ; FOR rec IN SELECT diam FROM zylinder WHERE typ = $1 list := list || rec.diam || ''; '' END LOOP; RETURN list; END; ' LANGUAGE 'plpgsql'; Note that PL/PGSQL must be installed first, which can be done by typing createlang plpgsql at a shell prompt as a postgres super user. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 26 Mar 2001 [EMAIL PROTECTED] wrote: > Hello there > > I have still the same problem. Any help would really be appreciated ! > Thanks ... jr > > Is it possible (and I think it is) to do the following : > > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 > 01840 > 01870 > 011120 > 02760 > 02780 > 02800 > 02900 > 031200 > 031234 > 031352 > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" > > > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] is it me or trigger side effects
Hi all,
I have written a trigger to update a table called categories whenever
a tuple is either deleted or inserted in tables articles, media, and links.
It works fine for inserts but "pukes" on deletes :). I am using two auxiliary
addcount(varchar) and delecount (varchar)
I am getting the following error on deletes:
record new is unassigned yet .
below is my code.
drop function addCount(varchar);
create function AddCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt +1;
cond:=substr(cat_id,1,cnt);
update categories
set items= items+1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select addCount('KDA');
drop function delCount(varchar);
create function delCount(varchar) returns integer as '
declare
cat_id alias for $1;
len integer;
cnt integer;
cond varchar;
begin
len:= length(cat_id);
cnt:=0;
for i in 1 .. len loop
cnt:=cnt
+1;
cond:=substr(cat_id,1,cnt);
update categories
set items= items-1 where id like cond;
end loop;
return cnt;
end;
' language 'plpgsql';
select delCount('KDA');
drop trigger trigger_update_articles on articles;
drop trigger trigger_update_links on links;
drop trigger trigger_update_media on media;
drop function updateCat();
create function updateCat() returns opaque as '
declare
rec record;
rename new to cat;
rename old to ct;
maxlen integer;
begin
if tg_op = ''INSERT'' and cat.category
is null then
raise exception
''You are missing entry for category field'';
else
if cat.display
then
maxlen:= addCount(cat.category);
return cat;
else
return cat;
end if;
end if;
if tg_op = ''DELETE''then
maxlen:= delCount(ct.category);
return ct;
end if;
end;
' language 'plpgsql';
create trigger trigger_update_articles before insert or delete
on articles for each row execute procedure updateCat();
create trigger trigger_update_media before insert or delete
on media for each row execute procedure updateCat();
create trigger trigger_update_links before insert or delete
on links for each row execute procedure updateCat();
begin:vcard
n:Hashmi;Najm
x-mozilla-html:FALSE
org:Mondo-Live.com;www.flipr.com
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
x-mozilla-cpt:;6144
fn:Najm Hashmi
end:vcard
---(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] Functions and Triggers
CREATE FUNCTION lastupdated() RETURNS opaque AS ' begin new.last_updated := CURRENT_TIMESTAMP; return new; end; ' LANGUAGE 'plpgsql'; CREATE TRIGGER trigname BEFORE INSERT OR UPDATE on tblname FOR EACH ROW EXECUTE PROCEDURE lastupdated(); Note: you could use now() instead of CURRENT_TIMESTAMP Note2: on a BEFORE trigger you must return new or old. If you return null the statement will be aborted. (?) -Cedar On Mon, 26 Mar 2001, Norbert Schollum wrote: > Hello there! > > here is what i want to realize: > > a trigger, that puts a now() in the last_updated field, on any update of > that table. > > i read the manual but i wasnt able to make a working function. > what is the return value here? is there any or is it void? > > has somebody a example for me that is similary to my problem? > > thank you alot! > > Greetings > Norbert > > > -- > > Norbert Schollum > --- > equinux Aktiengesellschaft > Informationstechnologien > Gabelsbergerstr. 30 > 80333 München - Germany > Tel. 089/520465-0 > Fax. 089/520465-299 > mailto:[EMAIL PROTECTED] > http://www.equinux.de > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Help
> Thanks for your valuable information. I tried the > cron. i typed >cron -e > and entereed into the input area. but i don't know how > to save the cron file. I pressed ctrl+z and came out > from cron. but i edit the cron file i found nothing on > it.(i.e using pico filename.) Please tell me some > description how to save the file in cron and to achive > this. I will be thankful to you. > Instead of "ctrl+z", press ":wq" (colon for command mode, w for write, q for quit). This assumes that vi is your default editor. Joe ---(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] RE: Still don't know how to build this string ?
Still learning this stuff, so please be gentle...
jeff=# select * from test_it;
typ | diam
-+--
01 | 800
01 | 840
01 | 870
01 | 1120
02 | 760
02 | 780
02 | 800
02 | 900
03 | 1200
03 | 1234
03 | 1352
(11 rows)
jeff=# \! cat test_it
drop function test_it_too(text);
create function test_it_too(text)
returns text as '
declare
typ2 alias for $1;
rec record;
string text:= ;
begin
for rec in select * from test_it where typ = typ2 loop
string := string || rec.diam || '','';
end loop;
string := substr(string, 1, length(string)-1);
return string;
end;
' language 'plpgsql';
jeff=# \i test_it
DROP
CREATE
jeff=# select test_it_too('01');
test_it_too
--
800,840,870,1120
(1 row)
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, March 26, 2001 4:13 AM
> To: [EMAIL PROTECTED]
> Subject: Still don't know how to build this string ?
>
> Hello there
>
> I have still the same problem. Any help would really be appreciated !
> Thanks ... jr
>
> Is it possible (and I think it is) to do the following :
>
> I have a table with diameters and types. I need to build a comma separated
> string.
>
> typ diam
> 01800
> 01840
> 01870
> 011120
> 02760
> 02780
> 02800
> 02900
> 031200
> 031234
> 031352
>
> select diam from zylinder where typ='01'
>
> should produce the string "800,840,870,1120"
>
>
>
>
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
>
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
>
>
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
---(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] is it me or trigger side effects
> create function updateCat() returns opaque as ' > declare > rec record; > rename new to cat; > rename old to ct; > maxlen integer; > > begin > if tg_op = ''INSERT'' and cat.category is null then > raise exception ''You are missing entry for category field''; > > else > if cat.display then >maxlen:= addCount(cat.category); >return cat; > else >return cat; > end if; > end if; > if tg_op = ''DELETE''then > maxlen:= delCount(ct.category); > return ct; > end if; > > end; > ' language 'plpgsql'; I think you want your if blocks to be more like: if tg_op = ''INSERT'' if cat.category is null (raise exception) else (do insert stuff) end if end if if tg_op = ''DELETE'' (do delete stuff) end if I think you're currently going to get into the insert stuff on both inserts where it isn't null and deletes (since in both cases the and will be false). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] paging
I have a simple script to automate sql statements: q " select * from tablename" where q is an alias to echo "$@ ; " | psql imdb Turning on security breaks this script since is need s password. the new one works a before: q "select *..." passwd where q is now an expect script: #!/usr/bin/expect set query [lindex $argv 0] set query [lindex $argv 1] spawn psql imdb -U hook -n expect "Password: "; send "$passwd\n "; expect "imdb=# "; send "$query;\r"; expect "imdb=# "; send "\q;\r"; The problem is that long query returns invoke the paging option so you can scroll a page a time. Paging doesn't work properly within the script and even if it did , I would have to disable it in order to redirect the output to a file or a pipe. I can't find any info in the docs. Can some one point me in the right direction thanks ---(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] paging
A. Van Hook writes: > #!/usr/bin/expect > > set query [lindex $argv 0] > set query [lindex $argv 1] > > spawn psql imdb -U hook -n > expect "Password: "; > send "$passwd\n "; > expect "imdb=# "; > send "$query;\r"; > expect "imdb=# "; > send "\q;\r"; > > The problem is that long query returns invoke the paging option so you > can scroll a page a time. Paging doesn't work properly within the > script and even if it did , I would have to disable it in order to > redirect the output to a file or a pipe. Try psql -P pager to turn the pager off. Also you can probably use export PGPASSWORD=xyz psql ... to skip the password probe. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
Mathijs Brands writes: > Has anybody ever tried calling Java code from a pgsql trigger written > in C? Shouldn't this be possible using JNI? I have, and given the current Java implementations it's a desaster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Calling Java from psql (was Re: [SQL] requesting help)
On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote: > Mathijs Brands writes: > > > Has anybody ever tried calling Java code from a pgsql trigger written > > in C? Shouldn't this be possible using JNI? > > I have, and given the current Java implementations it's a desaster. That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to work stable either :( Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Help
> Instead of "ctrl+z", press ":wq" (colon for command mode, w for write, q for > quit). If you are still mystified, "ctrl+z" stops a process running on a terminal, so it's likely that your vi is still running idle. Type 'fg' on the same terminal to bring it back. For more details on job control, see 'info sh' and search for 'jobs'. > This assumes that vi is your default editor. Which you can change by setting your EDITOR environment variable. For example, if your shell is bash, export EDITOR="emacs -nw" will do it. --Gene ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] pl/pgsql and returning rows
Here is the senario... I have a table defined as create table details ( field1 field2 . . . ); and a function: create function get_details(int4) returns details as ' declare ret details%ROWTYPE; site_recrecord; cntct contacts%ROWTYPE; begin select into site_rec * sites_table where id = $1 limit 1; select into cntct * from contacts where id = site_rec.contact; -- and then i populate rows of ret. ret.name := cntct.name; ret.ip := site_rec.ip; . . . return ret; end; ' language 'plpgsql'; now the problem is when is when I do a: SELECT get_details(55); all i get is a single oid-looking return value: get_details - 136295592 (1 row) How do i get at the actual information in the row? Is this type of function even possible? If not, is there a different manner in which i should approach this task? Thanx in advance. -Wade ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Function Vanished
Tom, Jan, I've already contacted PGSQL Inc. support about this, but I thought that you might have some immediate insight. I'm using 7.1 Beta 3, which has been pretty stable up until now. This morning, I went to export a function I spent 5 hours debugging on on Friday to text so that I could have a copy of the final version. To my horror, the function was GONE from the system catalog (pg_proc). I have not run VACUUM on the database anytime recently ... is there any possibility that my function is still present as a "deleted" row? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Implicit/Explicit casting of the unknown type
Hi, I am new to this list. I have browsed the list archives and tried a search, but haven't found anything about my problem. I am porting several applications to PostgreSQL and have come across a number of instances where a string literal does not work without an explicit cast to some character based datatype. Two examples are given below: one where the string literal is in the column list, and one where it is in a WHERE clause. Are there bugs in the area of determining a type for the unknown type? Or is explicit casting of string literals to a known type generally required by PostgreSQL? TIA, Stephen Jackson Home: [EMAIL PROTECTED] www.panting-deer.org.uk Work: [EMAIL PROTECTED]www.looksystems.co.uk [begin examples] version - PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) DROP TABLE strange DROP CREATE TABLE strange ( record_id integer ) CREATE INSERT INTO strange ( record_id ) VALUES ( 10 ) INSERT 5174249 1 SELECT DISTINCT record_id, 123 FROM strange WHERE record_id < 100 record_id | ?column? ---+-- 10 | 123 (1 row) SELECT DISTINCT record_id, 'Hello' FROM strange WHERE record_id < 100 ERROR: Unable to identify an ordering operator '<' for type 'unknown' Use an explicit ordering operator or modify the query SELECT record_id, 'Hello' FROM strange WHERE record_id < 100 record_id | ?column? ---+-- 10 | Hello (1 row) SELECT COUNT(*) FROM strange WHERE 'TR' || 'UE' = 'TRUE' count --- 1 (1 row) SELECT COUNT(*) FROM strange WHERE 'TRUE' = 'TRUE' ERROR: Unable to identify an operator '=' for types 'unknown' and 'unknown' You will have to retype this query using an explicit cast [end examples] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help
> > Thanks for your valuable information. I tried the > > cron. i typed > >cron -e > > and entereed into the input area. but i don't know how > > to save the cron file. I pressed ctrl+z and came out > > from cron. but i edit the cron file i found nothing on > > it.(i.e using pico filename.) Please tell me some > > description how to save the file in cron and to achive > > this. I will be thankful to you. > > > > Instead of "ctrl+z", press ":wq" (colon for command mode, w for write, q for > quit). This assumes that vi is your default editor. > And if you didn't know, you can set the VISUAL environment variable to change your default editor. eg.. export VISUAL=pico You can put this in your .profile to make it a default. Personally I don't like line wrapping so I turn it off like this: export VISUAL='pico -w' You can also use the 'v' command in from 'less' to edit a file.. very handy.. -Cedar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] is it me or trigger side effects
On Mon, 26 Mar 2001, Stephan Szabo wrote: > > > create function updateCat() returns opaque as ' > > declare > > rec record; > > rename new to cat; > > rename old to ct; > > maxlen integer; > > > > begin > > if tg_op = ''INSERT'' and cat.category is null then > > raise exception ''You are missing entry for category field''; > > > > else > > if cat.display then > >maxlen:= addCount(cat.category); > >return cat; > > else > >return cat; > > end if; > > end if; > > if tg_op = ''DELETE''then > > maxlen:= delCount(ct.category); > > return ct; > > end if; > > > > end; > > ' language 'plpgsql'; > > I think you want your if blocks to be more like: > if tg_op = ''INSERT'' > if cat.category is null > (raise exception) > else > (do insert stuff) > end if > end if > if tg_op = ''DELETE'' > (do delete stuff) > end if > > I think you're currently going to get into the > insert stuff on both inserts where it isn't > null and deletes (since in both cases the > and will be false). You probably want to handle UPDATEs as well.. make sure you plan for this too. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Function Vanished
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I'm using 7.1 Beta 3, which has been pretty stable up until now. This > morning, I went to export a function I spent 5 hours debugging on on > Friday to text so that I could have a copy of the final version. To my > horror, the function was GONE from the system catalog (pg_proc). Ick. Were you maybe working on it inside a transaction that you forgot to commit? > I have > not run VACUUM on the database anytime recently ... is there any > possibility that my function is still present as a "deleted" row? Should still be there in the table, if you haven't vacuumed. Getting it out again is another story though. If it was a small enough function, good ol' "strings" would do to extract the function body, which is probably all that you really need. But if it's more than a couple K then the text will be compressed and difficult to spot or extract. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Function Vanished
Tom, > Ick. Were you maybe working on it inside a transaction that you > forgot > to commit? Nope. Friday was debugging work; the function had already been saved as a buggy version. I can even find the last buggy call to the function, on Friday, in the logs. I do have a copy of the buggy version, but if I can get the debugged version back ... > Should still be there in the table, if you haven't vacuumed. Getting > it out again is another story though. If it was a small enough > function, > good ol' "strings" would do to extract the function body, which is > probably all that you really need. But if it's more than a couple K > then the text will be compressed and difficult to spot or extract. Can you explain that? I think that the text may be short entough to be uncompressed. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. 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] serial type; race conditions
Hi, I'm using serial fields to generate IDs for almost all object in my database. I insert an empty row, get the CURRVAL() of the sequence and then update to that value. I had understood (and now, I can't find the reference to back this up) that serial is implemented in such a way that race conditions between DB connections can't happen. Is this true? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5 ---(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] Function Vanished
"Josh Berkus" <[EMAIL PROTECTED]> writes: >> Should still be there in the table, if you haven't vacuumed. Getting >> it out again is another story though. If it was a small enough >> function, >> good ol' "strings" would do to extract the function body, which is >> probably all that you really need. But if it's more than a couple K >> then the text will be compressed and difficult to spot or extract. > Can you explain that? I think that the text may be short entough to be > uncompressed. Try running strings(1) on the pg_proc table, which will be ... hmm ... $PGDATA/base/YOURDBOID/1255. Look in pg_database if you're not sure of the OID of the database you are using. If you have not vacuumed then the latest version of the row will be the one closest to the front of the last 8K page of the file (got that? ;-)) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] serial type; race conditions
> Hi, > > I'm using serial fields to generate IDs for almost all object in my > database. I insert an empty row, get the CURRVAL() of the sequence > and then update to that value. > > I had understood (and now, I can't find the reference to back this up) > that serial is implemented in such a way that race conditions between > DB connections can't happen. > > Is this true? Safe. See FAQ item. currval is for your backend only. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] ÁY¼gªº§t¸q
--- Tom Lane <[EMAIL PROTECTED]> wrote: > > I'm using 7.1 Beta 3, which has been pretty > stable up until now. This > > morning, I went to export a function I spent 5 > hours debugging on on > > Friday to text so that I could have a copy of the > final version. To my > > horror, the function was GONE from the system > catalog (pg_proc). > > Ick. Were you maybe working on it inside a > transaction that you forgot > to commit? ½Ð°Ý Ick ¬O¤°»ò·N«ä, ¬O¤°»ò¦rªºÁY¼g? S.F.Lee 2001-03-27 __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(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 Vanished
Hi would it work to do a pg_dump -d or a pgdumpall, then look through the dumped sql file? I do that to retrieve PL/pgSQL functions from the database when I've accidentally wiped or modified the source (not often, but it happens). Regards and best wishes, Justin Clift Tom Lane wrote: > > "Josh Berkus" <[EMAIL PROTECTED]> writes: > > I'm using 7.1 Beta 3, which has been pretty stable up until now. This > > morning, I went to export a function I spent 5 hours debugging on on > > Friday to text so that I could have a copy of the final version. To my > > horror, the function was GONE from the system catalog (pg_proc). > > Ick. Were you maybe working on it inside a transaction that you forgot > to commit? > > > I have > > not run VACUUM on the database anytime recently ... is there any > > possibility that my function is still present as a "deleted" row? > > Should still be there in the table, if you haven't vacuumed. Getting > it out again is another story though. If it was a small enough function, > good ol' "strings" would do to extract the function body, which is > probably all that you really need. But if it's more than a couple K > then the text will be compressed and difficult to spot or extract. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
