PostgreSQL create type PL/pgSQL and cstring
I wanna format some fields in the output of my PostgreSQL 9.1 database. I thought of creating a type, so I could do the formatting in the output function, and checking for inconsistencies in the input function. I decided to use the procedural language PL/pgSQL. But I'm getting some errors:
CREATE OR REPLACE FUNCTION "CPF_in"(cstring) "PL/pgSQL functions cannot accept type cstring"
(But that's how it is in the manual.) I can put "character varying" instead of cstring, or even leave the () empty. But when I'm going to create the desired type:
CREATE TYPE Tcpf ( INPUT = CPF_in(character varying), OUTPUT = CPF_out );
I got an error:
ERROR: syntax error at or near ")" LINE 2: INPUT = CPF_in(character varying),
and if I try
CREATE TYPE Tcpf ( INPUT = CPF_in(), OUTPUT = CPF_out );
ERROR: syntax error at or near ")" LINE 2: INPUT = CPF_in(),
How is this supposed to be done? The manual only say cstring...
You must register two or more functions (using CREATE FUNCTION) before defining the type. The support functions input_function and output_function are required . . . . Generally these functions have to be coded in C or another low-level language.
A simpler way to control the output format is to use a view. If your formatting is complex, write a function, and call that function from a view. You can revoke permissions on the base table if you need to force every client to use your formatting. You might need to create triggers to make your view fully updatable.
For controlling input, you can use a function. (CREATE FUNCTION...) You can write functions in PL/pgSQL. Again, consider revoking permissions on the table.