Thread: 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@hemio.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@hemio.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
Attachment
Greeting, Sophie! * Sophie Herold (sophie_h@hemio.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@hemio.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 thefollowing: $ 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@hemio.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