PostgreSQL create type PL/pgSQL and cstring

Question

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...

I've thought that a word already in the title of the question didn't need to be in the tags...? — Rodrigo
– Rodrigo, Commented Feb 1, 2014 at 21:07

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-02-01 15:37:41Z

4

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.

edited Feb 1, 2014 at 15:37

answered Feb 1, 2014 at 14:59

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodrigo Over a year ago

Thanks, it worked. I'm calling the function right from the select, though, but it's fine.

Collectives™ on Stack Overflow

PostgreSQL create type PL/pgSQL and cstring

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related