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
);

I get

ERROR:  syntax error at or near ")"
LINE 2: INPUT = CPF_in(),

How is this supposed to be done? The manual only say cstring...

Answers


The cstring pseudo-type is used for programming in a low-level language like C, not in PL/pgSQL. You have to use a low-level language like C if you're creating a new base type.

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.


Need Your Help

Getting a 'The data types nvarchar(max) and ntext are incompatible in the equal to operator.'

sql-server django python-3.x

I am trying to populate a table with data and am using Django's get_or_create method. Whenever I do this it will enter records into the database but at a certain record it will throw the above erro...

Generate password reset token through UUID

java base64 uuid

I am looking for mechanism to generate random unique alpha numeric key for resetting user password.