Thread: varchar, text and cidr
so i'm trying to puzzle out how to do something in a client's database.
he's using postgresql 7.3.2 on a rh 8.0 system.
he has a column with type varchar(18) containing cidr blocks (yes, i know
he should use the cidr type, but he didn't.)
in my testing, i've determined that i can cast text to cidr, but i can't
cast varchar( limit) to cidr.
i've worked out that i can do this:
select * from vctest where '192.0.2.5'::cidr << baz::text::cidr;
baz
--------------
192.0.2.0/24
but is there any particular reason why i have to jump through the
typecasting hoop twice here?
thanks,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
Richard Welty <rwelty@averillpark.net> writes:
> but is there any particular reason why i have to jump through the
> typecasting hoop twice here?
Lack of an entry in pg_cast for varchar->cidr. Feel free to create one.
regards, tom lane
On Mon, 04 Aug 2003 16:58:29 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Welty <rwelty@averillpark.net> writes:
> > but is there any particular reason why i have to jump through the
> > typecasting hoop twice here?
> Lack of an entry in pg_cast for varchar->cidr. Feel free to create one.
if i knew how to determine the oid for a data type, i certainly would.
actually, i might not. the customer is maintaining this system himself, and
mostly calling me after he breaks things. better leave the underlying
software base stock. i'll live with the doubled up typecast for now.
cheers,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
Richard Welty <rwelty@averillpark.net> writes:
> On Mon, 04 Aug 2003 16:58:29 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Lack of an entry in pg_cast for varchar->cidr. Feel free to create one.
> if i knew how to determine the oid for a data type, i certainly would.
You don't have to. See CREATE CAST.
regards, tom lane
On Mon, 04 Aug 2003 18:07:49 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Welty <rwelty@averillpark.net> writes:
> > On Mon, 04 Aug 2003 16:58:29 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Lack of an entry in pg_cast for varchar->cidr. Feel free to create
> one.
> > if i knew how to determine the oid for a data type, i certainly would.
> You don't have to. See CREATE CAST.
cool. although it does look like there's still a piece missing:
test=# create cast( varchar as cidr) with function cidr( text);
ERROR: argument of cast function must match source data type
test=# create cast( varchar as cidr) with function cidr( varchar);
ERROR: CreateCast: function cidr(character varying) does not exist
which i find interesting, since i can use the cidr funtion directly on
the varchar field:
test=# select * from vctest where '192.0.2.5'::cidr << cidr( baz);
baz
--------------
192.0.2.0/24
(1 row)
note that i'm not in any big rush to deal with this, the cast-of-a-cast is
working ok at my customer's site.
oh well,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
Richard Welty <rwelty@averillpark.net> writes:
> On Mon, 04 Aug 2003 18:07:49 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You don't have to. See CREATE CAST.
> cool. although it does look like there's still a piece missing:
> test=# create cast( varchar as cidr) with function cidr( varchar);
> ERROR: CreateCast: function cidr(character varying) does not exist
Yeah, you'd need to create a pg_proc entry for cidr(varchar) --- though
you can get away with letting it point to the implementation function
for cidr(text).
regards, tom lane
A thanks to Richard and Tom. I've been too embarrased to display my lack
of networking knowledge by asking about how to do some 'interesting'
selects on inet types. This discussion got me looking and testing and I'm
now on my way to getting the results I need.
Funny thing is I had seen this in the docs before but it never quite
clicked in.
Rod
--
"Open Source Software - You get more than you pay for..."
On Mon, 04 Aug 2003 20:07:18 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Welty <rwelty@averillpark.net> writes:
> > On Mon, 04 Aug 2003 18:07:49 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> You don't have to. See CREATE CAST.
>
> > cool. although it does look like there's still a piece missing:
>
> > test=# create cast( varchar as cidr) with function cidr( varchar);
> > ERROR: CreateCast: function cidr(character varying) does not exist
>
> Yeah, you'd need to create a pg_proc entry for cidr(varchar) --- though
> you can get away with letting it point to the implementation function
> for cidr(text).
which would be a new row looking like this one, but with a different entry
in the vector of arg types. at this point, i end up needing to know the
OIDs for the argument types to do this after all. i guess i can figure that
out from looking at other functions in the pg_proc table.
test=# select * from pg_proc where proname = 'cidr';
proname | pronamespace | proowner | prolang | proisagg | prosecdef
---------+--------------+----------+---------+----------+----------
cidr | 11 | 1 | 12 | f | f
| proisstrict | proretset | provolatile | pronargs | prorettype |
-+-------------+-----------+-------------+----------+------------+
| t | f | i | 1 | 650 |
proargtypes | prosrc | probin | proacl
-------------+-----------+--------+--------
25 | text_cidr | - | {=X}
(1 row)
test=#
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security