--On Friday, April 13, 2001 12:50:12 PM -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:
> John Havard <enigma@sevensages.org> writes:
>> Is there anyway to do this without having to resort to writing the
>> functions in C or some other language?
>
> You really cannot write datatype I/O functions in anything but C,
> because the I/O functions have to deal in C-style strings, which are
> not a SQL datatype; so there is no way to describe the necessary
> behavior in any PL language.
>
After thinking for less than a minute, I realized I can use views and rules
to do this. Much easier than doing it in C, especially seeing as how all
I'm doing is just formatting another type.
First, create a table:
CREATE TABLE foo (asdf int, bb numeric(9));
Then, create the necessary funtions:
CREATE FUNCTION ssn_in (char) RETURNS numeric(9) AS '
SELECT to_number($1, \'000 00 0000\')
' LANGUAGE 'sql';
CREATE FUNCTION ssn_out(numeric(9,0)) RETURNS char AS '
SELECT to_char($1, \'000-00-0000\')::char
' LANGUAGE 'sql';
Next we need a view:
CREATE VIEW blah AS select asdf, ssn_out(bb) from foo;
After that, create a rule
CREATE RULE blah_insert as on INSERT TO blah DO INSTEAD insert into foo
values (NEW.asdf, ssn_in(NEW.bb));
Then, just do inserts and selects as normal. Well, there is a problem.
When I query by the ssn field (bb) on the view, but get nothing returned.
Any ideas? EXPLAIN says it simply does a sequential scan on foo.
Also, on a different note, is there any particular reason why tab
completion for views doesn't work in psql? It sure would make me a happier
person, especially with having wasted 1.5 hours of my life, and a few bucks
on some pepperoni pizza croiscant pockets that burned last night because
3com moved and hid things on their web site (thanks 3com!).
Regards,
John Havard
