Thread: 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

http://www.sevensages.org/

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