How to use custom functions created by my2pg.pl?
От | Scott Chapman |
---|---|
Тема | How to use custom functions created by my2pg.pl? |
Дата | |
Msg-id | 35816.69.59.200.186.1100623908.squirrel@69.59.200.186 обсуждение исходный текст |
Список | pgsql-admin |
The my2pg.pl script creates custom functions that help with the MySQL "set" column type. I can't figure out how to use the functions once I have the database migrated into Postgres. Can someone please explain how to make use of them? In MySQL, the table exists: CREATE TABLE accessright ( accessright_id int(10) unsigned NOT NULL auto_increment, entity_ptr int(10) unsigned NOT NULL default '0', rights set('admin','edit','visit') default NULL, ar_area_key varchar(60) default NULL, PRIMARY KEY (accessright_id), KEY entity_index (entity_ptr), KEY rights_index (rights), KEY area_key_index (ar_area_key) ) TYPE=MyISAM; You can: SELECT rights+0 FROM accessright WHERE condition; ... and get the numeric bitmask back (1,2, or 4) in the above table's case. I need to be able to do the same thing in the Postgres version of this. Here's the relevant code and data structure created by the my2pg script: CREATE SEQUENCE accessright_accessright_id_s; CREATE FUNCTION set_accessright_admin_in (opaque) RETURNS set_accessright_admin AS '/tmp/libtypes.so' LANGUAGE 'c'; CREATE FUNCTION set_accessright_admin_out (opaque) RETURNS opaque AS '/tmp/libtypes.so' LANGUAGE 'c'; CREATE TYPE set_accessright_admin ( internallength = 2, input = set_accessright_admin_in, output = set_accessright_admin_out ); CREATE FUNCTION set_accessright_admin_eq (set_accessright_admin,set_accessright_admin) RETURNS bool AS '/tmp/libtypes.so' LANGUAGE 'c'; CREATE FUNCTION find_in_set (set_accessright_admin,set_accessright_admin) RETURNS bool AS '/tmp/libtypes.so' LANGUAGE 'c'; CREATE OPERATOR = ( leftarg = set_accessright_admin, rightarg = set_accessright_admin, commutator = =, procedure = set_accessright_admin_eq ); CREATE OPERATOR <> ( leftarg = set_accessright_admin, rightarg = set_accessright_admin, commutator = <>, negator = =, procedure = set_accessright_admin_eq ); CREATE TABLE accessright ( accessright_id INT4 DEFAULT nextval('accessright_accessright_id_s'), entity_ptr INT4 NOT NULL DEFAULT '0', rights set_accessright_admin DEFAULT NULL, ar_area_key varchar(60) DEFAULT NULL, PRIMARY KEY (accessright_id), CHECK (entity_ptr>=0) ); INSERT INTO accessright VALUES (1,1,'visit','home'); INSERT INTO accessright VALUES (2,1,'visit','login'); INSERT INTO accessright VALUES (3,1,'visit','getimage'); INSERT INTO accessright VALUES (4,5,'visit','cron'); INSERT INTO accessright VALUES (5,1,'visit','admin'); INSERT INTO accessright VALUES (6,1,'visit','utility'); INSERT INTO accessright VALUES (7,1,'visit','global'); INSERT INTO accessright VALUES (8,1,'visit','about'); INSERT INTO accessright VALUES (9,1,'visit','registration'); INSERT INTO accessright VALUES (10,1,'visit','focus_lists'); INSERT INTO accessright VALUES (11,1,'visit','tracking'); INSERT INTO accessright VALUES (12,1,'visit','marketplace'); INSERT INTO accessright VALUES (13,1,'visit','profile'); INSERT INTO accessright VALUES (14,1,'visit','registration'); INSERT INTO accessright VALUES (15,1,'visit','resources'); INSERT INTO accessright VALUES (16,1,'visit','private'); SELECT SETVAL('accessright_accessright_id_s',(select case when max(accessright_id)>0 then max(accessright_id)+1 else 1 end from accessright)); Here's the relevant output from \df in psql: Schema | Name | Result data type | Argument data types public | find_in_set | boolean | set_accessright_admin, set_a ccessright_admin public | set_accessright_admin_eq | boolean | set_accessright_admin, set_a ccessright_admin TIA, Scott
В списке pgsql-admin по дате отправления: