Thread: getting inet out of char?
Hi,
Since everyone here has been so helpful in the past (even when I recently
overlooked something obvious in The Fine Material), I wonder if I might get
a pointer about what to do in this case.
I have a table mapping names to ips; the access to users is through PHP3.
Now, PHP's module to PostgreSQL does not know about the data type inet. I
could just forget about it, but it seems to me the inet data type offers a
number of advantages for easy data extraction. So, I thought the answer
would be simple: I created two tables.
Table "name_and_ip"
Attribute | Type | Modifier
-----------+-------------+----------
name | varchar(30) | not null
ip | char(15) | not null
Indices: name_and_ip_ip_key,
name_and_ip_name_key
Table "name_and_ip_v4"
Attribute | Type | Modifier
-----------+-------------+----------
name | varchar(30) | not null
ip | inet | not null
Indices: name_and_ip_v4_ip_key,
name_and_ip_v4_name_key
And I thought to have a rule:
create rule name_ip_update as on update to name_and_ip do insert into
name_and_ip_v4(name,ip) values (new.name, new.ip::inet);
ERROR: Cannot cast type 'bpchar' to 'inet'
Is there something else I can do? (Having followed the recent discussion on
rules and triggers, I thought a rule was what I wanted.)
Thanks,
A
--
Andrew Sullivan Computer Services
<sullivana@bpl.on.ca> Burlington Public Library
+1 905 639 3611 x158 2331 New Street
Burlington, Ontario, Canada L7R 1J4
Andrew Sullivan <sullivana@bpl.on.ca> writes:
> I have a table mapping names to ips; the access to users is through PHP3.
> Now, PHP's module to PostgreSQL does not know about the data type inet. I
> could just forget about it, but it seems to me the inet data type offers a
> number of advantages for easy data extraction.
Seems like the cleanest answer would be to teach PHP about inet data
type (not to mention other extension types). I've got no idea what
that would take, but ...
> create rule name_ip_update as on update to name_and_ip do insert into
> name_and_ip_v4(name,ip) values (new.name, new.ip::inet);
> ERROR: Cannot cast type 'bpchar' to 'inet'
> Is there something else I can do? (Having followed the recent discussion on
> rules and triggers, I thought a rule was what I wanted.)
You could create a C-coded function to do the transformation, or you
could replace the rule with a trigger coded in pltcl or plperl. I
believe both the pltcl and plperl languages are basically datatype-
ignorant --- as long as the textual output from one data value looks
like the format the input routine for another type is expecting,
it'll work. SQL rules and plpgsql functions are far more anal-
retentive about type checking. Sometimes that's good, sometimes not
so good.
regards, tom lane
On Wed, Jun 07, 2000 at 12:20:23AM -0400, Tom Lane wrote:
> Seems like the cleanest answer would be to teach PHP about inet data
> type (not to mention other extension types).
I'd like to do that, but I don't even know where to begin. I gather that
something like this is still being worked on (although I guess it will get
integrated with PHP4, rather than 3, and I'm not in a hurry to upgrade).
Anyway, it'd probably take more than my meagre skills could do.
> You could create a C-coded function to do the transformation, or you
> could replace the rule with a trigger coded in pltcl or plperl. I
> believe both the pltcl and plperl languages are basically datatype-
> ignorant --- as long as the textual output from one data value looks
> like the format the input routine for another type is expecting,
> it'll work.
Thanks. At least this isn't one of those cases where I overlooked something
really obvious!
A
--
Andrew Sullivan Computer Services
<sullivana@bpl.on.ca> Burlington Public Library
+1 905 639 3611 x158 2331 New Street
Burlington, Ontario, Canada L7R 1J4