Обсуждение: Variable-length Types
I'm going from the documentation in the Programmer's Guide, chapter 4.
I'd like to have the following type available in Postegres:
typedef struct FullName { char *first; char *last;
} FullName;
According to the docs, it looks like I need to do something like:
typedef struct FullName { int4 len; int first; int last; char data[1];
} FullName;
where data[] stores both first and last names (has two '\0'
terminators in it), and first and last are indexes into data[]? Is
there a better way to do this?
-itai
Folks,
I'm a bit confused on custom type declarations, actually. I'd like to
create a custom type for American phone numbers, such that:
It takes a string and returns something that looks like Varchar(22);
If someone types in 10 digits, it returns output like (###) ###-####
If a user enters 7 digits, it picks a default area code and returns
(415) ###-####,
and if they type more than 10 digits or enter any digits after an "x",
they get (###) ###-#### x###
My questions are as follows:
1. Can I reference a custom function (phoneformat) in a type definition?
2. If 1. is "no", is there a way to do the above without programming the
type in C?
3. What sort of trouble am I going to get into trying to pull data from
a custom type into an external interface (i.e. PHP4)?
Thanks for your thoughts!
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
Josh Berkus <josh@agliodbs.com> writes:
> I'm a bit confused on custom type declarations, actually. I'd like to
> create a custom type for American phone numbers, such that:
> ...
> 1. Can I reference a custom function (phoneformat) in a type definition?
Of course. The input and output converters for the new type would be
the natural place to do the reformatting. You'd probably make the input
converter do error checking, insertion of default area code, and
reduction to a pure digit string, and then make the output converter
insert the fluff data like parentheses and dashes.
However, building a new type for this seems like overkill, because you'd
also have to supply a set of functions and operators for the type. It
would be a lot less work just to provide a normalization functioninterpret_phone_no(text) returns text
which could be invoked explicitly, eginsert into tab values (..., interpret_phone_no('5551212'), ...)
or implicitly in ON INSERT and ON UPDATE triggers for the table.
> 3. What sort of trouble am I going to get into trying to pull data from
> a custom type into an external interface (i.e. PHP4)?
Good question --- the interface code might or might not have a sensible
default behavior for types it doesn't recognize.
regards, tom lane
Tom,
> However, building a new type for this seems like overkill, because you'd
> also have to supply a set of functions and operators for the type. It
> would be a lot less work just to provide a normalization function
> interpret_phone_no(text) returns text
> which could be invoked explicitly, eg
> insert into tab values (..., interpret_phone_no('5551212'), ...)
> or implicitly in ON INSERT and ON UPDATE triggers for the table.
Thanks. You're probably right ... reformatting the phone numbers is
going to be a lot less work than a custom type. Still, I can see the
usefulness of a custom type if one had the time to build the new library
of operators etc. For example, a special set of comparison operators for
phone numbers. Maybe I'll hire somebody to do it :-)
I do think I'll use triggers for ON INSERT and ON UPDATE, because it
will space me having to remember to use the function every time I handle
a phone number field. I'll post the PLSQL function after I've written
it.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
> > 3. What sort of trouble am I going to get into trying to pull data from > > a custom type into an external interface (i.e. PHP4)? > > Good question --- the interface code might or might not have a sensible > default behavior for types it doesn't recognize. Why not explicitly convert to text? (I assume the output function for that type will be used in the conversion...?). SELECT phone_num::text FROM tab -itai
Josh, > Thanks. You're probably right ... reformatting the phone numbers is > going to be a lot less work than a custom type. I remember from the old days of Delphi/InterBase, and even older days of Paradox, there were so called input masks, US phone code mask would be like (999)_000-00-00 or something like that (no default values) Borland has released their Turbo stuff for public domain, so it might be worth looking at.Implementing a generic picture functions (as in PL/I) would probably be easier and cover more cases. However, watch your back with user-supplied format strings, there's a plenty of ruined lives at BugTraq :)I'm just not sure what to do if 8 or 9 digits are supplied?Maybe, reject, assuming that such things should be caught by UI, and if we get such a weird thing, there's something really really wrong? -- Well I tried to be meek And I have tried to be mildBut I spat like a woman And I sulked like a childI have lived behind thewalls That have made me aloneStriven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
KuroiNeko, > I remember from the old days of Delphi/InterBase, and even older days of > Paradox, there were so called input masks, US phone code mask would be like Input masks still exist for some languages (VB, Paradox) but I've found that even in those platforms that support them I tend to replace them with custom functions, because actually using them is too buggy. > I'm just not sure what to do if 8 or 9 digits are supplied? Maybe, reject, > assuming that such things should be caught by UI, and if we get such a > weird thing, there's something really really wrong? Or, if you're using an interface that doesn't readily support entry validation (e.g. PHP) then you can nest functions and have the "Save" button test for a reply indicating that something's wrong. To wit: Create Function save_candidate (Lots of candidate data variables) RETURNS VARCHAR (100) ASDeclare output_string VARCHAR(100)Do a whole bunch of stuffSELECT first_phone = phoneformat(first_phone)IF first_phone= 'fail' ( SELECT output_string = 'Bad phone number for Primary Phone' RETURN output_string )More code -Josh -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Mr. Popkov, > http://www.primechoice.com/hum/uspn.c Thanks! Since I don't read C, I'll just have to compile it as a function and try it out. To repay you, I'll write the function (in PL/PGSQL) to strip out any extraneous characters that the user might have added in data entry. Soon. -Josh P.S. this makes you the first outside contributor to my open-source project ... which isn't up on the web yet! -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
> KuroiNeko, > > > I remember from the old days of Delphi/InterBase, and even older days of > > Paradox, there were so called input masks, US phone code mask would be like > > Input masks still exist for some languages (VB, Paradox) but I've found > that even in those platforms that support them I tend to replace them > with custom functions, because actually using them is too buggy. You can use CHECK column constraints to enforce this using regular expressions in the CHECK. > > > I'm just not sure what to do if 8 or 9 digits are supplied? Maybe, reject, > > assuming that such things should be caught by UI, and if we get such a > > weird thing, there's something really really wrong? > > Or, if you're using an interface that doesn't readily support entry > validation (e.g. PHP) then you can nest functions and have the "Save" > button test for a reply indicating that something's wrong. > > To wit: > > Create Function save_candidate (Lots of candidate data variables) > RETURNS VARCHAR (100) > AS > Declare output_string VARCHAR(100) > Do a whole bunch of stuff > SELECT first_phone = phoneformat(first_phone) > IF first_phone = 'fail' ( > SELECT output_string = 'Bad phone number for Primary Phone' > RETURN output_string > ) > More code > > -Josh > > -- > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 436-9166 > for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco > -- Bruce Momjian | http://candle.pha.pa.us 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
> It takes a string and returns something that looks like Varchar(22); > If someone types in 10 digits, it returns output like (###) ###-#### > If a user enters 7 digits, it picks a default area code and returns > (415) ###-####, > and if they type more than 10 digits or enter any digits after an "x", > they get (###) ###-#### x### Why not treat each group of ###s as its own variable? You would then have a lot of freedom to insert defaults if null and the like. -C. Currie