[SQL] select with function per row
Hi, I have a table with FQDN. I'm trying to take from it the second part which desribed a city. Query isn't too clear but with what is important that whole substr was counted once and applied to every row. Can I (how?) force executor to run this substr on every row of my table? mtldb=# select substr(substr(s.nazwa,strpos(s.nazwa,'a.')+2),1, textlen(s.nazwa)-strpos(s.nazwa,+'.m')) from serwery_old s; substr poznan mtl.pl ka.kie tia maz Marcin Mazurek -- Kierownik Działu Systemowego MULTINET SA o/Poznan http://www.multinet.pl/
[SQL]
Hello everybody! I need some help with a simple query. I've got a problem with getting a maximum value from a very large table (1000+ rows): I have table: CREATE TABLE TABLE_A ( COL1 INT, COL2 INT, CONSTRAINT PK PRIMARY KEY (COL1, COL2) ) and when I want to get the maximum value for col1: SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1 it takes a large amount of time. I created an index on column COL1, but it doesn't work. Bc. Ferenc Lengyel Address:Prievozská 6/A, Bratislava 821 09, Slovakia Phone Number: +421 (7) 58222 222, +421 (7) 58222 407 E-mail address: mailto:[EMAIL PROTECTED]
Re: [SQL]
Hello Lengyel, Once, Monday, October 30, 2000, 5:36:23 PM, you wrote: LF> Hello everybody! LF> I need some help with a simple query. LF> I've got a problem with getting a maximum value from a very large table LF> (1000+ rows): LF> I have table: LF> CREATE TABLE TABLE_A LF> ( LF> COL1 INT, LF> COL2 INT, LF> CONSTRAINT PK PRIMARY KEY (COL1, COL2) LF> ) LF> and when I want to get the maximum value for col1: LF> SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1 LF> it takes a large amount of time. LF> I created an index on column COL1, but it doesn't work. You need to create index on COL2. -- Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
[SQL] Large Object dump ?
I need to move a DB from Pg 6.5 to 7 haw can i export Large Object to the new DB ? Pg_dumpall seems non to export LArge Object. Please Help Alex
RE: [SQL]
Can I be removed from this mailing list? > -Original Message- > From: Yury Don [SMTP:[EMAIL PROTECTED]] > Sent: Monday, October 30, 2000 6:55 AM > To: [EMAIL PROTECTED] > Subject: Re: [SQL] > > Hello Lengyel, > > Once, Monday, October 30, 2000, 5:36:23 PM, you wrote: > > LF> Hello everybody! > LF> I need some help with a simple query. > LF> I've got a problem with getting a maximum value from a very large > table > LF> (1000+ rows): > LF> I have table: > LF> CREATE TABLE TABLE_A > LF> ( > LF> COL1 INT, > LF> COL2 INT, > LF> CONSTRAINT PK PRIMARY KEY (COL1, COL2) > LF> ) > > LF> and when I want to get the maximum value for col1: > LF> SELECT MAX(COL1) FROM TABLE_A WHERE COL2 = 1 > LF> it takes a large amount of time. > LF> I created an index on column COL1, but it doesn't work. > > You need to create index on COL2. > > -- > Best regards, > Yury ICQ 11831432 > mailto:[EMAIL PROTECTED] > >
Re: [SQL] Large Object dump ?
Large objects are not dumped. It should be in the documentation for large objects. You need to write a script which writes them to disk and then imports them back in after you have installed your dbs. Troy > > > I need to move a DB from Pg 6.5 to 7 haw can i export Large Object to the > new DB ? > > Pg_dumpall seems non to export LArge Object. > > Please Help > > Alex > > >
[SQL]
Hi I'll ask for help. I have had used MySQL and the syntax of INSERT have a switch IGNORE if You try to insert a row that in the table already is. I'll ask if exist something like this in postgre SQL syntax. Petr Jezek
Re: [SQL]
At 13:36 30/10/00 +0100, Lengyel Ferenc wrote: >Hello everybody! >I need some help with a simple query. >I've got a problem with getting a maximum value from a very large table >(1000+ rows): Unfortunately PGSQL does not use indexes for min & max. One approach that should work is: select col1 from TABLE_A WHERE COL2 = 1 order by col2, col1 desc limit 1; and you will need an index on (col2, col1). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [SQL] Large Object dump ?
On Mon, 30 Oct 2000, [EMAIL PROTECTED] wrote:
>Large objects are not dumped. It should be
>in the documentation for large objects.
>
>You need to write a script which writes them to
>disk and then imports them back in after you have
>installed your dbs.
>
>
>Troy
CREATE TABLE news -- { chiave: id news ,newarchivio, newsnuove}
(
"idnews"SERIAL primary key,
"oidnotizia"OID,-- news as large object
"autore"TEXTx -- author
);
How should be done the script for this table ?
I found something about large object only onthe programmesg guide are they
present in other docs?
Thanks in advance
Alex
[SQL] unsubscribe
unsubscribe
[SQL] Need help! parse erro
Hi every one, I have just strated to use postgres. I wrote a vert
simple pl/pgsql function as given below:
CREATE FUNCTION
add_new_user(varchar(32),char(8),varchar(128),varchar(128),varchar(32),
varchar(32)) RETURNS boolean AS' DECLARE
oldUser RECORD;
USR ALIAS FOR $1;
PWORD ALIAS FOR $2;
EMAIL ALIAS FOR $3;
ADDR ALIAS FOR $4;
CITY ALIAS FOR $5;
CNTRY ALIAS FOR $6;
BEGIN
SELECT INTO oldUser *
FROM users
where username=USR AND password= PWORD;
IF FOUND
THEN
RETURN ''f'';
ELSE
INSERT INTO USERS(username,password,email,address,city,country)
values(USR,PWORD,EMAIL,ADDR,CITY,CNTRY);
RETURN ''t'';
END IF;
END;'
LANGUAGE 'plpgsql';
When I run it I get the following error:
najdb=# select
add_new_user('najm'::varchar,'najm1234'::char,'najmh@whatever'::varchar,'123street'::varchar,'mtl'::varchar,'canada'::varchar);
ERROR: parser: parse error at or near "$1"
Someone, please help me out here.
I would also appreciate if you all "wizards" out there could send me
some more complex examples of plpgsql functions and triggers sinece
postgres docs doesn't have any good ones. Thank you all for all your
help.
Regards.
Najm.
[SQL] Synonyms
HELP!!! * Is there a way of creating synonyms of tables within one database in another database in Postgres similar to the Informix CREATE SYNONYM? * If not and related to the post on 2000-10-28 by Sivagami, is there a way, within a select I can make a query from mulitple databases. ( the usual DB.Table or DB:table or DB@table dont seem to work) Thanks Carl
[SQL] unsubscribe
unsubscribe application/ms-tnef
Re: [SQL] Synonyms
Carl Shelbourne writes: > * Is there a way of creating synonyms of tables within one database in another > database in Postgres similar to the Informix CREATE SYNONYM? Nope. > * If not and related to the post on 2000-10-28 by Sivagami, is there a way, > within a select I can make a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) Nope. It's not really planned either in case you wanted to ask. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] fetching rows
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? When i ask this you need to know that Im using PHP in web environment and try do this to get more performens from the script (now the script fetch all rows, then jump to needed row)
Re: [SQL] fetching rows
I hate to be the bearer of bad news, but if you're using PHP and you wanted to fetch just 25 rows at a time for a single page, and then fetch more when the user clicks on a NEXT button or link, you're completely out of luck. Each http transaction is completely separate and so you can't maintain a cursor between pages. Perhaps LIMIT would help you here; but I've found it more useful to make sure there is a unique key for the order I'm displaying, and then say "WHERE key > highest_key_value_on_current_page" Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
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
Re: [SQL] fetching rows
> "Jeff" == Jeff Hoffmann <[EMAIL PROTECTED]> writes: Jeff> you can't do that with a cursor, but you can use they mysql-ism Jeff> called a limit clause. for example, to fetch rows 26-50 from Jeff> that query, you'd do: Jeff> select * from films limit 25,26; Jeff> or Jeff> select * from files limit 25 offset 26; Since there's no particular ordering of the output of such a query it is necessary to explicitly state an ordering key. Otherwise you'll quickly find that the phase of the moon has very significant influence on the produced results. :-) Especially in 7.*, as I understand it. Martin -- GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt
Re: [SQL] Synonyms
On Mon, Oct 30, 2000 at 01:17:24PM +, Carl Shelbourne wrote: > * Is there a way of creating synonyms of tables within one database in another > database in Postgres similar to the Informix CREATE SYNONYM? > * If not and related to the post on 2000-10-28 by Sivagami, is there a way, > within a select I can make a query from mulitple databases. ( the usual > DB.Table or DB:table or DB@table dont seem to work) don't know how informix works, but why dont you just use a view? create view synonym as select * from table; am i missing something? depesz -- hubert depesz lubaczewski najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
