pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Chapman <chappie@nativeseednetwork.org>
To: pgsql-admin@postgresql.org
Subject: How to use custom functions created by my2pg.pl?
Date: Tue, 16 Nov 2004 08:51:48 -0800 (PST)
Message-ID: <35816.69.59.200.186.1100623908.squirrel@69.59.200.186> (raw)

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




Message-ID: <35816.69.59.200.186.1100623908.squirrel@69.59.200.186>
Permalink:  ../35816.69.59.200.186.1100623908.squirrel@69.59.200.186/
Also on:    postgresql.org/message-id/35816.69.59.200.186.1100623908.squirrel@69.59.200.186

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: chappie@nativeseednetwork.org
  Subject: Re: How to use custom functions created by my2pg.pl?
  In-Reply-To: <35816.69.59.200.186.1100623908.squirrel@69.59.200.186>
  References: <35816.69.59.200.186.1100623908.squirrel@69.59.200.186>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox