Thread: Using substr with user defined types

Using substr with user defined types

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Using substr with user defined types

From
Tom Lane
Date:
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


Re: Using substr with user defined types

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Using substr with user defined types

From
Tom Lane
Date:
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


Re: Using substr with user defined types

From
Bruce Momjian
Date:
> 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
 


Re: Using substr with user defined types

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Using substr with user defined types

From
Tom Lane
Date:
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


Re: Using substr with user defined types

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Orderby two different columns

From
"Mitch Vincent"
Date:
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: Using substr with user defined types

From
Tom Lane
Date:
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


Re: Orderby two different columns

From
Tom Lane
Date:
"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


Re: Orderby two different columns

From
"Oliver Elphick"
Date:
"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 
 




Re: Using substr with user defined types

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.