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','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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org