From: | John Havard <enigma(at)sevensages(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Using pl/pgsql or sql for in/out functions for types |
Date: | 2001-04-09 22:55:45 |
Message-ID: | 397650000.986856945@samurai.corp.netdoor.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Charlie Derr | 2001-04-09 23:31:25 | RE: JDBC and Perl compiling problems w/ postgresql-7.1rc4 |
Previous Message | Homayoun Yousefi'zadeh | 2001-04-09 22:33:19 | JDBC and Perl compiling problems w/ postgresql-7.1rc4 |