pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVariable-length Types
10+ messages / 6 participants
[nested] [flat]
* Variable-length Types
@ 2000-10-15 14:00 Itai Zukerman <zukerman@math-hat.com>
0 siblings, 1 reply; 10+ messages in thread
From: Itai Zukerman @ 2000-10-15 14:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-15 18:31 Josh Berkus <josh@agliodbs.com>
parent: Itai Zukerman <zukerman@math-hat.com>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2000-10-15 18:31 UTC (permalink / raw)
To: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-15 20:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2000-10-15 20:26 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-15 20:36 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2000-10-15 20:36 UTC (permalink / raw)
To: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-15 20:58 Itai Zukerman <zukerman@math-hat.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Itai Zukerman @ 2000-10-15 20:58 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-15 21:17 KuroiNeko <evpopkov@carrier.kiev.ua>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 10+ messages in thread
From: KuroiNeko @ 2000-10-15 21:17 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-16 00:39 Josh Berkus <josh@agliodbs.com>
parent: KuroiNeko <evpopkov@carrier.kiev.ua>
0 siblings, 2 replies; 10+ messages in thread
From: Josh Berkus @ 2000-10-16 00:39 UTC (permalink / raw)
To: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-16 02:10 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2000-10-16 02:10 UTC (permalink / raw)
To: KuroiNeko <evpopkov@carrier.kiev.ua>; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-16 02:22 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2000-10-16 02:22 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Variable-length Types
@ 2000-10-17 16:54 DalTech - CTE <CTE@Dal.Ca>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 10+ messages in thread
From: DalTech - CTE @ 2000-10-17 16:54 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-10-17 16:54 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-15 14:00 Variable-length Types Itai Zukerman <zukerman@math-hat.com>
2000-10-15 18:31 ` Josh Berkus <josh@agliodbs.com>
2000-10-15 20:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-15 20:36 ` Josh Berkus <josh@agliodbs.com>
2000-10-15 21:17 ` KuroiNeko <evpopkov@carrier.kiev.ua>
2000-10-16 00:39 ` Josh Berkus <josh@agliodbs.com>
2000-10-16 02:10 ` Josh Berkus <josh@agliodbs.com>
2000-10-16 02:22 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-10-15 20:58 ` Itai Zukerman <zukerman@math-hat.com>
2000-10-17 16:54 ` DalTech - CTE <CTE@Dal.Ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox