Thread: Using substr with user defined types
I created a type (for passwords) but I can't seem to use substr. I
suppose that's because it is my own type because the errors suggest that
there is no substr (actually it says ltrim) function for that type.
The type is a password that takes a string on input and stores it as
a DES encrypted string. In order for dump and reload to preserve the
passwords I did the following.
- On output precede the string with a colon, an otherwise invalid character - On input strip the colon if present and
storeinstead of encrypting
My problem is that to use this value I need to strip the colon off and
I can't figure out how to do that. I even wrote a second output function
called chkpass_rout but I can't seem to figure out how to access it. I
get this error when I try to use it (after creating the function in SQL.
ERROR: typeidTypeRelid: Invalid type - oid = 0
When I try to use substr I get this.
ERROR: No such function 'substr' with the specified attributes
When I try to cast the field to text I get this.
ERROR: No such function 'text' with the specified attributes
Can someone please tell me what I am missing here?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | 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.
darcy@druid.net (D'Arcy J.M. Cain) writes:
> I created a type (for passwords) but I can't seem to use substr. I
> suppose that's because it is my own type because the errors suggest that
> there is no substr (actually it says ltrim) function for that type.
Yup --- the system has no idea how to apply any functions to a
user-defined type except the ones you specifically define for that type.
If you make a type-conversion function "text(yourtype) returns text"
then the system will figure out that it should apply that function
whenever you use your type as the argument of a function that needs
text. But without such a function, the system will not assume that
it can do anything text-ish with the datatype.
regards, tom lane
Thus spake Tom Lane
> darcy@druid.net (D'Arcy J.M. Cain) writes:
> > I created a type (for passwords) but I can't seem to use substr. I
> > suppose that's because it is my own type because the errors suggest that
> > there is no substr (actually it says ltrim) function for that type.
>
> Yup --- the system has no idea how to apply any functions to a
> user-defined type except the ones you specifically define for that type.
OK, I created a function in the C file called chkpass_rout (Raw OUTput)
which does exactly what I want. I create a function like this.
create function chkpass_rout(opaque) returns opaque as '/usr/pgsql/modules/chkpass.so' language 'c';
Here is what happens.
soccer=> select chkpass_rout('hello'::chkpass);
ERROR: typeidTypeRelid: Invalid type - oid = 0
I tried various combinations of chkpass instead of opaque with no success.
> If you make a type-conversion function "text(yourtype) returns text"
> then the system will figure out that it should apply that function
> whenever you use your type as the argument of a function that needs
> text. But without such a function, the system will not assume that
> it can do anything text-ish with the datatype.
OK, I'll go try this. Thanks.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | 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.
darcy@druid.net (D'Arcy J.M. Cain) writes:
> create function chkpass_rout(opaque)
> returns opaque
> as '/usr/pgsql/modules/chkpass.so'
> language 'c';
> Here is what happens.
> soccer=> select chkpass_rout('hello'::chkpass);
> ERROR: typeidTypeRelid: Invalid type - oid = 0
Functions that you intend to invoke as ordinary functions shouldn't have
inputs or outputs declared "opaque", because the expression evaluation
code won't have any idea what to do. When you are building functions
that will be the input or output converters for a datatype, you can read
"opaque" as meaning "C string", so for example the input converter takes
opaque and returns your type. But otherwise you don't want to be using
opaque. Perhaps what you wanted here was
"create function chkpass_rout(chkpass) returns text".
I'd like to see "opaque" eliminated from Postgres, because it's
unhelpfully named and is used to cover several distinct purposes that
would be better served with distinct names. Might create too much of
a backwards compatibility problem though :-(
regards, tom lane
> I'd like to see "opaque" eliminated from Postgres, because it's > unhelpfully named and is used to cover several distinct purposes that > would be better served with distinct names. Might create too much of > a backwards compatibility problem though :-( I was just thinking about that last night in relation to my book. Opaque is a weird name. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Tom Lane
> darcy@druid.net (D'Arcy J.M. Cain) writes:
> > create function chkpass_rout(opaque)
> > returns opaque
> > as '/usr/pgsql/modules/chkpass.so'
> > language 'c';
>
> > Here is what happens.
>
> > soccer=> select chkpass_rout('hello'::chkpass);
> > ERROR: typeidTypeRelid: Invalid type - oid = 0
>
> Functions that you intend to invoke as ordinary functions shouldn't have
> inputs or outputs declared "opaque", because the expression evaluation
> code won't have any idea what to do. When you are building functions
> that will be the input or output converters for a datatype, you can read
> "opaque" as meaning "C string", so for example the input converter takes
> opaque and returns your type. But otherwise you don't want to be using
> opaque. Perhaps what you wanted here was
> "create function chkpass_rout(chkpass) returns text".
OK, I tried this.
load '/usr/pgsql/modules/chkpass.so';
--
-- Input and output functions and the type itself:
--
create function chkpass_in(opaque) returns opaque as '/usr/pgsql/modules/chkpass.so' language 'c';
create function chkpass_out(opaque) returns opaque as '/usr/pgsql/modules/chkpass.so' language 'c';
create type chkpass ( internallength = 16, externallength = 13, input = chkpass_in, output = chkpass_out
);
create function raw(chkpass) returns text as '/usr/pgsql/modules/chkpass.so', 'chkpass_rout' language 'c';
Then I did this.
darcy=> select 'hello'::chkpass;
?column?
--------------
:Rd1xqQo0.2V6.
(1 row)
darcy=> select raw('hello'::chkpass);
pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally
before or while processing the request.
I added an fprintf to stderr as the first statement in chkpass_rout() which
doesn't print so I am pretty sure it isn't my function. The same thing
happens if I create a table with a chkpass type. Any ideas?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | 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.
darcy@druid.net (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...
regards, tom lane
Thus spake Tom Lane > darcy@druid.net (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* darcy@druid.net* http://www.druid.net/darcy/**$Id$* best viewed with tabs set to 4*/ #include <stdio.h> #include <string.h> #include <time.h> #include <unistd.h> #include <postgres.h> #include <utils/palloc.h> /** This type encrypts it's input unless the first character is a colon.* The output is the encrypted form with a leadingcolon. The output* format is designed to allow dump and reload operations to work as* expected without doing specialtricks.*/ /** This is the internal storage format for CHKPASSs.* 15 is all I need but add a little buffer*/ typedef struct chkpass {char password[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 */ bool chkpass_eq(chkpass * a1, text * a2); bool chkpass_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;char mysalt[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); returnNULL;} 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; /* technicallythe 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) {char str[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) {char str[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 <darcy@{druid|vex}.net> | 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.
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
darcy@druid.net (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
"Mitch Vincent" <mitch@venux.net> 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
"Mitch Vincent" wrote: >I need to order search results by the two dates. Here is the problem.. > >They want whichever
dateis 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
tobe above C..
select * from table order by case when resubmitted > created then resubmitted else created
end desc;
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
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
notdepart from it." Proverbs 22:6
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 <darcy@{druid|vex}.net> | 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.