[SQL] 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
Re: [SQL] Variable-length Types
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 information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Regular expression query
> Rodger Donaldson <[EMAIL PROTECTED]> writes:
> > SELECT url
> > FROM sites
> > WHERE url ~ url || '\\s+'
>
> > While this concatenation works with the LIKE directive (ie LIKE url || '%'),
> > postgresql barfs on it in a regexp with the error:
>
> > ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown'
> > You will have to retype this query using an explicit cast
>
> LIKE and ~ do not have the same precedence. See
> http://www.postgresql.org/docs/postgres/operators.htm.
> ~ and || actually fall in the same category ("all other") and therefore
> are grouped left-to-right; so you're getting (url ~ url) || '...'.
>
> > The other aspect of this is that it seems that postgresql's regexp engine
> > doesn't understand some expected regexps; I've tried both escaped and
> > unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them.
>
> The regexp package we currently use implements POSIX 1003.2 regexps
> (see src/backend/regex/re_format.7). I believe there is an item on the
> TODO list about upgrading the regexp parser to something more modern
> ... feel free to hop on that project if it's bugging you ...
I bug Henry Spencer regularly. His new code is in TCL/TK, but has not
been released into any other code.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Variable-length Types
Josh Berkus <[EMAIL PROTECTED]> 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 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.
> 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
Re: [SQL] Variable-length Types
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 information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Variable-length Types
> > 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
Re: [SQL] Variable-length Types
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 mild But I spat like a woman And I sulked like a child I have lived behind the walls That have made me alone Striven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
Re: [SQL] Variable-length Types
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) 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 [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Variable-length Types
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 information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Variable-length Types
> 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 [EMAIL PROTECTED] > 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] many-many mapping between unique tables
Hi, I am facing a problem in mapping between two tables containing unique entries T1 T2 _ | x1 | | y1 | | x2 | | y2 | | x3 | | y3 | - - x(i) points to 1 or more entries in T2. y(i) points to one or more entries in T1. How do I store this mapping? I'd cannot use an array due to size restrictions and inability to extract data easily for furthur processing. I don't want to put everything into a single table and repeat values in the 2nd column since these are really huge tables and I cannot increase overhead by increasing data redundancy. Is there some way to do something about this? \Indraneel /. # Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `/
