Re: [SQL] LEAST and GREATEST functions?
Tom Lane wrote: create function greatest(anyelement, anyelement) returns anyelement as 'select case when $1>$2 then $1 else $2 end' language sql; Any chance of this making it into 7.4's contrib? Maybe with enough documentation to make it a tutorial for PostgreSQL's user functions? You do have to create several greatest() functions for different numbers of arguments, but not one for each datatype you want to handle. Insignificant, compared with the flexiblity. I have not seen enough requests for a native LEAST/GREATEST implementation to make me think we need to do more than this... certainly I'd rather spend development effort on general facilities like polymorphism and inlining than on creating one-use facilities like built-in LEAST/GREATEST. Nice. It would speed up our current functions too. Thanks, developers, esp. Tom and Joe for this! Wow, less reasons to use VIEWs when CREATE FUNCTION can be just as fast, but more flexible. Can't wait, IMHO, the advocacy people can and should be promoting this(functions returning sets, and how it can be used) as a killer feature for 7.3 and 7.4. I know I was pretty happy to discover that gem lurking in the documentation in 7.3. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 2:30pm up 188 days, 5:35, 4 users, load average: 5.04, 5.15, 5.16 pgp0.pgp Description: PGP signature
Re: [SQL] Getting all rows even if not a member of any groups
Dear All, thank you for your help, it was really efficient. I'll get by with it now. Thanks. -- Csaba - Original Message - From: "Bruno Wolff III" <[EMAIL PROTECTED]> To: "Együd Csaba" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, June 27, 2003 5:10 PM Subject: Re: [SQL] Getting all rows even if not a member of any groups > On Fri, Jun 27, 2003 at 16:18:10 +0200, > Együd Csaba <[EMAIL PROTECTED]> wrote: > > > > This is absolutelly what I want, but I can't understand how it is working. > > Where can I find a descriptive (tale-like, for kids ... :) ) documentation > > about using joins? > > If you look at the documentation for the select command and page down a > bit there is a description of join syntax. Note that in 7.4 using > the explicit join syntax won't force join order. (This really only > affects cross joins and inner joins; left and right joins normally > can't be reordered.) > > You need a left join to pick up products that aren't in any group. > The parenthesis changed the join order so that group names were attached > to group ids before group ids were joined to products. This can have > performance implications. I think that this is probably the faster > way, but the other option would to have been to make the second join > a left join as well. > > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Postgres - Delphi Application
Both link work nicely, thanks. I don't know what could be the problem with the others. But it's not important by now Thank you. -- Csaba - Original Message - From: "Tomasz Myrta" <[EMAIL PROTECTED]> To: "Együd Csaba" <[EMAIL PROTECTED]> Sent: Wednesday, July 02, 2003 12:06 PM Subject: Re: [SQL] Postgres - Delphi Application > Dnia 2003-07-02 11:58, Użytkownik Együd Csaba napisał: > > Dear Tomasz, > > I'm very sorry, because the download simply won't work. It does not do > > anything. > > I don't know what I'm doing wrong. > > > > Is there an other place on the web where I can download it from? > > > > (Sorry for disturbing!) > > Thanks, > > I can't help you, because I've never had any problems when downloading files > from sourceforge project. > > Anyway - try one of these links: > http://heanet.dl.sourceforge.net/sourceforge/zeoslib/zeosdbo-6.0.10-gamma.zi p > http://heanet.dl.sourceforge.net/sourceforge/zeoslib/zeosdbo-5.4.1-stable.zi p > > Currently I use 5.4.1, because of many changes between 5.4.x and 6.x If your > project is a new one - try 6.x. Zeoslib team said, that they won't work on > 5.4.x branch anymore. > > Tomasz > > > > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 2003. 06. 18. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Weird order problem (ignoring whitespaces?)
Hello, I used to run a program on a box with postgres 7.1 There was data like: 'abc 234' 'abc 1234' (not the extra blank before 234) so after a select * from x order by field i got 'abc 234' 'abc 1234' but since this program runs on a 7.3 postgres version i get. 'abc 1234' 'abc 234' it seems that postgres ignores the blank. What can i do to get it work again? Thanx, Heiko Irrgang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Weird order problem (ignoring whitespaces?)
What locales are the two databases initdb'd to? On Wed, 2 Jul 2003 [EMAIL PROTECTED] wrote: > Hello, > > I used to run a program on a box with postgres 7.1 > There was data like: > 'abc 234' > 'abc 1234' > > (not the extra blank before 234) > > so after a select * from x order by field i got > 'abc 234' > 'abc 1234' > > but since this program runs on a 7.3 postgres version > i get. > > 'abc 1234' > 'abc 234' > > it seems that postgres ignores the blank. > > What can i do to get it work again? > > Thanx, > Heiko Irrgang > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SEQUENCE and PRIMARY KEY
Hi,
many clients like webmin and openoffice makes an
INSERT NULL if i give no value for a field because it is a sequence.
Is there a better way to solve this problem as using a trigger ?
CREATE TABLE test (
id integer PRIMARY KEY
);
CREATE SEQUENCE test_seq_id MINVALUE 0;
CREATE OR REPLACE FUNCTION test_fnc_id_seq() RETURNS TRIGGER AS
'
begin
NEW.id := nextval(''test_seq_id'');
RETURN NEW;
end;
'
LANGUAGE 'plpgsql';
CREATE TRIGGER test_trg_ins BEFORE INSERT ON test FOR EACH ROW
EXECUTE PROCEDURE test_fnc_id_seq();
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SEQUENCE and PRIMARY KEY
On Wed, 2003-07-02 at 11:30, Ralf Werny wrote: > Hi, > many clients like webmin and openoffice makes an > INSERT NULL if i give no value for a field because it is a sequence. > Is there a better way to solve this problem as using a trigger ? A trigger (maybe a rule) is the way to go about this. The alternative is to teach the bad clients to use DEFAULT rather than NULL when they expect the GENERATOR to create the value for them. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] LEAST and GREATEST functions?
Ang Chin Han wrote: Tom Lane wrote: create function greatest(anyelement, anyelement) returns anyelement as 'select case when $1>$2 then $1 else $2 end' language sql; Any chance of this making it into 7.4's contrib? Maybe with enough documentation to make it a tutorial for PostgreSQL's user functions? > >> You do have to create several greatest() functions for different >> numbers of arguments, but not one for each datatype you want to >> handle. > > Insignificant, compared with the flexiblity. I don't think it's worth putting in contrib, but for the archives: create or replace function make_greatest() returns text as ' declare v_args int := 32; v_first text := ''create or replace function greatest(anyelement, anyelement) returns anyelement as select case when $1 > $2 then $1 else $2 end language sql''; v_part1 text := ''create or replace function greatest(anyelement''; v_part2 text := '') returns anyelement as select greatest($1, greatest($2''; v_part3 text := '')) language sql''; v_sql text; begin execute v_first; for i in 3 .. v_args loop v_sql := v_part1; for j in 2 .. i loop v_sql := v_sql || '',anyelement''; end loop; v_sql := v_sql || v_part2; for j in 3 .. i loop v_sql := v_sql || '',$'' || j::text; end loop; v_sql := v_sql || v_part3; execute v_sql; end loop; return ''OK''; end; ' language 'plpgsql'; select make_greatest(); Now you should have 31 "greatest" functions, accepting from 2 to 32 arguments. *Not* heavily tested, but seemed to work for me. regression=# select greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2); greatest -- 1234 (1 row) I'll leave "least()" as an exercise for the reader ;-) HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LEAST and GREATEST functions?
Ang, > Any chance of this making it into 7.4's contrib? Maybe with enough > documentation to make it a tutorial for PostgreSQL's user functions? Er, no. Feature freeze was Tuesday. And you haven't submitted a patch yet. > Wow, less reasons to use VIEWs when CREATE FUNCTION can be just as fast, > but more flexible. Can't wait, IMHO, the advocacy people can and should > be promoting this(functions returning sets, and how it can be used) as a > killer feature for 7.3 and 7.4. I know I was pretty happy to discover > that gem lurking in the documentation in 7.3. Actually, we did ... that was one of 3-4 "killer features" for 7.3 -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plpgsql - how can I use TG_OP ?
Hello *, I have a litlle problem writing a plpgsql trigger function. I am in need to get some interpretaion of the TG_OP within an IF clause to decide with action will be taken. I tried it that way: IF (TG_OP=DELETE AND check_count > 1) THEN UPDATE tipp_team SET team_count = (select count(*) from tipp_team_members where member_team_id=OLD.member_team_id) WHERE team_id=OLD.member_team_id; END IF; and I got the following error: tippspiel2003=# delete from tipp_team_members where member_team_id=2 and member_user_id=27; WARNING: Error occurred while executing PL/pgSQL function update_team_member_count WARNING: line 13 at if ERROR: Attribute "delete" not found tippspiel2003=# What am I making wrong ? best regards -Andreas Schmitz -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plpgsql - how can I use TG_OP ?
On Wednesday 02 July 2003 18:49, Andreas Schmitz wrote: > Hello *, > > I have a litlle problem writing a plpgsql trigger function. I am in need to > get some interpretaion of the TG_OP within an IF clause to decide with > action will be taken. I tried it that way: > > IF (TG_OP=DELETE AND check_count > 1) THEN > (...) > What am I making wrong ? Not quoting? Try: IF (TG_OP=''DELETE'' AND check_count > 1) THEN Ian Barwick [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])
[SQL] Timeout while waiting for a LOCK ...
Simple (I think) question ... is there a way of having an application attempt to acquire a LOCK on a table *without* it blocking? Right now, if you try to LOCK a table that another process has LOCKed, it will hang indefinitely waiting for the other LOCK to drop ... is there a way of setting a 'timer' so that if it doesn't acquire a LOCK in n secs, it just fails and reports it back to the application? Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Timeout while waiting for a LOCK ...
Yep, we get asked to do that quite often. Use statement_timeout before the LOCK command. If the timeout happens, the LOCK, and hence transaction will abort. --- The Hermit Hacker wrote: > > Simple (I think) question ... is there a way of having an application > attempt to acquire a LOCK on a table *without* it blocking? Right now, if > you try to LOCK a table that another process has LOCKed, it will hang > indefinitely waiting for the other LOCK to drop ... is there a way of > setting a 'timer' so that if it doesn't acquire a LOCK in n secs, it just > fails and reports it back to the application? > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy > Systems Administrator @ hub.org > primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] LEAST and GREATEST functions?
It's good to hear this kind of discussion going on!
I solved my problem (for now) by creating a bunch of
overloaded LEAST and GREATEST functions, one for each
datatype. They only take two parameters, but that's
fine for what we're doing.
However, I ran into another, unrelated problem. I
created the LEAST and GREATEST functions as described
above, but when I tried to perform an update statement
comparing two timestamps, I ran into a problem:
UPDATE foo
SET my_timestamp_field =
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'-MM-DD HH24:MI:SS'))
WHERE ...
My LEAST function would not work because
my_timestamp_field has a datatype of "timestamp
without time zone", and the TO_TIMESTAMP(...) creates
a "timestamp *with* time zone". I could not find
anything in the documentation about this behavior. I
am running v7.3.2.
All help is appreciated, and please keep up the
discussion about the ability to create functions with
variable number of arguments (LEAST, GREATEST, etc.).
I could see the potential for wanting to write these
in the future.
Cheers,
-Stefan
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
---(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] need some help with a delete statement
Thanks for all your help, Scott. A friend of mine whipped out this script which runs a lot faster than trying to use the original query I posted. -M@ begin; create temporary table cart_temp as select distinct a.cart_id, a.cart_cookie from v_carts a, v_cart_contents b where a.cart_id = b.cart_id; delete from v_carts; insert into v_carts (cart_id, cart_cookie) select cart_id, cart_cookie from cart_temp order by cart_id; drop table cart_temp; commit; vacuum analyze verbose v_carts; reindex table v_carts; ---(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] Inquiry From Form [pgsql]
Hi Chris, You need to start a transaction explicitly: Session 1: buns=# begin; BEGIN buns=# create table test_table (col1 int); CREATE TABLE Session 2: buns=# \d test_table Did not find any relation named "test_table". Session 1: buns=# commit; COMMIT Session 2: buns=# \d test_table Table "public.test_table" Column | Type | Modifiers +-+--- col1 | integer | HTH. David -Original Message- From: Chris Schneider [mailto:[EMAIL PROTECTED] Sent: Thursday, 3 July 2003 05:26 To: [EMAIL PROTECTED] Subject: [SQL] Inquiry From Form [pgsql] I know this is basic, but couldn\'t find and in a hurry to know the answer. When interfacing with PostgreSQL through PSQL, it appears that DML statements are auto-commited, that is, a change I make in one session is seen from another without the original session issueing a COMMIT. Is this a result of PSQL interface and if so, can it be turned off. Is PostgreSQL transactional in the sense that I can issue several DMLs and then ROLLBACK. If so, how. Thanks and sorry for the newbie question. ---(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 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inquiry From Form [pgsql]
I know this is basic, but couldn\'t find and in a hurry to know the answer. When interfacing with PostgreSQL through PSQL, it appears that DML statements are auto-commited, that is, a change I make in one session is seen from another without the original session issueing a COMMIT. Is this a result of PSQL interface and if so, can it be turned off. Is PostgreSQL transactional in the sense that I can issue several DMLs and then ROLLBACK. If so, how. Thanks and sorry for the newbie question. ---(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] LEAST and GREATEST functions?
Stefan,
> My LEAST function would not work because
> my_timestamp_field has a datatype of "timestamp
> without time zone", and the TO_TIMESTAMP(...) creates
> a "timestamp *with* time zone". I could not find
> anything in the documentation about this behavior. I
> am running v7.3.2.
Just do a:
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'-MM-DD HH24:MI:SS')::TIMESTAMP WITHOUT TIME ZONE)
with and without time zone are effectively seperate data types with easy
casting.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
