From: | "Scott Chapman" <chappie(at)nativeseednetwork(dot)org> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | How to use custom functions created by my2pg.pl? |
Date: | 2004-11-16 16:51:48 |
Message-ID: | 35816.69.59.200.186.1100623908.squirrel@69.59.200.186 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | ogjunk-pgjedan | 2004-11-16 16:57:11 | Re: cannot open segment 1 of relation .... No such file or directory |
Previous Message | Christian Fowler | 2004-11-16 16:06:50 | Re: evil characters #bfef cause dump failure |