How to use custom functions created by my2pg.pl?

[Scott Chapman]

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','ed it','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_acc essright_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_accessrigh t_admin,set_acc essright_admin)
RETURNS bool
AS '/tmp/libtypes.so'
LANGUAGE 'c';

CREATE FUNCTION find_in_set (set_accessrigh t_admin,set_acc essright_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('access right_accessrig ht_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','h ome');
INSERT INTO accessright VALUES (2,1,'visit','l ogin');
INSERT INTO accessright VALUES (3,1,'visit','g etimage');
INSERT INTO accessright VALUES (4,5,'visit','c ron');
INSERT INTO accessright VALUES (5,1,'visit','a dmin');
INSERT INTO accessright VALUES (6,1,'visit','u tility');
INSERT INTO accessright VALUES (7,1,'visit','g lobal');
INSERT INTO accessright VALUES (8,1,'visit','a bout');
INSERT INTO accessright VALUES (9,1,'visit','r egistration');
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('accessr ight_accessrigh t_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_admi n

public | set_accessright _admin_eq | boolean
| set_accessright _admin, set_a
ccessright_admi n

TIA,
Scott
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org