PostgreSQL - return n-sized varchar from function

Question

As I found in documentation:

Parenthesized type modifiers (e.g., the precision field for type numeric) are discarded by CREATE FUNCTION

Are there any alternatives to return varchar(N) type from plpgsql function?

question update:

On picture you can see that Name column recognised as varchar(128), however Number column is recognised as nonsized varchar

f_concat function returns: cast(res as varchar(255));

Why do you think you need that? What problem are you trying to solve? — user330315
– user330315, Commented Nov 11, 2016 at 13:52
@a_horse_with_no_name One old Delphi program see everything without length as TEXT BLOB. So if I select fields from varchar(255) column it shows correctly as varchar(255), but if I use function on that column result then result is character varying even if I make cast as varchar(255) inside the function. — Ice2burn
– Ice2burn, Commented Nov 11, 2016 at 15:46
I think the only way is a view which does an explicit cast to varchar(x) — user330315
– user330315, Commented Nov 11, 2016 at 16:30
@Ice2burn: We have an old Delphi program with exactly this problem, and we use domains as a workaround — Nick Barnes
– Nick Barnes, Commented Nov 11, 2016 at 21:37

Nick Barnes · Accepted Answer · 2016-11-11 21:20:55Z

4

You can preserve the type modifier for a function result by creating a domain. Postgres will use the underlying varchar(N) type when sending column descriptions to your client:

edited Nov 11, 2016 at 21:20

answered Nov 11, 2016 at 13:26

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - return n-sized varchar from function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related