Re: to_typemod(type_name) information function

Hi,

I need to test a (user) given column type name, with one in the database
for equality. To this end, I have to do some kind of normalization (e.g.
'timestamptz(2)' to 'timestamp (2) with time zone'.)

Comparing the name alone is possible with to_regtype(type_name) or
::regtype. However, this ignores the 'typemod'.

I want to suggest a to_typemod(type_name) function which, combined with
to_regtype, would allow to decompose (and reconstruct) a type name
completely.

Best,
Sophie

Sophie Herold <sophie_h(at)hemio(dot)de> writes:
> I need to test a (user) given column type name, with one in the database
> for equality. To this end, I have to do some kind of normalization (e.g.
> 'timestamptz(2)' to 'timestamp (2) with time zone'.)

Perhaps format_type(oid, integer) would help you.

regards, tom lane

On 18/11/17 16:50, Tom Lane wrote:
> Sophie Herold <sophie_h(at)hemio(dot)de> writes:
>> I need to test a (user) given column type name, with one in the database
>> for equality. To this end, I have to do some kind of normalization (e.g.
>> 'timestamptz(2)' to 'timestamp (2) with time zone'.)
>
> Perhaps format_type(oid, integer) would help you.
>
> regards, tom lane
>

I am not sure how. I am exactly looking for the the second argument integer.

The only workaround I can think of is to create a table with a column
with that type, ask the pg_catalog for the typemod afterwards and
rollback the creation. But that doesn't sound like a proper solution to me.

Best,
Sophie

Hi,

the following patch allows to retrieve the typemod. Without this patch,
it does not seem to be possible to generate the first column.

SELECT format_type(to_regtype(t), pg_to_typemod(t)),
format_type(to_regtype(t), NULL)
FROM (VALUES
('INTERVAL SECOND (5)'),
('Varchar(17)'),
('timestamptz (2)')) AS x(t);
format_type | format_type
-----------------------------+--------------------------
interval second(5) | interval
character varying(17) | character varying
timestamp(2) with time zone | timestamp with time zone

I did not find any advice on how to choose a new OID for pg_proc.

Best,
Sophie

Greeting, Sophie!

* Sophie Herold (sophie_h(at)hemio(dot)de) wrote:
> I did not find any advice on how to choose a new OID for pg_proc.

(Haven't looked at the patch itself yet really, but wanted to answer
this.)

The main thing is to not duplicate the OID, which you can avoid by
calling 'unused_oids' in src/include/catalog. That will then return a
list of OIDs that haven't been used yet. Generally speaking, for a case
where you only need one OID, grabbing one from any of the blocks listed
is fine, though it doesn't hurt to check and see what the nearby used
OIDs were for and if there might be some reason to keep a particular OID
free for future use (just for grouping convenience with other related
things).

Generally though, it's not something you have to worry about too much,
just try to avoid duplicating them. Even then, if you do, most likely
the committer who picks the patch up will realize it and adjust
accordingly.

Thanks!

Stephen

Sophie Herold <sophie_h(at)hemio(dot)de> writes:
> the following patch allows to retrieve the typemod. Without this patch,
> it does not seem to be possible to generate the first column.

I thought about this a bit, and I now follow the problem you want to
solve, and agree that format_type() is going in the wrong direction.
However, the proposed solution seems a bit grotty. You're basically
always going to need to run parseTypeString twice on the same input,
because there are few if any use-cases for getting just the typmod
without the type OID. I think it might be more useful to provide
a single function

parse_type(type_name text, OUT typeid regtype, OUT typmod integer)

which would replace both to_regtype and to_typemod in your example.
Usage might look like

SELECT format_type(typeid, typmod)
FROM (VALUES
('INTERVAL SECOND (5)'),
('Varchar(17)'),
('timestamptz (2)')) AS x(t), parse_type(x.t);

Creating a function with multiple OUT parameters at the pg_proc.h level
is slightly painful, but see e.g. pg_sequence_parameters for a model.

regards, tom lane

Hello,

On Tue, Nov 21, 2017 at 06:23:43PM +0100, Sophie Herold wrote:
>
> I did not find any advice on how to choose a new OID for pg_proc.
>

Just two cents in addition to Stephen's and Tom's answers. You can choose a new Oid using unused_oids script. Just do the following:

$ cd src/include/catalog
$ ./unused_oids

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

On 2018-01-06 18:45:11 -0500, Tom Lane wrote:
> Sophie Herold <sophie_h(at)hemio(dot)de> writes:
> > the following patch allows to retrieve the typemod. Without this patch,
> > it does not seem to be possible to generate the first column.
>
> I thought about this a bit, and I now follow the problem you want to
> solve, and agree that format_type() is going in the wrong direction.
> However, the proposed solution seems a bit grotty. You're basically
> always going to need to run parseTypeString twice on the same input,
> because there are few if any use-cases for getting just the typmod
> without the type OID. I think it might be more useful to provide
> a single function
>
> parse_type(type_name text, OUT typeid regtype, OUT typmod integer)
>
> which would replace both to_regtype and to_typemod in your example.
> Usage might look like
>
> SELECT format_type(typeid, typmod)
> FROM (VALUES
> ('INTERVAL SECOND (5)'),
> ('Varchar(17)'),
> ('timestamptz (2)')) AS x(t), parse_type(x.t);
>
> Creating a function with multiple OUT parameters at the pg_proc.h level
> is slightly painful, but see e.g. pg_sequence_parameters for a model.

As there's been no activity on this thread since this comment I'm
marking this returned with feedback. Sophie, are you planning to
implement something along these lines?

Greetings,

Andres Freund

Hi,

On 01/03/18 11:16, Andres Freund wrote:
> Sophie, are you planning to
> implement something along these lines?

I don't have time right now, but yes.

Best,
Sophie