Re: [SQL] Using substr with user defined types
Thus spake Tom Lane
> [EMAIL PROTECTED] (D'Arcy J.M. Cain) writes:
> > Any ideas?
>
> Not with that much info. Sooner or later you're going to have to
> show us your C code...
Oh, sure. I was going to submit it to contrib when it was finished and
as I said, the fprintf test I added pretty much assures me that it isn't
in the code but just in case I am wrong, here it is.
/*
* PostgreSQL type definitions for chkpass
* Written by D'Arcy J.M. Cain
* [EMAIL PROTECTED]
* http://www.druid.net/darcy/
*
* $Id$
* best viewed with tabs set to 4
*/
#include
#include
#include
#include
#include
#include
/*
* This type encrypts it's input unless the first character is a colon.
* The output is the encrypted form with a leading colon. The output
* format is designed to allow dump and reload operations to work as
* expected without doing special tricks.
*/
/*
* This is the internal storage format for CHKPASSs.
* 15 is all I need but add a little buffer
*/
typedef struct chkpass
{
charpassword[16];
} chkpass;
/*
* Various forward declarations:
*/
chkpass*chkpass_in(char *str);
char *chkpass_out(chkpass * addr);
char *chkpass_rout(chkpass * addr);
/* Only equal or not equal make sense */
boolchkpass_eq(chkpass * a1, text * a2);
boolchkpass_ne(chkpass * a1, text * a2);
/* This function checks that the password is a good one
* It's just a placeholder for now */
static int
verify_pass(const char *str)
{
return 0;
}
/*
* CHKPASS reader.
*/
chkpass *
chkpass_in(char *str)
{
chkpass*result;
charmysalt[4];
static bool random_initialized = false;
static char salt_chars[] =
"./0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
/* special case to let us enter encrypted passwords */
if (*str == ':')
{
result = (chkpass *) palloc(sizeof(chkpass));
strncpy(result->password, str + 1, 13);
result->password[13] = 0;
return (result);
}
if (verify_pass(str) != 0)
{
elog(ERROR, "chkpass_in: purported CHKPASS \"%s\" is a weak password",
str);
return NULL;
}
result = (chkpass *) palloc(sizeof(chkpass));
if (!random_initialized)
{
srandom((unsigned int) time(NULL));
random_initialized = true;
}
mysalt[0] = salt_chars[random() & 0x3f];
mysalt[1] = salt_chars[random() & 0x3f];
mysalt[2] = 0; /* technically the terminator is not
* necessary but I
like to play safe */
strcpy(result->password, crypt(str, mysalt));
return (result);
}
/*
* CHKPASS output function.
* Just like any string but we know it is max 15 (13 plus colon and terminator.)
*/
char *
chkpass_out(chkpass * password)
{
char *result;
if (password == NULL)
return (NULL);
if ((result = (char *) palloc(16)) != NULL)
{
result[0] = ':';
strcpy(result + 1, password->password);
}
return (result);
}
/*
* special output function that doesn't output the colon
*/
char *
chkpass_rout(chkpass *password)
{
char *result;
if (password == NULL)
return (NULL);
if ((result = (char *) palloc(16)) != NULL)
strcpy(result, password->password);
return (result);
}
/*
* Boolean tests
*/
bool
chkpass_eq(chkpass * a1, text *a2)
{
charstr[10];
int sz = 8;
if (a2->vl_len < 8) sz = a2->vl_len;
if (!a1 || !a2) return 0;
strncpy(str, a2->vl_dat, sz);
str[sz] = 0;
return (strcmp(a1->password, crypt(str, a1->password)) == 0);
}
bool
chkpass_ne(chkpass * a1, text *a2)
{
charstr[10];
int sz = 8;
if (!a1 || !a2) return 0;
if (a2->vl_len < 8) sz = a2->vl_len;
strncpy(str, a2->vl_dat, sz);
str[sz] = 0;
return (strcmp(a1->password, crypt(str, a1->password)) != 0);
}
--
D'Arcy J.M. Cain| Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
Re: [SQL] Merging two columns into one
Thus spake Gary MacMinn > I have two columns in a table (areacode and phone number) that I'd like to merge >into one (phone number) containing both sets of info. Could anyone suggest a simple >way of achieving this? Oliver showed you how to merge these together with the SQL concatenate operator but there is another possibility. PostgreSQL could use a phone number type. I have been meaning to write a user defined type for some time but never seem to get around to it. Maybe you could give this a shot. The examples in contrib should get you started. Once finished you can make the phone # one column and split out parts with various functions. Don't forget to have a place for country code and extension. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
Re: [SQL] Merging two columns into one
Thus spake Oliver Elphick > SELECT col1, col2,..., areacode || phone as phone, colx, coly,... Although you may want this for easier reading. SELECT col1, col2,..., (areacode || ' ') || phone as phone, colx, coly,... Also, I would do "as fullphone" instead so that I don't get confused between the field name and my generated string but that's mainly a style issue. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
[SQL] Orderby two different columns
I ran into a problem today that I hope someone can help me with... I have a database (and application) that is used to track 'applicants'.. These applicants have two timestamp fields associated with their records and both have relevance as to how long the applicant has been available.. The resubmitted field s of type timestamp and has a default value of 'Sat Oct 02 00:00:00 1999 EDT' I need to order search results by the two dates. Here is the problem.. They want whichever date is the most recent to appear on top.. If I do 'order by resubmitted desc,created desc' I get something like this : Applicant Re-submitted Created A 06/05/2000 12/31/1999 B 06/05/2000 12/31/1999 C 05/17/2000 02/09/2000 D 05/17/2000 01/21/2000 E 05/11/2000 01/27/2000 F 05/11/2000 01/21/2000 G 05/01/2000 12/31/1999 H 04/28/2000 01/28/2000 I 04/28/2000 01/12/2000 J 05/23//2000 Ok, see applicant J? I need him to be above C.. Basically what I need to do is order by a combination of date created/resubmitted -- the way I'm doing it now is going to list al the resubmitted's in order, then all the created's in order.. Perhaps I'm just missing something simple, I sure hope so.. Hopefully I've explained it well enough. Thanks for any suggestions!!! -Mitch
Re: [SQL] Merging two columns into one
"D'Arcy J.M. Cain" wrote: > > PostgreSQL could use a > phone number type. I have been meaning to write a user defined type > for some time but never seem to get around to it. Maybe you could > give this a shot. The examples in contrib should get you started. > Once finished you can make the phone # one column and split out parts > with various functions. Don't forget to have a place for country code > and extension. i messed around with one a while back, but i dropped it before i finished it. if i recall correctly, it was mostly working, but i don't know if i broke it again after that or not. i'll put a copy of it on my web site -- you can download it, clean it up and put it in contrib if you want. i just tarred up the files, there's no documentation for it & barely any copyright info in it. put whatever license you want on it if you want to reuse it. the files located at: http://www.potlatch.org/source/phone.tar.gz -- Jeff Hoffmann PropertyKey.com
Re: [SQL] Using substr with user defined types
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes:
>> Not with that much info. Sooner or later you're going to have to
>> show us your C code...
> char *
> chkpass_rout(chkpass *password)
> {
> char *result;
> if (password == NULL)
> return (NULL);
> if ((result = (char *) palloc(16)) != NULL)
> strcpy(result, password->password);
> return (result);
> }
That doesn't return "text", so you can't tell the system it does.
Type text is a varlena type, ie, length word followed by data.
regards, tom lane
Re: [SQL] Orderby two different columns
"Mitch Vincent" <[EMAIL PROTECTED]> writes: > Ok, see applicant J? I need him to be above C. Your example was about as transparent as mud, but maybe you are looking to sort on MAX(resubmitted, created) or something like that? Anyway I'd think that sorting on some function of the two dates is probably what you need to do. I don't think we have a two-input MAX function like that, but it'd be easy enough to fake it with a CASE expression. Something like ... ORDER BY (CASE WHEN a > b THEN a ELSE b END) DESC; regards, tom lane
Re: [SQL] Orderby two different columns
"Mitch Vincent" wrote: >I need to order search results by the two dates. Here is the problem.. > >They want whichever date is the most recent to appear on top.. If I do >'order by resubmitted desc,created desc' I get something like this : > >Applicant Re-submitted Created >A 06/05/2000 12/31/1999 >B 06/05/2000 12/31/1999 >C 05/17/2000 02/09/2000 >D 05/17/2000 01/21/2000 >E 05/11/2000 01/27/2000 >F 05/11/2000 01/21/2000 >G 05/01/2000 12/31/1999 >H 04/28/2000 01/28/2000 >I 04/28/2000 01/12/2000 >J 05/23//2000 > > >Ok, see applicant J? I need him to be above C.. select * from table order by case when resubmitted > created then resubmitted else created end desc; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Train up a child in the way he should go, and when he is old, he will not depart from it."Proverbs 22:6
Re: [SQL] Using substr with user defined types
Thus spake Tom Lane > > char * > > chkpass_rout(chkpass *password) > > That doesn't return "text", so you can't tell the system it does. > Type text is a varlena type, ie, length word followed by data. Ack! That was it. I don't understand why it didn't print my debug message at the start of the function. I used "fprintf(stderr, ..." at the start of the function to make sure that it wasn't gobbled up by buffering or something. When I didn't see my message I just assumed that it had to come from the engine. Thanks. I was able to dig out what changes I needed for the operator stuff from the docs (I needed scalar??sel instead of int??sel) and now everything I had before plus the chkpass stuff works. I'll send the corrected chkpass stuff for contrib and work on that phone number item I mentioned in another message. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
[SQL] query failed , don't know why
UPDATE members m,payments p SET m.status = 2 WHERE p.paydate > 'now'::datetime - '1 month'::timespan and p.productid = 'xxx' and m.gid = p.gid i'm trying to run that query and i'm getting "parse error near m" but it looks ok to me i'm running postgresql 7.0.2 with freebsd 4.0 stable jeff
[SQL] Speaking of fulltextindex...
I just noticed this in some testing.. When I use my PHP application to insert text into the field that's used in the full text index it takes 9 full seconds, when investigating resource usage using 'top' I see this : Development from a completely idle start up : PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 682 postgres 2 0 124M 2328K select 0 0:00 0.00% 0.00% postgres Production server -- this one is being used : PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 96825 postgres 2 0 38380K 35464K sbwait 0:04 2.10% 2.10% postgres The backend is started exactly the same way with the same version (7.0.2) on these two servers with the options -B 4096 -o '-S 16384' -- can anyone think of a reason why would one initially grow to 124 megs? I'm waiting to see about that before I continue investigating the sudden exponential increase in my INSERT speed - hopefully it's related (because I sure see why the transaction suddenly take ten times longer to complete than it did!).. Thanks!! -Mitch
[SQL] Need to improve performance
Hallo,
My name is Vassili Akimov. I work with postgres, we maintain the main
database for our sponsors on it. One of thier requirements,- perform the
search through the long text field. Sometimes this field even exceeds
32k so we cut off the rest. But search trough this long field is too
slow. (it takes approximately 4 minutes of proccessor time on Intel
pentium II 266MHz). So we were advised to use "fulltextindex".- chunk
this fields on single words and make new table with words and oids in
it. After we made this table its size was 2940360 records. And I tried
to measure the time:
Create table app_fti(string varchar(32),id oid);
fill this table with words and oid.(as it explained in
fulltextindex(update main table))
create index app_fti_idx on app_fti(string,id);
cluster app_fti_idx on app_fti;
vacuum;
vacuum analyze;
select f1.id from app_fti f1, app_fti f2 where f1.string~*'visual' and
f2.string~*'basic' and f1.id=f2.id;
this select takes about the same time that the select we used for
searching through whole original text field. So we tried slightly
different approach:
Create table app_fti(string varchar(32),id oid);
fill this table with words and oid. (as it explained in fulltextindex)
create index app_fti_idx on app_fti(string);
create index app_fti_id_idx on app_fti(id);
cluster app_fti_idx on app_fti;
vacuum;
vacuum analyze;
select f1.id from app_fti f1, app_fti f2 where f1.string~*'visual' and
f2.string~*'basic' and f1.id=f2.id;
this select takes slightly less time but not significant. And if we
would add one more word in search criteria, it would add 1 more minute
to the search time. So we can't use this for our database.
the question is can we do something to make this type of search faster?
we need to make performace of our search at least twice faster than it
is now.
original table and search we do now are:
Table "applicant"
Attribute | Type|Modifier
-+---+
hours/shift | char(70) |
position| text |
type of employment | integer |
date of applying| date | default date(now())
time of applying| time | default "time"(now())
salary | integer |
salary type | char(25) | not null default 'anually'
last name | char(20) |
first name | char(20) |
mid name| char(20) |
file reference | char(100) |
street address | text |
city| char(25) |
state | char(2) |
zip code| char(5) |
country | char(2) | default 'US'
permanent street address| text |
permanent city | char(25) |
permanent state | char(2) | default 'VA'
permanent zip code | char(5) |
permanent country | char(2) | default 'US'
home phone area code| smallint |
home phone number | integer |
home phone extension| char(10) |
alternative phone area code | smallint |
alternative phone number| integer |
alternative phone extension | char(10) |
work phone area code| smallint |
work phone number | integer |
work phone extension| char(10) |
fax area code | smallint |
fax number | bigint|
pager | bigint|
pager extension | char(10) |
e-mail | text |
url | text |
permition | boolean |
relocation | boolean |
travel | boolean |
highest grade | char(10) |
major_minor | text |
other skills| text |
reference number| bigint| default
nextval('reference'::text)
password| char(15) |
other | text |
supervisor | char(15) |
level | integer | default 7
aux1| integer |
aux2| integer |
aux3| integer |
aux4| integer |
aux5| integer |
aux6| date |
aux7| time |
it also has index on oid;
the average select is:
SELECT CASE
WHEN "other skills"~*'[^a-zA-Z]visual[^a-zA-Z]'
then 1 else 0 end +
CASE WHEN "other skills"~*'[^a-zA-Z]basic[^a-zA-Z]'
then 1 else 0 end +
CASE WHEN "other skills"~*'[^a-zA-Z]web[^a-zA-Z]'
then 1 else 0
end as "count",
"first name", "last name",
"reference number",
"date of applying", "city", "state",
"e-mail", "home phone area code",
"home phone number",
"home phone extension" from "applicant"
WHERE
Re: [SQL] Need to improve performance
> vacuum; > vacuum analyze; > select f1.id from app_fti f1, app_fti f2 where f1.string~*'visual' and > f2.string~*'basic' and f1.id=f2.id; Use ~*'^basic' It will use the indexes I believe. Also, enable likeplanning (look in contrib/likeplanning) -- it will speed things up too.. If that doesn't help then use EXPLAIN to get your query plan and post it, I'll try to help further... I'm doing this exact thing, so have some experience on tweaking it (Thanks again Tom!) :-) I'd bet what's happening is you're doing a seq scan, not something you want to do on that big of a table. (I know that's what's happening with using ~*'whatever' ) Make good use of the stop words in fti.c too (be sure to order them, it's a binary search). Hope that helps.. -Mitch
[SQL] Re: [HACKERS] query failed , don't know why
Jeff MacDonald <[EMAIL PROTECTED]> writes: > UPDATE members m,payments p SET m.status = 2 WHERE p.paydate > 'now'::datetime - '1 >month'::timespan and p.productid = 'xxx' and m.gid = p.gid > i'm trying to run that query and i'm getting > "parse error near m" > but it looks ok to me OK according to what reference? SQL92 doesn't allow anything but a simple between UPDATE and SET --- no aliases, much less multiple table names. regards, tom lane
[SQL] Re: Wildcard in date field
The suggest solution works:
> SELECT * FROM my_table WHERE
> date_part('month', col_name::datetime) = '06'
> AND date_part('year', col_name::datetime) =
> '2000';
But you can also just do a comparison:
where col_name >= '2000-06-01' AND col_name <= '2000-06-30'
Using the correct date format for your environment, of course.
Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
http://www.eudoramail.com
