Using pl/pgsql or sql for in/out functions for types
I was bored, so I decided to attempt to create a new type under postgres.
I figured a type for a social security number would be easy. Sure enough,
to_char and to_number make this extremely easy.
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';
blah=> CREATE TYPE ssn (INPUT = ssn_in, OUTPUT = ssn_out);
ERROR: TypeCreate: function 'ssn_in(opaque)' does not exist
blah=>
Since sql functions can't have opaque arguments, I decided to attempt to
reimplement the functions in plpgsql...
CREATE FUNCTION ssn_out(opaque) RETURNS char AS '
BEGIN
RETURN SELECT to_char($1, \'000-00-0000\')::char;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION ssn_in (opaque) RETURNS numeric AS '
BEGIN
RETURN SELECT to_number($1, \'000 00 0000\');
END;
' LANGUAGE 'plpgsql';
Creating the type works.
blah=> CREATE TYPE ssn ( INPUT = ssn_in, OUTPUT=ssn_out);
CREATE
And then a table is created...
blah=> CREATE TABLE foobar (id int, bigbrother ssn);
CREATE
Now, the fun part is when it comes time to insert some data.
blah=> INSERT INTO foobar values (1, '123-45-5555');
NOTICE: plpgsql: ERROR during compile of ssn_in near line 0
ERROR: plpgsql functions cannot take type "opaque"
Is there anyway to do this without having to resort to writing the
functions in C or some other language? Why doesn't CREATE FUNCTION
complain about plpgsql functions not being able to accept the opaque type
as an argument?
Regards,
John Havard
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.
There has been some talk of promoting "C string" to be at least a
second-class SQL datatype (on the order of being able to declare
functions that take or return them, but not use them as a column
datatype). Hasn't happened yet though.
Why doesn't CREATE FUNCTION complain about plpgsql functions not being
able to accept the opaque type as an argument?
Because it makes no assumptions about what abilities PL languages might
have. The PLs are plug-ins, remember.
regards, tom lane
--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