Re: [SQL] Getting row with id=max(id)
> A related question is: is there a way to time a query in psql, like the > client of MySQL does? use the explain commmand explain select * from foo; > > > ---(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 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] large going giving errors.
Hi I have a largeish join that is giving me an error.. here's the query SELECT o.name,o,contactname,o.contactphone,o.fax,o.emailaddy,o.freetelephone,o.address1,o.address2,uw.id,m.manufacturer,i.type,uw.model,uw.color,uw.size,uw.price FROM user_wantads AS uw,organisations AS o,users AS u ,itemtypes AS i,itemmanufacturers AS m,provinces AS p,cities AS ct,countries AS cy WHERE uw.owner = u.loginid AND u.belongsto = o.organisationid AND m.id=uw.manufacturer AND i.id=uw.itemtype AND o.provinceid=p.provinceid AND o.cityid=ct.cityid AND o.countryid=cy.countryid AND uw.id=9 and the error ERROR: copyObject: don't know how to copy 704 can anyone shed a little light please ? jeff. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [SQL] large going giving errors.
i actually found the problem, SELECT o.name,o,contactname should be SELECT o.name,o.contactname jeff On Wed, 13 Jun 2001, Robby Slaughter wrote: > What happens when you don't join quite so much? That is, take off the AND > blah blocks, > one by one? > -Robby > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] > Sent: Wednesday, June 13, 2001 3:38 PM > To: [EMAIL PROTECTED] > Subject: [SQL] large going giving errors. > > > Hi > > I have a largeish join that is giving me an error.. > > here's the query > > SELECT > o.name,o,contactname,o.contactphone,o.fax,o.emailaddy,o.freetelephone,o.addr > ess1,o.address2,uw.id,m.manufacturer,i.type,uw.model,uw.color,uw.size,uw.pri > ce > FROM user_wantads AS uw,organisations AS o,users AS u ,itemtypes AS > i,itemmanufacturers AS m,provinces AS p,cities AS ct,countries AS cy > WHERE uw.owner = u.loginid >AND u.belongsto = o.organisationid >AND m.id=uw.manufacturer >AND i.id=uw.itemtype >AND o.provinceid=p.provinceid >AND o.cityid=ct.cityid AND o.countryid=cy.countryid >AND uw.id=9 > > and the error > > ERROR: copyObject: don't know how to copy 704 > > can anyone shed a little light please ? > > jeff. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] constraints,
Hello folks, wondering how to display any constraints that a table may have, as well i know alter table add constraint works, but what's the syntax to remove one ? i assume /alter table remove constraint, but i can't find any examples. jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] how do i insert an empty string ?
On Tue, 15 Oct 2002, Peter Galbavy wrote:
> FAQ: A search yielded nothing explicit...
>
> I have an INSERT statement:
>
> INSERT INTO metadata (md5, origin, name, value)
> VALUES ('fd859f263bd0579935f2146a22d24f32', 'EXIF',
> 'UserComment', '')
>
> but this fails (using Perl DBI, DBD::Pg) because $dbh->quote() returns two
Since you are using DBI, why not bind the variables and be done with it?
this would become
$query = $db->prepare("INSERT INTO metadata (md5, origin, name, value)
VALUES (?, ?, ?, ?)");
$query->execute($md5, $origin, $name, $value);
This way you don't have to deal with double quoting and all that. (You can
also call bind_param, but I find it easier to just pass args into execute)
--
Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/
Ronald McDonald, with the help of cheese soup,
controls America from a secret volkswagon hidden in the past
---
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to specify the beginning of the month in Postgres SQL syntax?
Hello, I need to create a view in Postgres that has a where clause of the date < beginning of month. i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this? Thanks. ---(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] CREATE TYPE
On Nov 3, 2004, at 10:56 AM, Ameen - Etemady wrote: I like to do it like this: create table mytmp(name myvarchar(10,"en_US")); you can't unless you modify the parser. It has special cases to support varchar (and numeric) syntax. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style
On May 2, 2005, at 6:09 PM, Markus Schaber wrote: 1) is it possible to know Pg backend uptime with SQL queries? Or must I look at postmaster.pid file? or even something else? In contrib, there's a function caled backend_pid() defined in misc_utils.sql, it may be helpful for you. markus I wrote a small util called "pgtop" that does a top style listing on PG in terms of cpu. I also have one called pgiomonitor (that is on pgfoundry and mostly done) that does the same but shows you which tables are producing the most IO) http://postgresql.jefftrout.com/pgtop.pl it only runs on linux and it must run on the same box as pg itself. You may want to look at it and see if you can get anything good out of it. queries / second is tricky. You could look at my pgspy utility but it is in C. It can give you queries / second data -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [GENERAL] bug with if ... then ... clause in views
Emil Rachovsky wrote: While trying to create some views I stumbled on some problem with using the if-then clause. Here is a simple example : CREATE OR REPLACE VIEW public.SomeView as select d.id, if (true) then d.DocNumber endif from public.Z_Documents as d; I get the following error : syntax error at or near "then" at character 72 I don't have a clue what is going on here. Any suggestions? IF / THEN is not part of SQL. it is part of plpgsql. However you'll find that CASE can do the same thing. select d.id, case when true then d.docNumber else 'something else' end as blah, public.Z_documents as d ... -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Fetch the latest log for each product
Bernie Huang wrote: > > Hi, > > I bet people have asked this question several times, but oh well, please > do anwser again. Thanks. =) > > I have a product table and a log file. > > product_tb > --- > prod_id > prod_name > ... > > log_tb > - > log_id > prod_id > cust_id > transact_date > ... > > How do I fetch the latest log for each product according to transaction > date? > > - Bernie here's how i would do it, i guess. select p.prod_name, l.prod_id, max(l.transact_date) as last_transaction from log_tb l, prod_tb p where l.prod_id=p.prod_id group by p.prod_name, l.prod_id; i think that's what you want, at least... -- Jeff Hoffmann PropertyKey.com
[SQL] select
how would i select all rows where a boolean value is neither t nor f.. ? ie if someone inserted without setting the boolean tag. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] select
bignose=# create table test ( bignose(# val1int4, bignose(# val2boolean); CREATE bignose=# bignose=# insert into test (val1) values (56); INSERT 322762 1 bignose=# bignose=# select * from test; val1 | val2 --+-- 56 | (1 row) ta da :) On Sat, 23 Sep 2000, Indraneel Majumdar wrote: > Hi, > > AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0. > > \Indraneel > > On Sat, 23 Sep 2000, Jeff MacDonald wrote: > > > how would i select all rows where a boolean value is neither > > t nor f.. ? > > > > ie if someone inserted without setting the boolean tag. > > > > Jeff MacDonald, > > > > /. > # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # > # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # > # Centre for DNA Fingerprinting and Diagnostics, # > # Hyderabad, India - 500076 # > `/ > Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] select
bingo, thaks On Sat, 23 Sep 2000, Stephan Szabo wrote: > > I'd assume this would work: > select * from table where booleanfield is null; > > Stephan Szabo > [EMAIL PROTECTED] > > On Sat, 23 Sep 2000, Jeff MacDonald wrote: > > > how would i select all rows where a boolean value is neither > > t nor f.. ? > > > > ie if someone inserted without setting the boolean tag. > > Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose
[SQL] Re: OID Perfomance - Object-Relational databases
Hi Josh, I didn't get right tho this, because well.. you asked the list, i figured i'd give them a chance first. and they've answered the same as i would have.. > Because it's a very elegant solution to my database structure issues, > I'm using OID's extensively as referents and foriegn keys. However, I > wanted to see if others had previous experience in this (answer as many > as you like): > > 1. Is there a performance loss on searches and joins when I use the OID > as a liniking field as opposed to a SERIAL column? yes, because indexes are automatcally created on serials. > 2. Can I define my own index on the OIDs of a table? sure, create index oid_idx on table(oid); Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] Table Attribute Help
"Brian C. Doyle" wrote: > > Hello all, > > I am trying to find a query to retrive the attributes of a table as in \d > tablename but as a select command. Is this possible? run "psql -E" -- it will echo all of the sql queries that those commands use. -- Jeff Hoffmann PropertyKey.com
[SQL] substr
i noticed that substr behaves a bit different in pgsql than perl ie select foo from table where substr(foo,1,1) = 'X'; initially i thought it should be substr(foo,0,1) just wondering on the reasoning for this offset ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] substr
> Jeff MacDonald <[EMAIL PROTECTED]> writes: > > i noticed that substr behaves a bit different in pgsql than perl > > ie select foo from table where substr(foo,1,1) = 'X'; > > > just wondering on the reasoning for this offset ? > > Larry Wall and the SQL92 authors didn't talk to each other... bastards :) now we gotta decide who to kill, larry or the sql folk. i'd say the sql folk, 0 is just more inututive for computer counting. but that's just me.. :) anyway thanks. > > We are implementing SQL around here, not Perl, so we have to follow > the SQL spec's definition of substr(). > > regards, tom lane > Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] fetching rows
Nikolay Mijaylov wrote: > > Let say we have a select that returns 100 rows. > > I can fetch first 25 with simple sql: > > BEGIN WORK; > DECLARE liahona CURSOR FOR SELECT * FROM films; > FETCH [FORWARD] 25 IN liahona; > CLOSE liahona; > COMMIT WORK; > > but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or > can I skip first 25? you can't do that with a cursor, but you can use they mysql-ism called a limit clause. for example, to fetch rows 26-50 from that query, you'd do: select * from films limit 25,26; or select * from files limit 25 offset 26; -- Jeff Hoffmann PropertyKey.com
[SQL] substring ..
hi folks.. i want to do this to a datetime field.. select foo from table where substr(datefoo,1,11) = '2000-12-14'; it returns no results yet.. select substr(datefoo,1,11) does return some values that say 2000-12-14 any clues ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
[SQL] Use of RETURN in pl/pgsql function
My script is below. I thought (based on recent posts) that this use of RETURN is allowed, but when trying an insert to report_table, I get the following error: ERROR: control reaches end of trigger procedure without RETURN I have solved several problems in getting to this point, but have now run out of ideas. I would appreciate any pointers. jeffe@kiyoko=> uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 10:44:07 CDT 2000 jeffe@kiyoko=> psql -V psql (PostgreSQL) 7.0.0 Script: drop function mrr(); create function mrr() returns opaque as ' begin if NEW.billing_frequency = ''Monthly'' -- That's doubled single quotes (and below as well) then return NEW; else if NEW.billing_frequency = ''Yearly'' then NEW.rate := NEW.rate/12; NEW.rate_override := NEW.rate_override/12; return NEW; else if NEW.billing_frequency = ''Semi-Annual'' then NEW.rate := NEW.rate/6; NEW.rate_override := NEW.rate_override/6; return NEW; else if NEW.billing_frequency = ''Quarterly'' then NEW.rate := NEW.rate/3; NEW.rate_override := NEW.rate_override/3; return NEW; end if; end if; end if; end if; end; 'language 'plpgsql'; drop trigger mrr_set_trigger on report_table; create trigger mrr_set_trigger before insert on report_table for each row execute procedure mrr();
RE: [SQL] Use of RETURN in pl/pgsql function
I'll be hornswoggled: that works. I hadn't expected a problem, simply because I knew the range in advance: here is the distribution for the select that I was working with: extracts=# select billing_frequency, count (*) from report_table group by billing_frequency; billing_frequency | count ---+--- Monthly | 50431 Quarterly | 7742 Semi-Annual | 167 Yearly| 8573 (4 rows) Nevertheless, just adding an "...else return new;" did the trick. I don't understand the logic of this, but then, if I stopped to consider every mystery, I'd never get any work done:-). Thanks very much for your help (again). > -Original Message- > From: Tom Lane [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, February 07, 2001 12:48 PM > To: Jeff Eckermann > Cc: '[EMAIL PROTECTED]' > Subject: Re: [SQL] Use of RETURN in pl/pgsql function > > Jeff Eckermann <[EMAIL PROTECTED]> writes: > > I thought (based on recent posts) that this use of RETURN is allowed, > but > > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > Looks to me like you didn't cover the case where billing_frequency is > not any of the values you tested for. Maybe you just want to raise > an error in that case... > > regards, tom lane
[SQL] COPY isn't working right for me
I have a tab delimited file that I'm trying to import into an empty table. I've set the table up as follows: create table member ( member_id serial not null, fname varchar(25) not null, lname varchar(25) not null, member_since date not null, Primary Key (member_id) ); My member.txt file looks like this: Joe Smith 2000/01/14 Frank Jones 2000/06/21 MikeDavis 2000/09/24 Here's the copy command I use: COPY member FROM '/tmp/member.txt'; But I'm getting the following error: ERROR: copy: line 1, pg_atoi: error in "Joe": can't parse "Joe" I'm assuming this has to do with the member_id with type serial. How do I import into this without having to add the OID's to each of the rows in the text file? __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [SQL] pl/Perl
> > 1: can you call other stored procedures from within pl/Perl > No. darn. > > > 2: from within a pl/Perl script , can i do a select etc.. > >i'm assuming no, because you cannot use DBI.. but just wondering > >if there is a way.. > Not currently. darn. > > 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. > >when i tried to install pl/perl i get this.. > > > > cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ > > perl Makefile.pl > > make > Try using gmake instead of make (cd /usr/ports/devel/gmake, make) thanks ! works great. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
[SQL] pl/Perl
Hello Few questions about pl/perl as the docs on this are very sparse.. (i find that with our procedural language docs in general) 1: can you call other stored procedures from within pl/Perl 2: from within a pl/Perl script , can i do a select etc.. i'm assuming no, because you cannot use DBI.. but just wondering if there is a way.. 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. when i tried to install pl/perl i get this.. cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ perl Makefile.pl 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 any tips ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
[SQL] bug.. ?
A person recent pointed this out to me.. seems a bit funny, because limit 1 pretty much say's it't not gonna return multiple values. jeff > This doesn't work: > > CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM > i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC > LIMIT 1' LANGUAGE 'sql'; > > The result: > ERROR: function declared to return int4 returns multiple values in final > retrieve > > > I'm running 7.0.2. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
[SQL] Re: logging a psql script
On Wed, 21 Feb 2001, Ken Kline wrote: > Hello, >I would like my psql script to log everything that it does. > I set the following > > \set ECHO all > \o foo.txt > \qecho > > some sql, some ddl, etc... > > \o > > > But foo.txt only contains > > DROP > DROP > DROP > CREATE > CREATE > CREATE > On UNIX/UNIX-like machines, use the script(1) command (man script for details). Jeff -- Errors have occurred. We won't tell you where or why. Lazy programmers. -- Hacking haiku
[SQL] How do I use text script containing SQL?
I want to build my tables by placing all the sql statements in a file. What is the correct way to use this file with psql? Example: My text file has this in it: CREATE TABLE table1 ( table1_id serial, field1 char(5), PRIMARY KEY (table1_id) ); I want to be able to use the file to create my table. I've tried psql -d databasename -e < filename.txt but that doesn't work. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help with UPDATE syntax
Howdy, I am porting a bit of code from ORACLE to PostgreSQL 7.1 and am getting stuck on an update statment. I am NOT a SQL expert, merely a beginner swimming hard, so any help will be greatly appreciated. The specific query looks like this: begin transaction update user_group_map map set user_id = 4 where user_id = 9 not exists ( select * from user_group_map where user_id = 4 and group_id = map.group_id and role = map.role ) commit There are other updates taking place during the transaction, but this is the one for which I can't figure out the PostgreSQL equivalent. I've tried this: update user_group_map set user_id = 4 from user_group_map map where user_id = 9 and not exists ( select * from user_group_map ug2 where user_id = 4 and ug2.group_id = map.group_id and ug2.role = map.role); for the update replacement, but get an error: NOTICE: current transaction is aborted, queries ignored until end of transaction block As noted earlier, any guidance will be most appreciated. Thanks, Jeff. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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])
[SQL] RE: pl/pgsql and returning rows
As a workaround, you can insert your row into an existing table, then retrieve it from there later. I think you need to enumerate all of the fields, as in 'INSERT INTO table VALUES (ret.field1, ret.field2,...ret.fieldn);'. At least, I haven't succeeded any other way. Messy, but the best method available right now. > -Original Message- > From: Richard Huxton [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, March 27, 2001 2:27 AM > To: [EMAIL PROTECTED]; wade > Subject: Re: pl/pgsql and returning rows > > From: "wade" <[EMAIL PROTECTED]> > > > 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) > > Sorry - you can't return a row from a function at the present time (except > for trigger functions which are special) although I believe this is on the > todo list for a later 7.x release. > > Just from the top of my head, you might try a view with a select rule, > although I'm not completely clear what your objectives are. > > - Richard Huxton > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go 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])
[SQL] RE: counting distinct rows on more than one column
I don't think this will necessarily work: field1 | field2 aa | ab a | aab These are two distinct rows, so should be counted as two. The proposed method would count them as one. You can get around this problem by doing: count (distinct (a || x || b)) where x is some character not found in your data. > -Original Message- > From: Dirk Lutzebaeck [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, March 28, 2001 1:32 PM > To: Michael Fork > Cc: [EMAIL PROTECTED] > Subject: Re: counting distinct rows on more than one column > > Michael Fork writes: > > In 7.0.3, I believe the following would work: > > > > SELECT count(distinct(a || b)) FROM t; > > Great, this works! I don't quite get it why... > > Dirk > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: serial type; race conditions
Probably just me: but I don't see the point. Consider:
* User 1 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 2 commences insert transaction: grabs nextval(sequence),
max(foo)
* User 1 commits
* User 2 commits (insert has sequence value one higher than for User
1, but same value for max(foo) + 1), or
* If foo has a unique constraint, transaction 2 will roll back.
Either way, I don't see what has been gained. All of the messages I have
read on this subject conclude with the same point: choice is to:
* accept unique sequence with holes
* accept loss of concurrency (as in the example above).
Or am I just missing the point?
> -Original Message-
> From: Andrew Perrin [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, March 29, 2001 8:46 AM
> To: [EMAIL PROTECTED]
> Cc: PgSQL-SQL
> Subject: Re: serial type; race conditions
>
> I ditto what Bruce said - trying to get a true sequence without gaps is a
> losing battle. Why don't you, instead, use a serial column as the real
> sequence, and then a trigger that simply inserts max(foo) + 1 in a
> different column? Then when you need to know the column, do something
> like:
>
> SELECT number_i_care_about FROM table WHERE serial_number =
> currval('serial_number_seq');
>
> ap
>
> --
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> [EMAIL PROTECTED] - http://www.unc.edu/~aperrin
>
> On Thu, 29 Mar 2001, Bruce Momjian wrote:
>
> > > How does currval work if you are not inside a transaction. I have
> > > been experimenting with inserting into a table that has a sequence.
> > > If the insert fails (not using a transaction) because of bad client
> input
> > > then the next insert gets the proper next number in the sequence.
> >
> > If you are in a transaction, and the INSERT succeeds but the transaction
> > rolls back, the sequence does not get reused. Each backend has a local
> > variable that holds the most recent sequence assigned. That is how
> > currval works.
> >
> > >
> > > given sequence 1,2,3,4,5 exists
> > > insert into table date 1/111/01 (obviously wrong) insert fails...
> > > try again with good data, insert succeeds and gets number 6 in the
> > > sequence.
> > >
> > > i'm getting what I want. A sequence number that does not increment
> > > on a failed insert. However, how do I get the assigned sequence
> > > number with currval when I am not using a transaction? What
> > > happens when multiple users are inserting at the same time?
> > >
> > > I am trying to create a sequence with out any "missing" numbers. If
> > > there is a failure to insert, and a sequence number is "taken". I want
>
> > > the empty row.
> > >
> > > Thanks, it is getting clearer
> >
> > You really can't use sequences with no gaps. Sequence numbers are not
> > _held_ until commit because it would block other backends trying to get
> > sequence numbers.
> >
> > --
> > 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])
> >
>
>
> ---(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 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] RE: serial type; race conditions
OK:
* Transaction 1 commences, sets seed to seed + 1
* Transaction 2 commences, sets seed to seed + 1
* Transaction 1 inserts into some_table, selects seed (sequence now
has a hole)
* Transaction 2 inserts into some_table, selects seed (same value as
just used by transaction 1)
* The second transaction to commit will either create a duplicate
"seed" value, or roll back because of a unique constraint, still leaving a
hole in the sequence.
> -Original Message-
> From: Gerald Gutierrez [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, April 04, 2001 7:24 PM
> To: Jeff Eckermann; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
>
> It seems to just feel like conflicting requirements, so it's a tug-of-war.
>
> I've always done it by doing all the processing I can and then, from
> inside
> a transaction, do
>
> update seed from seed_table set seed=seed+1 where id='abc';
> insert into some_table values ((select seed from seed_table where
> id='abc'),
> other_stuff);
>
> The processing would be concurrent and only the update & insert would be
> "serialized". It would be portable and shouldn't contain holes, but is
> slower than sequences.
>
>
> Gerald.
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jeff Eckermann
> Sent: Thursday, March 29, 2001 10:48 AM
> To: 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: [SQL] RE: serial type; race conditions
>
>
> Probably just me: but I don't see the point. Consider:
> * User 1 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 2 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 1 commits
> * User 2 commits (insert has sequence value one higher than for User
> 1, but same value for max(foo) + 1), or
> * If foo has a unique constraint, transaction 2 will roll back.
>
> Either way, I don't see what has been gained. All of the messages I have
> read on this subject conclude with the same point: choice is to:
> * accept unique sequence with holes
> * accept loss of concurrency (as in the example above).
>
> Or am I just missing the point?
>
> > -Original Message-
> > From: Andrew Perrin [SMTP:[EMAIL PROTECTED]]
> > Sent: Thursday, March 29, 2001 8:46 AM
> > To: [EMAIL PROTECTED]
> > Cc: PgSQL-SQL
> > Subject:Re: serial type; race conditions
> >
> > I ditto what Bruce said - trying to get a true sequence without gaps is
> a
> > losing battle. Why don't you, instead, use a serial column as the real
> > sequence, and then a trigger that simply inserts max(foo) + 1 in a
> > different column? Then when you need to know the column, do something
> > like:
> >
> > SELECT number_i_care_about FROM table WHERE serial_number =
> > currval('serial_number_seq');
> >
> > ap
> >
> > --
> > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> > (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> > [EMAIL PROTECTED] - http://www.unc.edu/~aperrin
> >
> > On Thu, 29 Mar 2001, Bruce Momjian wrote:
> >
> > > > How does currval work if you are not inside a transaction. I have
> > > > been experimenting with inserting into a table that has a sequence.
> > > > If the insert fails (not using a transaction) because of bad client
> > input
> > > > then the next insert gets the proper next number in the sequence.
> > >
> > > If you are in a transaction, and the INSERT succeeds but the
> transaction
> > > rolls back, the sequence does not get reused. Each backend has a
> local
> > > variable that holds the most recent sequence assigned. That is how
> > > currval works.
> > >
> > > >
> > > > given sequence 1,2,3,4,5 exists
> > > > insert into table date 1/111/01 (obviously wrong) insert fails...
> > > > try again with good data, insert succeeds and gets number 6 in the
> > > > sequence.
> > > >
> > > > i'm getting what I want. A sequence number that does not increment
> > > > on a failed insert. However, how do I get the assigned sequence
> > > > number with currval when I am not using a transaction? What
> > > > happens when multiple users are inserting at the same time?
> > > >
> > > > I am tryi
FW: [SQL] RE: serial type; race conditions
OK, I'll wake up now.
Transaction 2 will actually wait for transaction 1 to complete, before
updating "seed".
But that drops you on the other horn of the dilemma, i.e. loss of
concurrency (partial at least).
That may not matter for your application, but for very high traffic, this
will become a problem.
> -Original Message-
> From: Jeff Eckermann
> Sent: Thursday, April 05, 2001 10:36 AM
> To: 'Gerald Gutierrez'; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
> OK:
> * Transaction 1 commences, sets seed to seed + 1
> * Transaction 2 commences, sets seed to seed + 1
> * Transaction 1 inserts into some_table, selects seed (sequence now
> has a hole)
> * Transaction 2 inserts into some_table, selects seed (same value as
> just used by transaction 1)
> * The second transaction to commit will either create a duplicate
> "seed" value, or roll back because of a unique constraint, still leaving a
> hole in the sequence.
>
> -Original Message-
> From: Gerald Gutierrez [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, April 04, 2001 7:24 PM
> To: Jeff Eckermann; 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: RE: [SQL] RE: serial type; race conditions
>
>
> It seems to just feel like conflicting requirements, so it's a tug-of-war.
>
> I've always done it by doing all the processing I can and then, from
> inside
> a transaction, do
>
> update seed from seed_table set seed=seed+1 where id='abc';
> insert into some_table values ((select seed from seed_table where
> id='abc'),
> other_stuff);
>
> The processing would be concurrent and only the update & insert would be
> "serialized". It would be portable and shouldn't contain holes, but is
> slower than sequences.
>
>
> Gerald.
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jeff Eckermann
> Sent: Thursday, March 29, 2001 10:48 AM
> To: 'Andrew Perrin'
> Cc: PgSQL-SQL
> Subject: [SQL] RE: serial type; race conditions
>
>
> Probably just me: but I don't see the point. Consider:
> * User 1 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 2 commences insert transaction: grabs nextval(sequence),
> max(foo)
> * User 1 commits
> * User 2 commits (insert has sequence value one higher than for User
> 1, but same value for max(foo) + 1), or
> * If foo has a unique constraint, transaction 2 will roll back.
>
> Either way, I don't see what has been gained. All of the messages I have
> read on this subject conclude with the same point: choice is to:
> * accept unique sequence with holes
> * accept loss of concurrency (as in the example above).
>
> Or am I just missing the point?
>
> > -Original Message-
> > From: Andrew Perrin [SMTP:[EMAIL PROTECTED]]
> > Sent: Thursday, March 29, 2001 8:46 AM
> > To: [EMAIL PROTECTED]
> > Cc: PgSQL-SQL
> > Subject:Re: serial type; race conditions
> >
> > I ditto what Bruce said - trying to get a true sequence without gaps is
> a
> > losing battle. Why don't you, instead, use a serial column as the real
> > sequence, and then a trigger that simply inserts max(foo) + 1 in a
> > different column? Then when you need to know the column, do something
> > like:
> >
> > SELECT number_i_care_about FROM table WHERE serial_number =
> > currval('serial_number_seq');
> >
> > ap
> >
> > --
> > Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> > (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
> > [EMAIL PROTECTED] - http://www.unc.edu/~aperrin
> >
> > On Thu, 29 Mar 2001, Bruce Momjian wrote:
> >
> > > > How does currval work if you are not inside a transaction. I have
> > > > been experimenting with inserting into a table that has a sequence.
> > > > If the insert fails (not using a transaction) because of bad client
> > input
> > > > then the next insert gets the proper next number in the sequence.
> > >
> > > If you are in a transaction, and the INSERT succeeds but the
> transaction
> > > rolls back, the sequence does not get reused. Each backend has a
> local
> > > variable that holds the most recent sequence assigned. That is how
> > > currval works.
> > >
> > > >
> > > > given seque
[SQL] RE: Re: select substr???
Regular expressions make this much easier. The below could be shortened to: create function ComparisonString(text) returns text as ' declare t alias for $1; r text; c char; begin if t is null or t !~ ''[^a-zA-Z0-9]'' then return t; end if; r = ; for i in 1 .. char_length(t) loop c = substring(t from i for 1); if c ~ ''[a-zA-Z0-9]'' then r = r || c; end if; end loop; return r; end; ' language 'plpgsql' with (IsCachable); > -Original Message- > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, April 10, 2001 11:38 AM > To: [EMAIL PROTECTED] > Subject: Re: Re: select substr??? > > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote: > > Hi, > > > > I have postgres 6.x (where x is something). > > > > I have the following list of data > > > > data > > > > ABC* > > ABC > > ABC- > ... > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > > characters] order by data' > > somewhere I use the following, which might be adapted to do what you > want. I am sure there are more elegant ways of doing this, though. > > create function ComparisonString(text) returns text as ' > declare > t text; > r text; > c char; > ns bool; > begin > if $1 is null then > return NULL; > end if; > t = lower(trim(both $1)); > r = ; > ns = false; > for i in 1 .. char_length(t) loop > c = substring(t from i for 1); > if c = '' '' then > if ns then > r = r || '' ''; > end if; > ns = false; > else > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > then > r = r || c; > ns = true; > end if; > end if; > end loop; > return trim(both r); > end; > ' language 'plpgsql' with (IsCachable); > > Albert. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(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: Re: select substr???
And if you have plperl installed, something like this is even easier: create function texttrim(text) returns text as '$_[0] =~ s/\\W//g; return $_[0]' language 'plperl'; (I just read the docs) :-) > -Original Message- > From: Jeff Eckermann [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, April 10, 2001 5:41 PM > To: [EMAIL PROTECTED] > Subject: RE: Re: select substr??? > > Regular expressions make this much easier. The below could be shortened > to: > > create function ComparisonString(text) returns text as ' > declare > t alias for $1; > r text; > c char; > begin > if t is null or t !~ ''[^a-zA-Z0-9]'' > then > return t; > end if; > r = ''''; > for i in 1 .. char_length(t) loop > c = substring(t from i for 1); > if c ~ ''[a-zA-Z0-9]'' > then > r = r || c; > end if; > end loop; > return r; > end; > ' language 'plpgsql' with (IsCachable); > > > > > -Original Message- > > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, April 10, 2001 11:38 AM > > To: [EMAIL PROTECTED] > > Subject:Re: Re: select substr??? > > > > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote: > > > Hi, > > > > > > I have postgres 6.x (where x is something). > > > > > > I have the following list of data > > > > > > data > > > > > > ABC* > > > ABC > > > ABC- > > ... > > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > > > characters] order by data' > > > > somewhere I use the following, which might be adapted to do what you > > want. I am sure there are more elegant ways of doing this, though. > > > > create function ComparisonString(text) returns text as ' > > declare > > t text; > > r text; > > c char; > > ns bool; > > begin > > if $1 is null then > > return NULL; > > end if; > > t = lower(trim(both $1)); > > r = ''''; > > ns = false; > > for i in 1 .. char_length(t) loop > > c = substring(t from i for 1); > > if c = '' '' then > > if ns then > > r = r || '' ''; > > end if; > > ns = false; > > else > > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > > then > > r = r || c; > > ns = true; > > end if; > > end if; > > end loop; > > return trim(both r); > > end; > > ' language 'plpgsql' with (IsCachable); > > > > Albert. > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > ---(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 ---(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: RE: Re: select substr???
The references to REs in the docs are a little hard to find (maybe it's just me). Bruce's book gives a good coverage, with the benefit of an index to find where that is. AFAICT, the implementation is "extended regular expressions", like egrep. My choice of characters to match was a little arbitrary: it would depend on the specification. You are correct, the check for "$1 is null" is not required. I was attempting an optimisation, as in "don't do anything else if this is null". The gain would depend on how much further processing the function would attempt before recognizing that it was dealing with a null value, which is something that I don't know enough to tell. > -Original Message- > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 12, 2001 10:44 AM > To: [EMAIL PROTECTED] > Subject: Re: RE: Re: select substr??? > > Thanks, interesting. I did not find any mention of regular expressions > in the 7.0.2 docs I installed locally. > > BTW, your code does not do exactly the same, as it removes any > whitespace while the other one only collapses consecutive blanks. But, > of course, regular expressions in PL/pgSQL make this much easier. > > As a further aside, I think that in both versions of the function the > check for `$1 IS NULL' is not necessary; I got the impression that > passing NULL as an argument to a function will automatically return > NULL as the result, doesn't it? > > Albert. > > > On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote: > > Regular expressions make this much easier. The below could be shortened > to: > > > > create function ComparisonString(text) returns text as ' > > declare > > t alias for $1; > > r text; > > c char; > > begin > > if t is null or t !~ ''[^a-zA-Z0-9]'' > >then > > return t; > > end if; > > r = ''''; > > for i in 1 .. char_length(t) loop > > c = substring(t from i for 1); > > if c ~ ''[a-zA-Z0-9]'' > > then > > r = r || c; > > end if; > > end loop; > > return r; > > end; > > ' language 'plpgsql' with (IsCachable); > > > > > -Original Message- > > > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > ... > > > > > > somewhere I use the following, which might be adapted to do what you > > > want. I am sure there are more elegant ways of doing this, though. > > > > > > create function ComparisonString(text) returns text as ' > > > declare > > > t text; > > > r text; > > > c char; > > > ns bool; > > > begin > > > if $1 is null then > > > return NULL; > > > end if; > > > t = lower(trim(both $1)); > > > r = ''''; > > > ns = false; > > > for i in 1 .. char_length(t) loop > > > c = substring(t from i for 1); > > > if c = '' '' then > > > if ns then > > > r = r || '' ''; > > > end if; > > > ns = false; > > > else > > > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > > > then > > > r = r || c; > > > ns = true; > > > end if; > > > end if; > > > end loop; > > > return trim(both r); > > > end; > > > ' language 'plpgsql' with (IsCachable); > > > > > ---(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
Re: [SQL] RTREE on points
Julian Scarfe wrote: > > explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box; > NOTICE: QUERY PLAN: > Seq Scan on nodes (cost=0.00..1.10 rows=1 width=28) > this should work, assuming you have enough points to make a difference (in the optimizer's mind, at least). the optimizer still doesn't do a great job of knowing when it's best to use an index, although, in your sample, there's no way it would ever be cheaper to use an index. there's simply not enough data there. you can test to see if an index can be used by a query by shutting off the sequential scans (set enable_seqscan=off) and retrying the query. essentially, this forces it to use an index scan if at all possible. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] RTREE on points
Julian Scarfe wrote: > > It hadn't occured to me that the index would simply not be used and I'm > grateful for the pointer to the appropriate variable. i wouldn't recommend turning off sequential scans for day-to-day usage, but it certainly can be useful for debugging and testing. if you have specific queries that you need optimized, you can do that, but the whole point of cost estimates is to give a good estimate of what a normal query would return, so if you have a lot of ad-hoc queries, it's probably better to just trust the system. > Nevertheless, wouldn't... > > CREATE INDEX test_rtree ON nodes USING RTREE (node); > (which fails) > > ...be a lot simpler than... > > CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node)); > (which succeeds, as above) > > ? yes, it does seem like a little more work, but there doesn't seem to be a lot of usage of the geometric functions by the developers to look at missing features -- they're mostly just reactive to problems. i really have never dug into tweaking access methods to get this to work, but i would imagine it's not that hard to implement. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] RTREE on points
Tom Lane wrote: > BTW, you should also look at the GIST stuff and figure out whether > it might not be better to develop a GIST opclass instead of rtree. > In the long run I suspect GIST will be better supported than rtree, > since it's more general. > > regards, tom lane are there any built-in GIST opclasses? i didn't see any when looking through the system tables. weren't there things like gist_box_ops & gist_poly_ops at one time? i know there are a couple of GiST examples in contrib (seg, cube & intarray), but i thought there used to be at least a gist_box_ops. or was that another contrib item that got dropped? -- Jeff Hoffmann PropertyKey.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] RTREE on points
Tom Lane wrote: > > I don't recall any such thing having been removed, but it does seem > peculiar that there are no GIST opclasses in the standard distribution. > How the heck did the GIST index code get developed/tested without some > opclasses? doing some digging at berkeley, i found the original pggist patch file that created the gist access method & gist_box_ops opclass (among others). i'm assuming that patch was the basis for what was originally introduced, so i don't know why it didn't get included with everything else. it looks like there are a lot of calls to internal postgresql box comparison functions that would need to get converted to the new calling convention, but it should be pretty straightforward to get it to work with a recent version of postgresql. it does seem pretty silly to have it in there if you don't have any built-in way of using it, if for no other reason than to be able to test if the feature even works. -- Jeff Hoffmann PropertyKey.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] RTREE on points
Oleg Bartunov wrote: > > GiST is great ! > > You may look at http://www.sai.msu.su/~megera/postgres/gist/ > for GiST implementation of RTree - it could be not compiled with 7.1 > release due to some api changes, but it's not difficult to do. it looks like i just wasted a good couple of hours trying to convert the gist_box_ops. it did help find the pointer problem i was having because i'm still not up to speed on the new function calling conventions, though... > If somebody want it I could contribute it to contrib area. i'm definitely interested. i'm going to play with it & if oleg's claim holds about index insertion time holds, i can definitely see myself moving to it over the built in rtree. anything that can cut down the hours of index creation time would be great. also, it seems that it'd be a good choice for inclusion in the standard distribution because it'd be easy to test -- you already have to run rtree tests anyway, you can just duplicate them with gist & gist_box_ops. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] creating tables that are visible across databases
Mark Stosberg wrote: > > Hello, > > I'd like to create some tables that would visible across databases, > much like the postgres system tables. These would be for "static" data, > such as state and country codes, and geo-spatial data. I couldn't find > this mentioned in the docs, but unless this feature of the system tables > is magical, it appears to be possible. Did I miss an explanation in some > docs, or could someone give me a pointer? > Thanks! you could create the tables using a template database, in which case they would be created when you create a new database using that template (look at "createdb -T" to see how this works). this may not be what you're looking for because they wouldn't be shared across databases, they would just be copied into the new database when it was created. i don't think there's a way to have shared access to those tables. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] SET SEQSCAN TO OFF - error
Hans-Jürgen Schönig wrote: > > I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > Does anybody know why? > because the documentation is wrong. the variable name is ENABLE_SEQSCAN, i.e.: SET ENABLE_SEQSCAN TO OFF; should work. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] "correct" sorting.
Hi folks, say i have a text field with teh values 1,2,3,10,20,30,1a,1b,2a,2b and i want to sort it so i get, 1 1a 1b 2 2a 2b 3 10 20 30 is there anyway to do that with postgresql ? below is what actually happens. jeff=> select * from foo order by var1; var1 -- 1 10 1a 1b 2 20 2a 2b 3 30 3a 3b (12 rows) ---(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] "correct" sorting.
You're trying to compare apples and oranges. Since the field is of type text, it will sort by text. Therefore, the result will be 1 then 10 then 1a and so forth. It is sorting based on ASCII. The only way to get it to sort in proper numerical order is to make the field a numeric field. But of course you won't be able to use characters in that. Therefore, create a second field called revision or whatever of text. Now you can sort correctly with: select * from foo order by var1,revision; On Thu, 3 May 2001, Jeff MacDonald wrote: > Hi folks, > > say i have a text field with teh values > > 1,2,3,10,20,30,1a,1b,2a,2b > > and i want to sort it so i get, > > 1 > 1a > 1b > 2 > 2a > 2b > 3 > 10 > 20 > 30 > > is there anyway to do that with postgresql ? > below is what actually happens. > > jeff=> select * from foo order by var1; > var1 > -- > 1 > 10 > 1a > 1b > 2 > 20 > 2a > 2b > 3 > 30 > 3a > 3b > (12 rows) > -- Jeff Self Information Specialist Great Bridge, LLC www.greatbridge.com | www.greatbridge.org Norfolk, VA (757)233-5570 [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] RE: Order by email address by domain ?
I assume that you want to do this within the database, i.e. with a query. You would need to use a function, but I don't think a builtin function would do it. Try: CREATE FUNCTION email_order (text) RETURNS text AS ' select substr ($1, strpos ($1, ''@'') + 1) || substr ($1, 1, strpos ($1, ''@'') -1) ' LANGUAGE 'sql'; Then do: SELECT . FROM ... ORDER BY email_order (fieldname); I am assuming text datatype; substitute as appropriate. That's doubled single quotes around the @ symbol, by the way. > -Original Message- > From: Hervé Piedvache [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, May 10, 2001 1:38 PM > To: [EMAIL PROTECTED] > Subject: Order by email address by domain ? > > Hi, > > I just want to order by a listing of email address by domain like : > > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > Is it possible and how ? > > Thanks ! > -- > Hervé Piedvache > > Elma Ingenierie Informatique > 6, rue du Faubourg Saint-Honoré > F-75008 - Paris - France > http://www.elma.fr > Tel: +33-1-44949901 > Fax: +33-1-44949902 > Email: [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(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: Order by email address by domain ?
???
I don't think anyone suggested adding a new builtin function.
Yes, your suggestion is good for an occasional use.
Where this kind of functionality is likely to be needed on a continuing
basis, my experience is that wrapping the code up in a custom function is
easier and cleaner than writing it out every time. Saves a lot of typing,
not to mention the risk of typos (which could give spurious results without
being obvious about it). Also, a function allows for indexing on that
value, which can be a great aid to performance.
I have found that sometimes it is better to just add a column or two to the
table to contain the needed key, because with large amounts of data that can
be much quicker. Doing this too much though can lead to a cluttered
database, and a loss of clarity about just what all of those extra fields
are for... It's a judgement call.
Just my $0.01 (That's $0.02 Australian :-))
> -Original Message-
> From: Frank Bax [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, May 10, 2001 8:53 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Order by email address by domain ?
>
> Why is everyone else suggesting new functions? This works (in 6.5.3):
>
>ORDER BY lower(substring(email from position('@' in email)+1 )),
> lower(email)
>
> remove the lower() functions if you don't need them (I had mixed case
> addresses).
>
> I am guessing/assuming that it's cheaper to just use entire email address
> in second key rather than extract before the '@' character.
>
> Frank
>
> At 08:37 PM 5/10/01 +0200, you wrote:
> >Hi,
> >
> >I just want to order by a listing of email address by domain like :
> >
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >[EMAIL PROTECTED]
> >
> >Is it possible and how ?
> >
> >Thanks !
> >--
> >Hervé Piedvache
> >
> >Elma Ingenierie Informatique
> >6, rue du Faubourg Saint-Honoré
> >F-75008 - Paris - France
> >http://www.elma.fr
> >Tel: +33-1-44949901
> >Fax: +33-1-44949902
> >Email: [EMAIL PROTECTED]
> >
> >---(end of broadcast)---
> >TIP 6: Have you searched our list archives?
> >
> >http://www.postgresql.org/search.mpl
> >
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] execute client application from PL/pgSql
you could hack the pg_dump bit out of phpPgAdmin i think the license permits it. just my 2 cents. jeff On Wed, 9 May 2001, Jack wrote: > Date: Wed, 9 May 2001 09:45:46 +1000 > From: Jack <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: [SQL] execute client application from PL/pgSql > > Is that possible to execute a client application from server site by > PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, > or is there any Windows version of all Client Applications come from > PostGreSQL V7.1? > > Jack > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] execute client application from PL/pgSql
hi, phpPGAdmin is a web based php driven postgresql admin tool. not sure of the exact url, try google :) it has a pg_dump option in it. jeff On Sat, 12 May 2001, datactrl wrote: > Date: Sat, 12 May 2001 10:23:39 +1000 > From: datactrl <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] execute client application from PL/pgSql > > Thank You Jeff, > What is phpPgAdmin and where can get it? > > Jack > > - Original Message - > From: "Jeff MacDonald" <[EMAIL PROTECTED]> > To: "Jack" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Saturday, May 12, 2001 2:28 AM > Subject: Re: [SQL] execute client application from PL/pgSql > > > > you could hack the pg_dump bit out of phpPgAdmin > > i think the license permits it. > > > > just my 2 cents. > > > > jeff > > > > On Wed, 9 May 2001, Jack wrote: > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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] seleting all dates between two dates
I am interested in a query where I can select all dates between two dates. I figure I can build a table of all valid dates with a resonable range and then select from that table, but I would like to use the power of sql to get the work done without building a date table. Any ideas? For example: I want all dates between 05-29-2001 and 06-02-2001 The result set would be: 05-30-2001 05-31-2001 06-01-2001 Thanks for the help. -Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: rpt
In article <[EMAIL PROTECTED]>, "Hilkiah Lavinier" <[EMAIL PROTECTED]> wrote: > Hi, this is probably not the correct group for this post, but it's kinda > related. Does anyone know of a report writer/generator that works for > postgres? It would be nice if it is natively built in or some 3rd party > tool that I can use. Depends on what platform you want to run the report generator on. If you're happy with a Windows platform, you could connect Access or Crystal Reports, or a large number of other query/report tools to Pg via ODBC. -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. [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] create type of table
I have a problem where I need to restrict queries by more than a million ids that are the result of another program. Typically I would in the application layer write those ids into an IN( ) clause, but that will not work for more than 10,000 ids. So I need to load these ids into the db into some structure that I can call/join in the other queries. I have a I amd considering a temporary solution where a table is loaded with the nessesary ids for restricting the queries. I would really like something more efficent. In oracle I know I can create a variable of type table and fill it with these ids and then write my queries to use this table. How can I do this in postgresql, I did not see any support for a type table. Anyone know of any good tutorials or how-tos for doing this type of stuff in postgresql? Thanks - Jeff ---(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: can we write to a flat file from Postgresql procedure
I don't think there is any way to do this using any of the generally available tools. You could do a workaround using pl/pgsql, in the following way: * insert your data into a temp table * output the data to a file using COPY pl/pgsql allows INSERT statements, but does not allow CREATE/DROP TABLE or COPY statements. Those can be constructed using EXECUTE, which takes a string (which can be constructed using parameters) and passes it on to be executed as an SQL statement. For more information, check out the PostgreSQL documentation, especially on pl/pgsql (under "Procedural Languages") and COPY (under "SQL Commands"). > -Original Message- > From: R Vijayanath [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, July 05, 2001 10:34 AM > To: [EMAIL PROTECTED] > Subject: can we write to a flat file from Postgresql procedure > > Hi, > > I found your name in the Postgresql web site. > > It would be great if you can tell me if I can write a > procedure that can write the output to the OS(Linux > OS) file. > > Can you assist me on this if there is a way to do it. > > We are using Postgresql 7.1 running in Linux Redhat > 7.1. > > Thanks in Advance. > > Vijay > > > __ > Do You Yahoo!? > Get personalized email addresses from 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: can we write to a flat file from Postgresql procedure
I haven't seen anything on this list to say that pl/perlu is being worked on. Is it? > -Original Message- > From: Alex Pilosov [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, July 11, 2001 10:03 AM > To: Peter Eisentraut > Cc: R Vijayanath; [EMAIL PROTECTED] > Subject: Re: can we write to a flat file from Postgresql procedure > > On Wed, 11 Jul 2001, Peter Eisentraut wrote: > > > R Vijayanath writes: > > > > > It would be great if you can tell me if I can write a > > > procedure that can write the output to the OS(Linux > > > OS) file. > > > > You could try out PL/sh for that. > > > > http://www.postgresql.org/~petere/plsh.html > Well, as long as we all keep plugging our favorite languages, in 7.2, you > can use pl/perlu ;) > -alex > > > ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] RE: pl/pgsql - code review + question
I think you need to use syntax:
raise exception ''Member % Not Found'', unitno;
> -Original Message-
> From: Gary Stainburn [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, July 18, 2001 10:24 AM
> To: pgsql-sql
> Subject: Re: pl/pgsql - code review + question
>
> Okay, I've been hit round the back of the head, and I realised that the
> postgresql functions (inc subtring) are available in pl/pgsql, so that's
> my
> problem solved.
>
> I've written the getmid function as below, which is basically the same as
> the
> getunitno I included in my first post.
>
> My problem now is that when I include the code to handle the record not
> being
> there, from the pgsql chapter (section 23.2.3.3) I get the following
> errors
> based of the function below. Can anyone explain why the concat of the
> string
> is failing. If I simply "raise exception ''member not found''" all works
> fine.
>
> __BEGIN__ (screen output)
> [revcom@curly revcom]$ psql -f t
> DROP
> CREATE
> [revcom@curly revcom]$ psql -c "select getmid('NE/011-06');"
> NOTICE: plpgsql: ERROR during compile of getmid near line 15
> ERROR: parse error at or near "|"
> [revcom@curly revcom]$
> __END__
> __BEGIN__ (~/t which contains the function def)
> drop function getmid(varchar);
> CREATE FUNCTION getmid(varchar) RETURNS int4 AS '
> DECLARE
> unitno ALIAS FOR $1;
> teamno varchar;
> munit int4;
> results RECORD;
> BEGIN
> teamno := substring(unitno from 1 for 6);
> munit := substring(unitno from 8);
> select into results m.mid as mid
> from teams t, members m
> where t.tid = m.mteam and
> t.tnumber = ''teamno'' and
> m.mnumber = munit;
> if not found then
> raise exception ''Member '' || unitno || '' not found'';
> return 0;
> end if;
> return results.mid;
> END;
> ' LANGUAGE 'plpgsql';
> __END__
>
> Gary
> On Wednesday 18 July 2001 3:10 pm, Gary Stainburn wrote:
> > Hi all, I've just written my first pl/pgsql function (code included
> below
> > for you to pull apart).
> >
> > It takes an int4 mid (e.g. 15) and then using a select pulls out the
> team
> > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full
> unit
> > number NE/012-02.
> >
> > I now want to write the reverse function, where I can enter 'NE/012-02'
> and
> > get back the mid 15. The bit I'm stuck on is now I split the team part
> > from the member part so that I can build the select statement.
> >
> > TIA Gary
> >
> > __BEGIN__
> > CREATE FUNCTION getunitno(int4) RETURNS varchar AS '
> > DECLARE
> > mid ALIAS FOR $1;
> > results RECORD;
> > BEGIN
> > select into results t.tnumber as tnumber, m.mnumber as mnumber
> > from teams t, members m
> > where t.tid = m.mteam and m.mid = mid;
> > if results.mnumber < 10 then
> > return results.tnumber || ''-0'' || results.mnumber;
> > else
> > return results.tnumber || ''-'' || results.mnumber;
> > end if;
> > END;
> > ' LANGUAGE 'plpgsql';
> > __END__
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---(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
---(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: pl/pgsql - code review + question
If the string will always be in that general form, use substring & position functions (see "String Functions and Operators" in the docs. Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1); If you don't want the leading zero, you could make make the "+1" into "+2". If you might have more than one leading zero, you could use ltrim: unit_number := ltrim(unit_number, ''0''); > -Original Message- > From: Gary Stainburn [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, July 18, 2001 9:10 AM > To: pgsql-sql > Subject: pl/pgsql - code review + question > > Hi all, I've just written my first pl/pgsql function (code included below > for > you to pull apart). > > It takes an int4 mid (e.g. 15) and then using a select pulls out the team > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit > > number NE/012-02. > > I now want to write the reverse function, where I can enter 'NE/012-02' > and > get back the mid 15. The bit I'm stuck on is now I split the team part > from > the member part so that I can build the select statement. > > TIA Gary > > __BEGIN__ > CREATE FUNCTION getunitno(int4) RETURNS varchar AS ' > DECLARE > mid ALIAS FOR $1; > results RECORD; > BEGIN > select into results t.tnumber as tnumber, m.mnumber as mnumber > from teams t, members m > where t.tid = m.mteam and m.mid = mid; > if results.mnumber < 10 then > return results.tnumber || ''-0'' || results.mnumber; > else > return results.tnumber || ''-'' || results.mnumber; > end if; > END; > ' LANGUAGE 'plpgsql'; > __END__ > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] RE: Records exactly the same.
If you include "oid" in your GROUP BY clause, you will get each distinct record. That will get you by for right now, but Josh's point is correct. You need some kind of unique key in your table. But... if you want to see every distinct record: why are you using a GROUP BY? > -Original Message- > From: Josh Berkus [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 20, 2001 9:48 AM > To: Fons Rave; [EMAIL PROTECTED] > Subject: Re: Records exactly the same. > > Fons, > > > I have a file in which there are records with what people have done. > > In the file > > are records with name, date, what they have done, time-length, etc. > > It is > > possible that there are two records that are exactly the same > > (somebody has done > > the same, on the same day for one hour). If I use a SELECT with GROUP > > on a list > > with some records that are exactly the same, only ONE of the > > identicals is > > shown. But I want to see them all. > > Well, there isn't an easy answer for you ... because you've designed > your database wrong. Records should *never* be the same. That is, ni > fact, one of the cardinal rules of Relational Database Design. > > You need to add an arbitrary primary key (e.g. act_key SERIAL NOT NULL > PRIMARY KEY) or some other piece of data (such as start_time) to make > each record unique. Then, by including that unique data in your query, > you can insure that all records are displayed. > > Frankly, I'm surprise that you're not getting errors when you try to > DELETE a record. This usually happens in tables without a unique key. > > It's been my argument for some time that PostgreSQL should require a > primary key on table creation. > > -Josh > > > __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 << File: >> << > File: >> << File: >> << File: ATT09948.txt >> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: example of [outer] join
You could make your FROM clause something like: FROM members m INNER JOIN address a ON m.madd = a.aid INNER JOIN teams t ON m.team = t.tid LEFT JOIN emails e ON m.memail = e.eid I think that should work: if not, try putting everything between (but not including) FROM and LEFT JOIN in brackets (parentheses for those over the pond :-)) > -Original Message- > From: Gary Stainburn [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 20, 2001 5:22 AM > To: pgsql-sql > Subject: example of [outer] join > > Hi, me again. > > I'm having fun here, but here's another question for you. > > Could someone please give me an example of a join where one of the fields > is > missing - I think reading from the docs that this is an OUTER join, but > I'm > having a problem with the syntax. I'm trying to create a view that pulls > in > all the relevent details for a single member. > > I'm still with my members table, which has links to the address table > (m.madd > = a.aid), teams table (m.mteam = t.tid) and the email table (m.memail = > e.eid). > > While every member has an address, and every member belongs to a team, not > > everyone has an email address. My problem is that every member without an > > email address gets omitted from the result set. > > My view so far is: > CREATE view member_dets as > select *, > getphone(m.mphone) as phone, > getphone(m.mfax) as fax, > getphone(m.mmobile) as mobile, > getunitno(m.mid) as munitno > from members m, > address a, > teams t, > emails e >where m.madd = a.aid and > m.memail = e.eid and > m.mteam = t.tid; > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] RE: PLpgSQL
You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql cover it: basically, "EXECUTE string" will cause that string to be executed as a SQL statement. > -Original Message- > From: Dado Feigenblatt [SMTP:[EMAIL PROTECTED]] > Sent: Friday, July 20, 2001 2:26 PM > To: Pgsql-Sql > Subject: PLpgSQL > > 3 questions: > > 1. Can I use CREATE SEQUENCE inside a function? > 2. I can create this function but I can't get it to run: > > CREATE FUNCTION new_proj_pts_seq(int4) > RETURNS text > AS 'DECLARE > proj_ID alias for $1; > seq_name TEXT; > BEGIN > seq_name := ''proj_pts_'' || proj_ID; > create sequence seq_name; > END; > RETURNS seq_name;' > LANGUAGE 'plpgsql'; > > When I do > SELECT new_proj_pts_seq(9000); > I get: > ERROR: parser: parse error at or near "$1" > > The same happens if I assign $1 to proj_ID (instead of aliasing), or > just use $1 in the string concatenation. > I always get the same message. > > Considering that all the above is possible/fixable... > > 3. On the statement 'create sequence seq_name;', will 'seq_name' be > evaluated properly? > > Thanks. > > > -- > Dado Feigenblatt Wild Brain, Inc. > Technical Director (415) 553-8000 x??? > [EMAIL PROTECTED] San Francisco, CA. > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] RE: Records exactly the same.
The content of your record is not limited to user data. Including a field that provides a unique key is simple: look at the documentation for data type SERIAL for an easy way to do this. You can also include information about when the record was inserted, and by whom, just by including fields in your table definition like: when_inserted timestamp default now(), who_insertedtext default current_user, which will be populated automatically every time a record is inserted. Also, see documentation on triggers for more sophisticated ways of doing this kind of thing. > -Original Message- > From: Fons Rave [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, July 21, 2001 7:15 AM > To: [EMAIL PROTECTED] > Subject: Re: Records exactly the same. > > > Well, there isn't an easy answer for you ... because you've designed > > your database wrong. Records should *never* be the same. That is, ni > > fact, one of the cardinal rules of Relational Database Design. > > Well, I started with "I'm a beginner". But I'm sure there's no reason NOT > to > accept two records that are exactly the same. In the example I gave, it is > clear > that the information I want to store can contain two records that are > exactly > the same; doing the same thing, on the same day, for the same amount of > time. In > this case it is the technical structure that doesn't want it like that. So > I > have to change it to make it work. > > Fons. > [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 ---(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: position(text,text) function
You are confusing the syntax of two similar functions:
position ('substring' in 'string')
strpos ('string', 'substring').
I have a feeling that "position" actually calls "strpos", but I am guessing
on that.
> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, July 23, 2001 9:27 AM
> To: [EMAIL PROTECTED]
> Subject: position(text,text) function
>
> I'm having trouble using the position function, it's a simple built-in
> function.
>
> It returns the index of a substring within a main string.
>
> But, I just can't get it to work, it always gives this silly parser error.
> I've tried it in a variety of ways with variables instead of constants
> casting
> to text and using it in other contexts, (ie. in functions, not just in a
> select). What am I doing wrong? Is this not the correct syntax for a two
>
> argument function?
>
> test=# select position('hello','el');
> ERROR: parser: parse error at or near ","
>
>
> Karl Orbell.
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] performance issue with distance function
Ryan Littrell wrote: > > I am trying to execute the following command: > > SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance > FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR > WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND > R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance(L1.lat, > L1.lon, L2.lat, L2.lon) <= 60 > LIMIT 100 OFFSET 0 > > I would rather execute this command: (but i get the error "Attribute > 'distance' not found") > > SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance > FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR > WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND > R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance <= 60 > LIMIT 100 OFFSET 0 > > Having that second distance function in the "WHERE" section of my sql > statement is costing me at least 10-20 seconds of execution time. I am > looking for a solution that will speed this up. Does anyone have any advice. > Thanks in advance. > this probably isn't what you want, but would it speed things up if you did an "order by distance" instead of doing the "distance <= 60", then having your application cut the results at 60? that should work from a language perspective, at least, and if the distance function is pretty computationally intensive, it should help. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Restriction by grouping problem.
The query I have now: SELECT min(datettime), sid FROM logs GROUP by sid; This returns the first instance of an sid in the logs table, there can be many rows in the table for each sid. The problem I have is that I do not need the SID I just need to group the min(datetime) by it. I need to return the column getfile. So the question I have is how would I create the retstriction of the min(datetime), sid and only return the value of getfile? The english of what I am looking for is. Select the getfile for the earliest of each sid. Any help would be greatly appreciated. --Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Restriction by grouping problem.
Yeah I have been having a bit of a problem expressing myself in terms of this query, sorry about that. What I need is: SELECT getfile FROM logs (and a restriction that results in finding the rows with the lowest datetime for each unique sid) To define this table a bit more: Logs table has a primary key of logid (int4) and serveral columns, of which I am deling with sid (text), getfile (text), and datetime (int4). Now a select getfile, datetime, logid from logs where sid = onevalue; would return a set of rows for that sid, the row I want is for the one with the smallest aka min(datetime) and I want this for every row in the table. Thanks for the quick response Josh, hopefully above will help out some. --Jeff ""Josh Berkus"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Jeff, > > > The query I have now: > > > > > > SELECT min(datettime), sid FROM logs GROUP by sid; > > > > > > This returns the first instance of an sid in the logs table, there > > > can be > > > many rows in the table for each sid. The problem I have is that I do > > > not > > > need the SID I just need to group the min(datetime) by it. > > The answer to this question is simple: > > SELECT min(datetime) as mintime FROM logs GROUP BY sid; > > However, I get the impression that your intended problem was more > complicated. Can you re-explain it? > > -Josh > > > __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 ---(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: Fuzzy matching?
With version 7.2 we will have pl/perlu (untrusted), which will allow use of the various Perl modules which do this sort of thing. > -Original Message- > From: Josh Berkus [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, July 31, 2001 1:16 PM > To: Joe Conway; Bruce Momjian > Cc: Josh Berkus; [EMAIL PROTECTED] > Subject: Re: Fuzzy matching? > > Joe, > > > In any case, metaphone is reportedly more accurate (at least for > > English > > words) than soundex, and levenshtein offers an entirely different and > > interesting approach. Any interest in having all three of these in > > the > > backend? > > I'm quite interested, myself. How difficult is it for somebody that > doesn't program C to attach a function from the Contrib directory? If > it's not very difficult, then I'd recommend putting metaphone in > /contrib, and levenstein in the backend. My reasoning is that > levenstein is useful for all roman alphabets, but metaphone is not so > useful for non-english versions of postgres. > > -Josh > > __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 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] RE: Referencing named attribute in where clause doesn't work with 7.1.2?
The WHERE clause is evaluated before your SELECT list is determined, so the aliased value cannot be used. You can put further NOT NULL tests into the subqueries to make sure that null values are not returned. Question: why not just join the tables explicitly? The more usual SQL approach would be something like: SELECT article.title_text_key, on_text.text_value AS title_text_value FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group ON code.code_group_id = code.id) ON on_text.lang_id = code.id) ON article.title_text_key = on_text.text_key WHERE on_text.text_value IS NOT NULL; or whatever other tests you want. In this case, you can easily reference the fields by name. > -Original Message- > From: Andreas Joseph Krogh [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, August 08, 2001 7:57 AM > To: [EMAIL PROTECTED] > Subject: Referencing named attribute in where clause doesn't work > with 7.1.2? > > Hi, this is my first post to this list so please... > I have problems getting this query to work, any ideas? > > select article.title_text_key, > (select on_text.text_value from on_text where > on_text.text_key = title_text_key > AND NOT title_text_key is NULL > AND on_text.lang_id = (select code.id from code, code_group where >code.code_group_id = code_group.id > AND code_group.description = 'lang' AND code.code_key = > 'lang.NO' > ) > ) > as title_text_value from article where NOT title_text_value is NULL; > > ERROR: Attribute 'title_text_value' not found > > Issuing the same query without the where-clause does work tho, but > return tupples with null in them which I don't want. > > -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RE: RE: Referencing named attribute in where clause doesn't work with7.1.2?
I suggest the best place to put your tests is in the WHERE clause. They are out of place in the JOIN clauses, even though this (apparently) works as you expect, because they do not represent a joining of fields. Also, easier to read and understand in the WHERE clause. On joins in general: there are plenty of kinds, but in real life inner or outer (left or right: all the same) joins will be all you want 99% of the time. Read up on what those do, and you'll be in good shape. > -Original Message- > From: Andreas Joseph Krogh [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, August 08, 2001 9:41 AM > To: [EMAIL PROTECTED] > Subject: Re: RE: Referencing named attribute in where clause doesn't > work with7.1.2? > > Jeff Eckermann wrote: > > > > The WHERE clause is evaluated before your SELECT list is determined, so > the > > aliased value cannot be used. > > You can put further NOT NULL tests into the subqueries to make sure that > > null values are not returned. > > Question: why not just join the tables explicitly? > > :-) Because I'm not too familiar with joins. > > > The more usual SQL > > approach would be something like: > > > > SELECT article.title_text_key, on_text.text_value AS title_text_value > > FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group > ON > > code.code_group_id = code.id) ON on_text.lang_id = code.id) ON > > article.title_text_key = on_text.text_key > > WHERE on_text.text_value IS NOT NULL; > > > > or whatever other tests you want. In this case, you can easily > reference > > the fields by name. > > The problem with the query above is that it doesn't include my > "code.code_key='lang.NO'" test. > > I rephrased the query as follows: > > SELECT article.title_text_key, on_text.text_value AS title_text_value > FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group > ON > code.code_group_id = code_group.id) ON on_text.lang_id = code.id AND > code_group.description = 'lang' AND code.code_key = 'lang.NO') ON > article.title_text_key = on_text.text_key > WHERE on_text.text_value IS NOT NULL; > > And now it works! > > Thank you for helping me out. > > -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: create function using language SQL
The "create function" syntax is the same. The language syntax is straight SQL: CREATE FUNCTION getteamno(int4) RETURNS varchar AS ' SELECT tregion || ''/'' || to_char(tnumber, ''FM000'') FROM teams WHERE tid = $1 ' LANGUAGE 'sql'; You don't get to build in error checking, but there has to be a reason to use plpgsql :-) SQL is non-procedural, after all. Check the docs: Reference Manual/SQL Commands/Create Function - Original Message - From: "Gary Stainburn" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 14, 2001 7:40 AM Subject: create function using language SQL > Hi all, > > As I was reading up on create function etc. while learning plpgsql, I seam to > remember it stated that as well as plpgsql, that other languages are/will be > available including using SQL as the language. However, I cannot find the > syntax to create a function in SQL. Specifically, how you return the result. > > As an example, how would I create a SQL function to match the plpgsql > function below? > > > CREATE FUNCTION getteamno(int4) RETURNS varchar AS ' > DECLARE > unitno varchar; > BEGIN > select into unitno > tregion || ''/'' || > to_char(tnumber,''FM000'') > from teams > where tid = $1; > if not found then > raise exception ''Team % not found'',$1; > return ; > end if; > return unitno; > END; > ' LANGUAGE 'plpgsql'; > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] \set variant for use in regular sql commands
I need to use the \set command in regular sql (I am running postgres queries through php and it does not seem to support the \ commands in general). How can I set a variable with regular sql? I want to be able to do the following: \set testvar '1,2,3,4,5' \set testvar '6,7,8,':testvar Which is then used in: select * from table where id in (:testvar); Thanks, --Jeff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Interval FAQ - please review
Why not just include examples of DATEDIFF and DATEADD functions? For example: CREATE FUNCTION datediff(timestamp, timestamp) RETURNS integer AS ' BEGIN RETURN $2 - $1; END; ' LANGUAGE 'plpgsql'; And similarly with DATEADD. You will increase the scope (and length) of your article, but only slightly: and add some other useful education, as well. - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 16, 2001 11:22 AM Subject: Interval FAQ - please review > Folks, > > Please review this for inaccuracies before I post it to pgsql-newbie and > the docs. > > -Josh > > FAQ: Working with Dates and Times in PostgreSQL > > This FAQ is intended to answer the following questions: > > Q: Where are the DATEADD() and DATEDIFF() functions in PostgreSQL? > Q: How do I tell the amount of time between X and Y? > > KEYWORDS: date, datetime, timestamp, operator, dateadd, datediff, > interval > > One of PostgreSQL's joys is a robust support of a variety of date and > time data types and their associated operators. This > has allowed me to write calendaring applications in Postgres that would > have been considerably more difficult on other > platforms. Before we get down to the nuts-and-bolts, I need to explain > a few things to the many who have come to us from > database applications which are less ANSI 92 SQL compliant than > PostgreSQL (particularly Microsoft SQL Server, SyBase and > Microsoft Access). If you are already educated, you'll want to skip > down to "Working with DATETIME, DATE, and INTERVAL > values". > > (BTW, I am not on an anti-Microsoft tirade here. I use MS SQL Server as > an example of a non-standards-compliant database > because I am a certified MS SQL Server admin and know its problems quite > well. There are plenty of other non-compliant > databases on the market.) > > ANSI SQL and OPERATORS > > In the ANSI SQL world, operators (such as + - * % || NOT) are defined > only in the context of the data types being operated > upon. Thus the division of two integers ( INT / INT ) does not function > in the same way as the divsion of two float values > (FLOAT / FLOAT). More dramatically, you may subtract one integer (INT - > INT) from another, but you may not subtract one > string from another (VARCHAR - VARCHAR), let alone subtract a string > from an integer (INT - VARCHAR). The subtraction > operator (-) in these two operations, while it looks the same, is in > fact not the same owing to a different datatype context. In > the absence of a pre-defined context, the operator does not function at > all and you get an error message. > > This fundamental rule has a number of tedious consequences. Frequently > you must CAST two values to the same data type > in order to work with them. For example, try adding a FLOAT and a > NUMERIC value; you will get an error until you help out > the database by defining them both as FLOAT or both as NUMERIC > (CAST(FLOAT AS NUMERIC) + NUMERIC). Even more > so, appending an integer to the end of a string requires a type > conversion function (to_char(INT, '0')). Further, if you > want to define your own data types, you must spend the hours necessary > to define all possible operators for them as well. > > Some database developers, in a rush to get their products to market, saw > the above "user-unfriendly" behavior and cut it > out of the system by defining all operators to work in a > context-insensitive way. Thus, in Microsoft Transact-SQL, you way > add a DOUBLE and an INTEGER, or even append an INTEGER directly to a > string in some cases. The database can handle > the implicit conversions for you, because they have been simplified. > > However, the Transact-SQL developers disregarded the essential reason > for including context-sensitive operators into > the SQL standard. Only with real, context-sensitive operators can you > handle special data types that do not follow > arithmatic or concatination rules. PostgreSQL's ability to handle IP > addresses, geometric shapes, and, most importantly for > our discussion, dates and times, is dependant on this robust operator > implementation. Non-compliant dialects of SQL, such > as Transact-SQL, are forced to resort to proprietary functions like > DATEADD() and DATEDIFF() in order to work with dates > and times, and cannot handle more complex data types at all. > > Thus, to answer the first question: > Q. Where are the DATEADD and DATEDIFF functions in PostgreSQL? > A. There are none. PostgreSQL does not need them. Use the + and - > operators instead. Read on. > > > WORKING with DATETIME, DATE, and INTERVAL VALUES > > Complete docs on date/time data types may be found at: > http://www.postgresql.org/idocs/index.php?datatype-datetime.html > I will not attempt to re-produce them here. Instead, I will simply try > to explain to the beginner what you need to know to > actually work with dates, times, and intervals. > > DATETIME or TIMESTAMP: Str
[SQL] Re: how to use record type
I have encountered this problem (in a different context), and could not find a way to insert entire rows/records in the way that you appear to want. But it worked fine if I INSERTed explicitly, like: INSERT INTO table VALUES (OLD.field1, OLD.field2, ..., OLD.fieldn); That should work fine inside a plpgsql function. - Original Message - From: "Horst Herb" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, August 16, 2001 5:24 PM Subject: Fwd: how to use record type > I have difficulties understanding how to use variable of "record" or "row" > type. How do I actually insert the variables OLD or NEW or a record type into > a table from within a trigger? > > Like doing the following: > > drop table th1; > create table th1( > id serial, > text text ); > > drop table th_audit; > create table th1_audit( > ts timestamp default now() > ) inherits(th1); > > drop function thaudit(); > create function thaudit() returns opaque as ' > begin > -- I want to insert OLD into th_audit - how do I do this??? > return NEW; > end; ' language 'plpgsql'; > > drop trigger ta on th1; > create trigger ta before delete or update on th1 > for each row execute procedure thaudit(); > > Reagrds, > Horst > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Simple SQL-syntax
UPDATE TableA SET nrA = TableB.nrB WHERE nrA = TableB.nrA; UPDATE statements don't take FROM clauses. I don't think they like aliasing, either. - Original Message - From: Fredrik Thunberg To: [EMAIL PROTECTED] Sent: Monday, August 20, 2001 6:15 AM Subject: Simple SQL-syntax Hello. I have a very simple sql-question from an sql-beginner: Suppose I have Table A looking something like this: Index Text NrA And Table B like this: NrA NrB Then I want to change all occurences of NrA in Table A to NrB... I've tried: UPDATE tableA Set tableA.nrA = B.nrB From tableB B Where tableA.nrA = B.nrA; But that doesn't seem to work... Best regards /Fredrik Thunberg
[SQL] Re: Sequential select queries...??
Select id from T where name = 'bleh' UNION ALL Select id from T where description = 'bleh'; Will get you the resultset you want, but: I don't believe that you can do a GROUP BY on it. If you just want counts, as you describe below, you could do something like: SELECT 'Only One', (SELECT count (id) from T where name = 'bleh' OR description = 'bleh') - (SELECT count (id) from T where name = 'bleh' AND description = 'bleh') UNION SELECT 'Both', (SELECT count (id) from T where name = 'bleh' AND description = 'bleh'); - Original Message - From: "Mark Mikulec" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 14, 2001 7:40 PM Subject: Sequential select queries...?? > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: split/explode functions
Not amongst the builtin functions. You will need to create your own using a procedural language. Easiest is plperl, since Perl already has a very functional "split" function. - Original Message - From: "omid omoomi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 20, 2001 7:31 PM Subject: split/explode functions > hi , > Is there any split or explode function which split a whole string to > seperate parts using any special delimiter ? > TIA > Omid > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Getting 'n-1'th record.
SELECT * FROM table ORDER BY field DESC LIMIT 1 OFFSET 1; This way you don't need to know the value of "n" in advance. The descending ORDER BY is to indicate a reversal of your intended ordering, so as to make the n-1'th record the second record. Note that getting the n-1'th record from an unordered list will not give meaningful results, because you cannot predict what will be returned. - Original Message - From: "Bhuvan A" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 21, 2001 2:41 AM Subject: Getting 'n-1'th record. > > hi all, > > consider below.. > > An sql query results with 'n' records. OK. > I need ONLY the 'n-1'th record. > > > HOW CAN I GET THIS? > > Thankx in advance! > > == > Q: What's the difference between the 1950's and the 1980's? > A: In the 80's, a man walks into a drugstore and states loudly, "I'd > like some condoms," and then, leaning over the counter, whispers, >"and some cigarettes." > > == > > Regards, > Bhuvaneswar. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help On Postgresql
a) Look at "Date/Time Functions" in the docs: specifically, listed under "Functions and Operators" in the Users Guide. b) "Serial" type will do this. Look under "Numeric Types" , in the "Data Types" section of the Users Guide. - Original Message - From: Jaydip To: [EMAIL PROTECTED] Sent: Thursday, August 23, 2001 2:50 AM Subject: Help On Postgresql Dear Friends I am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd. I have some questions regarding Postgresql which are listed below: a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a Date/DateTime variable. For Example : i> In Oracle to_char(dt,'') - for extracting the year value from a date variable dt ii> In SqlServer datepart(hh,dt) - for extracting the Hour value from a date variable dt b) Can I create an identity column in Postgresql the value of which column will start from 1 say and keep on incrementing by 1 OR how can I serve my purpose in any other way. Please do let me know if any one of you have any solution. Regards Jaydip
Re: [SQL] 2 tables, joins and same name...
I learned SQL from Sam's "Teach Yourself SQL in 21 Days", and am happy to recommend it. Which book is "best" is very subjective and situation dependent; all I can say is that this one did the job for me. Pros: clearly written; knowledgable authors; good coverage Cons: all due to space limitations. Many advanced features are touched on, without any really useful explanation of how to use them or even why they exist. Can be a good starting point though (The pl/pgsql docs made no sense at all to me, until I had read the Oracle pl/sql coverage in this book). - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Ross J. Reedstrom" <[EMAIL PROTECTED]>; "Josh Berkus" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, September 01, 2001 11:44 AM Subject: Re: 2 tables, joins and same name... > Ross, > > > Have you seen "Database Design for Mere Mortals" by Michael > > Hernandez? > > Yeah, that's one I've been loaning out a lot. However, while it does > cover a lot of good stuff about how to design a database, it never gets > past the most elementary SQL ... really, no further than Bruce gets. > And if I recommend Hernandez together with "SQL for Smarties", well, > that's over 600 pages combined ... > > What I'd really love to see, I guess, would be a 200 page "Elements of > SQL" book organized into "lessons" for the beginner. Maybe with an > additional 75 pages of Q&A examples at the back. > > Maybe I should write one. > > > And there's another one, that I can only remember as 'the pink book', > > I can never remember the title! that struck me as a reasonably good > > intro to intermediate level book. > > Oh, that'll make me friends at Stacy's Bookstore. "I'm not sure of the > title, and I don't know the author or publisher, but it's about > databases and it's pink." ;-P > > -Josh > > __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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] row level locking?
I have an update statement (no transaction controls surround these statements): update sessions set sessdate = 0 where sessid in ( long list of ids); How long will the rows being updated be locked for this statement? Will all be locked until all updates are completed or will the row locking only occur for each row being updated? If I have a statement like: update sessions set sessdate = 0 where datetime < 1; (this would be the same criteria that created the list used above) How long will each row be locked for? These queries can be updating a good number of rows ( > 10,000) every 10 minutes and I need to figure out how signifigant of an impact the locking occuring in those updates can be. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] calling a shell script from pl/pgsql
How can I call a shell script from within a pl/pgsql function that is called as from a trigger. I do not want to interact with the script I just want it to run. I do want the trigger to wait for the script it called to complete before returning a value. Any ideas would be greatly appreciated. Thanks, Jeff Barrett ---(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] calling a shell script from pl/pgsql
Thanks for the suggestions. I am running 7.1.2 and going to 7.1.3 soon. If I use pl/tclu or pl/perlu I can call this executable from within the code? I have a signifigant limitation, I cannot duplicate the action of the programs I want to call in a program I write within postgres, I need to call the executable (In this one case it is a shell script but I have others where it is a binary). I cannot find the pl/sh module. The google links that came up brought me to pages that no longer exist on postgresql.org. I will have to look around some more. Thanks for the advice... great help! Jeff Barrett "Jeff Barrett" <[EMAIL PROTECTED]> wrote in message 9nip2p$1s5o$[EMAIL PROTECTED]">news:9nip2p$1s5o$[EMAIL PROTECTED]... > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. > > Thanks, > > Jeff Barrett > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How do I extract ONE particular field, when multiple table contain the same field name?
You need to qualify "DENOM" with the table name: just write "xi.DENOM". I find this to be good general practice when selecting from more than one table. - Original Message - From: "Olle Wijk" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 12, 2001 4:06 AM Subject: How do I extract ONE particular field, when multiple table contain the same field name? > Hi, > > I am pretty new att using SQL-quires, could anyone help me with this > one: > > I want to do the following SQL-query: > > Select XID, DENOM, PRICE, FRT, CTID From XItem xi, Category c Where > xi.System=1 and xi.Category=c.Index > > the problem is that the field 'DENOM' is present in both table 'XItem' > and > 'Category' (it is a text description field where you explain whatever > you > want). Therefore I get the following complaint when I run the query: > > Error msg: "The specified fiel 'DENOM' could refer to more than one > table listed > in the FROM clause of your SQL-statement" > > The DENOM-field I actually want is the one belonging to the > XItem-table. > I would most appreciate if someone could give me a hint how to alter > the > SELECT-statement above so that it does what I want. > > Regards > > /olw > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(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] Number the lines
If you want "the third maximum value", easist to do: SELECT * FROM table ORDER BY whatever DESC OFFSET 2 LIMIT 1; - Original Message - From: "Yoann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, September 14, 2001 7:05 AM Subject: Number the lines > how can I number the result's lines of a sql query ? > > explaination : I have a query which return me a list of values. I need > to order them (it's ok, easy ;) and then number the lines. The goal is > then to extract, for example, "the third maximum value". > > Tx in advance ! > Yoann > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] transposing data for a view
--- James Orr <[EMAIL PROTECTED]> wrote: > I think he might be talking about mine. The region > values will not be > duplicated, the WHERE clause prevents it. If you are saying that I didn't read the original query closely enough, you're probably right. Unfortunately I deleted the original message, so I can't check that. > I kind of prefer my own query aesthetically, is it > as efficient internally? Someone more knowledgable will have to answer that: though I would guess that working with three joined tables would slow things down somewhat. > > - James > __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.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] transposing data for a view
Josh, I don't see how you got the result you quote below. My query sums the volume figures, grouping by scanid: that worked as expected (one line per scanid) on my system when I tested it (version 7.1.2, though I don't think that matters). Jeff --- Josh Berkus <[EMAIL PROTECTED]> wrote: > Of couse, it's only *half* a solution. Your query > will result in: > > scanidA_volume B_volume C_volume > 1 34.5 > 1 55.1 > 1 12.3 > 2 11.1 > etc. > __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating a boolean function
Maybe the reason is that you defined the function to return bool, but are attempting to return text? You need conditional logic, which suggests pl/pgsql is the way to go. Something simple should do it, like: create function check_loan (text,int,int) returns bool as ' begin select * from loans where typetable=$1 and code_user=$2 and code_loan=$3; if found then return true; else return false; end if; return true; end; ' language 'plpgsql'; Since this is your project, I will take the luxury of not testing that code :-) - Original Message - From: "Miguel González" <[EMAIL PROTECTED]> To: "Haller Christoph" <[EMAIL PROTECTED]> Cc: "PostgreSQL SQL" <[EMAIL PROTECTED]> Sent: Wednesday, September 19, 2001 1:55 PM Subject: Creating a boolean function > by the way you know how to create the following sql function? > > I need to return a boolean saying if an object is onloan. > > the loan table is as follows: > > loans > - > code_loan > code_user > typetable > > > i want to check these three fields, the first two fields (the code ones) are > integer and the last a string. > > I tried > > create function check_loan (text,int,int) > returns bool > as > 'select CAST(typetable AS text) from loans where typetable=$1 and > code_user=$2 and code_loan=$3;' > language 'sql'; > > > But I got that the parser cannot identify the =$ operator and requires me to > do the proper casting. > > How can I do it? Sorry for the questions, but I am new at creating functions > and I have to hand in this project tomorrow. > > Many thanks > > Miguel > > > > > > > ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select is fast, update based on same where clause is slow
I have a select statement that returns a set of 74,000+ results back in under a minute as follows: select s.sessid, s.membid, s.datetime from sessions2 s, (select min(datetime) as datetime, membid from sessions2 where membid is not null group by membid) as minsess where s.membid = minsess.membid and s.datetime = minsess.datetime; The final cost from explain for the above select is 22199.15 ... 24318.40 with rows = 5 and width = 28. Then I issue an update as follows (to update those 74,000+ rows): update sessions2 set sinceinception = 0 from sessions2 s, (select min(datetime) as datetime, membid from sessions2 group by membid) as mindate where s.membid = mindate.membid and s.datetime = mindate.datetime; The final cost from explain for the above update is 31112.11...98869.91 with rows = 2013209 and width=87. This update statement has been left running over night and does not complete. The ram usage on the machine is at about 3/4 capacity (800mb) during this time and CPU usage is near 100%. The machine has the -F option set and memory segments of 200mb and is running 7.1.2. What could be causing this update statement to not complete? Why are the costs so different since it seems to me that besides the cost of the update they are the same query? Any help would be great! Jeff Barrett ---(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] select is fast, update based on same where clause is slow
That did the trick. Thank you for the quick detailed answer. It runs in about a minute now. Jeff Barrett "Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > On Fri, 21 Sep 2001, Jeff Barrett wrote: > > > I have a select statement that returns a set of 74,000+ results back in > > under a minute as follows: > > > > select s.sessid, s.membid, s.datetime > > from sessions2 s, (select min(datetime) as datetime, membid > > from sessions2 > > where membid is not null > > group by membid) as minsess > > where s.membid = minsess.membid > > and s.datetime = minsess.datetime; > > > > The final cost from explain for the above select is 22199.15 ... 24318.40 > > with rows = 5 and width = 28. > > > > Then I issue an update as follows (to update those 74,000+ rows): > > update sessions2 set sinceinception = 0 > > from sessions2 s, (select min(datetime) as datetime, membid from sessions2 > > group by membid) as mindate > > where s.membid = mindate.membid > > and s.datetime = mindate.datetime; > > > > The final cost from explain for the above update is 31112.11...98869.91 with > > rows = 2013209 and width=87. > > > > This update statement has been left running over night and does not > > complete. The ram usage on the machine is at about 3/4 capacity (800mb) > > during this time and CPU usage is near 100%. The machine has the -F option > > set and memory segments of 200mb and is running 7.1.2. > > > > What could be causing this update statement to not complete? > > Why are the costs so different since it seems to me that besides the cost of > > the update they are the same query? > > I thought that the updated table is always in your from list (implicitly), > so you'd want: > update sessions2 set sinceinception = 0 > from (select min(datetime) as datetime, membid from sessions2 group by > membid) as mindate > where sessions2.membid=mindate.membid and > sessions2.datetime=mindate.datetime; > > I think your query would be joining the s/mindate results against > sessions2. > > > > ---(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 ---(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] ORDER BY case insensitive?
In article <PXEu7.602317$[EMAIL PROTECTED]>, "Bob Swerdlow" <[EMAIL PROTECTED]> wrote: > How do I get the rows sorted in a case insensitive way? > SELECT * FROM MyTable ORDER BY Name; Try SELECT * FROM MyTable ORDER BY upper(Name); (or 'lower(Name)'). -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. [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] assign count() result to a declared variable in plpgsql
try: f_count_var := count(empno) from employee; --- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > > i want to put my count() result in a plpgsql > declared integer variable > > > declare f_count_var integer; > begin > select into f_count_var count(empno) from employee > end; > > tried this one but it doesnt work > > > > > > > > > > > ---(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 > > __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] to_char() and order-by
Perhaps this was covered in the 7.2.x release notes, but it's mystifying us: select to_char(time_link,'999.99') from stat_fetch where time_link is not null order by 1 desc limit 10; (time_link is a double-precision column.) Under 7.1.3, this returns results like: to_char - 9.99 9.99 9.99 9.99 9.99 9.99 9.99 9.99 9.99 9.99 (10 rows) while under 7.2.1, you get the top 10 rows in *numerical* order: to_char - 278.78 261.07 240.25 180.24 173.26 160.35 159.02 144.57 134.21 131.66 (10 rows) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PLPGSQL language documentation
I'll second the suggestion about looking at Oracle PL/SQL materials. I used an introductory PL/SQL text when learning about PLpgSQL, and found it very useful. The syntax/functionality is highly similar (by design, apparently). Much of the Oracle PL/SQL functionality is not (yet) implemented in PLpgSQL however, so better to avoid detailed Oracle texts. Skip over the stuff about explicit cursors and exception handlers. --- Josh Berkus <[EMAIL PROTECTED]> wrote: > Sandy, > > > I was wondering if anyone knew of good online > documentation on PlPgsql? > > Read two things: > > 1. the online docs for PostgreSQL 7.2 under > "Programmer's guide: Procedural > Languages" > > 2. Roberto' Mello's enhanced documentation, > accessable through > techdocs.postgresql.org. Roberto also has a PLpgSQL > function library that > should give you some good examples. However, this > is entirely for Postgres > 7.0 and 7.1, so there are some syntax changes in the > current versions -- > mostly workarounds that are no longer necessary. > > Also, I found the first 4 chapters of O'reilly's > book on PL/SQL programming to > be invaluable in helping me develop an organized > approach toward manageing a > database using functions. However, the rest of the > book is Oracle stuff not > supported by Postgres, so you decide if it's worth > the $50. > > -- > -Josh Berkus > > __AGLIO DATABASE > SOLUTIONS___ > Josh Berkus >Complete information technology > [EMAIL PROTECTED] > and data management solutions (415) 565-7293 >for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "reverse()" on strings
On Mon, 26 Aug 2002 16:13:44 -0400, h012 wrote: > CREATE INDEX extension_idx ON file (reverse(name)); > -- but I didn't find a function called "reverse" > > CREATE INDEX extension_idx ON file (regex_match( '.*(\.[^\.]*)$' ); > -- but I didn't find a function called "regex_match" which would return > string matched in brackets () > You probably want to do a $ createlang plperl and then something like this: CREATE FUNCTION fn_strrev(text) returns text as ' return reverse($_[0]) ' language 'plperl' with (iscachable); The same approach can be used to provide a regex match with Perl syntax, but I don't have an example of that coded up and at hand. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Retrieving the new nextval...
--- friedrich nietzsche <[EMAIL PROTECTED]>
wrote:
> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that
> last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one do an
> insertion, so I would get the wrong ID...
"currval" will return the last value used _for the
current connection_. But if you want to be absolutely
sure, instead call "nextval" before doing your insert,
and use the returned value explicitly.
> does transactions resolve this, in Psql???
> I thought to solve it with a similiar solution,
> working in transactions inserting a raw and
> immedialtly after read from DB last raw, but who
> assure me that all will go right??
> If I was on a server app., I (and you, if it is your
> case) would insert a timestamp, and then I'd select
> from table where timestamp = mysavedtime;
> But in my case there could be two or more equals
> timestamp, cause there's not only one application
> working with DB...
> I'm still reading, searching, trying...
> ciao
> danilo
>
>
>
> --- Kevin Brannen <[EMAIL PROTECTED]> ha
> scritto: > Greg Patnude wrote:
> > > I am using postgreSQL with Perl::CGI and
> > Perl::DBI::Pg... I would like to be
> > > able to insert a row from my Perl script
> > [$SQL->exec();] and have postgreSQL
> > > return the id of the newly inserted record
> > (new.id) directly to the Perl
> > > script for further processing... Anyone with a
> > solution / idea ???
> > >
> > > Nearly EVERY table I create in postgreSQL (7.2)
> > has the following minimum
> > > structure:
> > >
> > > create table "tblName" (
> > >
> > > id int4 primary key nextval
> > ("tblName_id_seq"),
> > >
> > > ..field...
> > > )
> >
> > You can either do it in 2 statements, something
> > like:
> >
> > $dbh->do("insert into tblName ...");
> > my ($id) = $dbh->selectrow_array("select
> > currval('tblName_id_seq')");
> >
> > Or you could create a function which takes the
> > insert statement, and
> > ends with doing a select on the currval (as above)
> > and returning that.
> > As I do the 2 statement approach above, I haven't
> > done a function, but
> > it doesn't look like it would be that hard to do.
> >
> > HTH,
> > Kevin
> R
>
>
__
> Yahoo! Musica: notizie, recensioni, classifiche,
> speciali multimediali
>
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
>
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
__
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.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
[SQL] Large number of lookups
I have a large table (>2 Mrows) against which my application runs some 1M queries per day. The queries are almost all of the nature of select PRIMARY_KEY_FLD from MY_TABLE where SECONDARY_KEY_FLD = 'something'; I've optimized the table to the limits of what I can think of, now I'm looking to optimize the application. I'm really only interested in the yes/no question of whether the row exists (the returned PRIMARY_KEY_FLD value is pretty much just for debug documentation). What I'm wondering is whether my application would get better (faster) results if I ran a number of queries at once. For example, I could build SQL that looks like: select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD from MY_TABLE WHERE SECONDARY_KEY_FLD in (first_val, second_val, ...); so that the list contains some number of values (either a limited "slice" of the values I care about, or the whole pile--usually 100-200), and iterate this until I've checked them all. Another technique would be to construct a "UNION" table: select PRIMARY_KEY_FLD, SECONDARY_KEY_FLD from MY_TABLE join ( select first_val as "SECONDARY_KEY_FLD" union select second_val union ...) as TEMP_TABLE using (SECONDARY_KEY_FLD) likewise, either taking some number of my desired values in "chunks" or all at once. What's likely to work better, and why? (I'm going off to write a benchmark script, but I'd like to hear some theoretical answers, too.) -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] still sorting and casting problems
--- [EMAIL PROTECTED] wrote: > I need to sort the query by the log_date desc > (log_date is char(10)). I > tried the following without success : > > select id, log_date from userlog order by cast > (log_date as date) desc > > select id, log_date from userlog order by > date(log_date) desc > > Please help and thank's in advance ... jr > You haven't shown the error message here, but I'm guessing that there is no direct cast between char and date available in PostgreSQL. Try casting first to text, then to date. __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Select the max on a field
--- Gaetano Mendola <[EMAIL PROTECTED]> wrote: > > "Gaetano Mendola" <[EMAIL PROTECTED]> wrote in > message > alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]... > > Hi all, > > > > Suppose that I have a table like this: > > > > > > att_1 |att_2 |att_3 | att_4 > > > > 1 | a |y |y1 > > 2 | b |y |y2 > > 3 | a |xx |y3 > > 4 | c |zz |y4 > > 5 | a |t|y5 > > 6 | c |x |y6 > > > > > > > > I want obtain all row that have for each att_2 > > the max of att_1 > > I forgot to say: "with a single total query without > sub select " > If you don't mind a non-portable feature, DISTINCT ON should do what you want. Something like: SELECT DISTINCT ON (att_2) att_1, att_2, att_3, att_4 FROM table ORDER BY att_2, att_1 DESC; __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.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
[SQL] Query to evaluate space used
This may or may not be original, but I cobbled it together and thought it
might be useful: a query that reports how much space is used by each
table in your database, including TOAST and TOAST-IDX tables.
This particular version is a bit fancy because it shows the top 20 by
space, followed by a row for "All Others". You could eliminate the half
starting with 'union', and take out the 'limit 20' clause if you wanted
to see them all.
select "Table", "KRows", "MB" from
(select 1 as sort_order, * from (select min(relname) as "Table",
to_char(max(reltuples)/1000,'9990.9') as "KRows",
sum(relpages)/128 as "MB" from (
select relname, '', reltuples, relpages
from pg_class
where relkind = 'r'
union all
select a.relname, b.relname, 0, b.relpages
from pg_class a
join pg_class b
on (b.relname like 'pg_toast_' || a.relfilenode || '%')
where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc limit 20) as top_20
union
select 2, 'All Others', to_char(sum("KRows"),'9990.9'),
sum("MB")
from (
select min(relname) as "Table",
sum(reltuples)/1000 as "KRows",
sum(relpages)/128 as "MB" from (
select relname, '', reltuples, relpages
from pg_class
where relkind = 'r'
union all
select a.relname, b.relname, 0, b.relpages
from pg_class a
join pg_class b
on (b.relname like 'pg_toast_' || a.relfilenode || '%')
where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc offset 20) as "Others") as rows
order by sort_order, "MB" desc
--
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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Quartile (etc) ranking in a SQL statement?
Here's a puzzler: Given a query that returns rows ranked by some criteria, how can I write another query around it that will give me the (say) first quartile (top 25%)? Another way of putting it is: if I have rows that look like this: aaa | 1251 aba | 1197 cax | 1042 ... | ... axq | 23 (142 rows) How can I write a query that will return these as 1 | aaa | 1251 2 | aba | 1197 3 | cax | 1042 ... | ... | ... 142 | axq | 23 -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(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] calculating interval
On Fri, 22 Nov 2002 09:26:31 -0500, praveen vejandla wrote: > Dear All, > > Is there any way in postgresql to calculate the interval between two > times excluding specific days,specific duration. > > Ex: > timestamp1 : 2002-10-01 10:30AM > timestamp2 : 2002-15-01 04:50PM > > suppose if i need the difference between timestamp1,timestamp2 but i > don't want to count how many sun days are coming, i want to ignore all > sundays in between,i want to ignore certain timings(say 10.00 AM to > 5:00PM)s also,then how can I get the duration in this way. > The easiest way would be to write a function in a language that supports some kind of date library. I'm most familiar with Perl and its Date::Manip library, so that's what I would turn to. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(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
