Skip site navigation (1) Skip section navigation (2)

How to use custom functions created by my2pg.pl?

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 (view raw or flat)
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


pgsql-admin by date

Next:From: ogjunk-pgjedanDate: 2004-11-16 16:57:11
Subject: Re: cannot open segment 1 of relation .... No such file or directory
Previous:From: Christian FowlerDate: 2004-11-16 16:06:50
Subject: Re: evil characters #bfef cause dump failure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group