Subject: Please HELP

From: [email-masked]
Date: Fri, 25 Feb 94 19:58:26 +0800

Dear ALL,

	Can anyone kindly tell me how to define an user type
with 256 char ?
	POSTRGES manual tells me I need to first define
the input and output functions first ---- however, I am
not smart enough to understand how to define these functions.
Can anyone give me a helping hand ?  THANK YOU

Regards,
Keith Law

---

From: Marcel Kornacker <[email-masked]>
Date: Fri, 25 Feb 1994 17:36:27 -0800 (PST)

> Can anyone give me a helping hand ?  THANK YOU

There are two things you have to do to add a new type:
	- make the type, its operators etc. known to postgres
	- write the supporting code for them (in C)

Here is what a script for the first step could look like:
(the syntax might have changed from the last release)

/* in, out, and type decls for char256 */
define function char256in (language = "c", returntype = char256) arg is (any)
  as "char256.o"\g
define function char256out (language = "c", returntype = char16) arg is (any)
  as "char256.o"\g
define type char256 (internallength = 256, input = char256in, output = char256out)\g

/* functions and operators for char256 */
define function char256lt (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256le (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256eq (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256ne (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256ge (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256gt (language = "c", returntype = bool)
    arg is (char256, char256) as "char256.o"\g
define function char256cmp (language = "c", returntype = int4)
    arg is (char256, char256) as "char256.o"\g

define operator < (arg1 = char256, arg2 = char256, procedure = char256lt,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g
define operator <= (arg1 = char256, arg2 = char256, procedure = char256le,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g
define operator = (arg1 = char256, arg2 = char256, procedure = char256eq,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g
define operator != (arg1 = char256, arg2 = char256, procedure = char256ne,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g
define operator >= (arg1 = char256, arg2 = char256, procedure = char256ge,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g
define operator > (arg1 = char256, arg2 = char256, procedure = char256gt,
		   associativity = left, restrict = intltsel,
		   join = intltjoinsel)\g

replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = "<" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = ">" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = ">=" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g
replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = "<=" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = ">=" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = ">" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g
replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = "=" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = "=" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = "!=" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g
replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = "!=" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = "!=" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = "=" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g
replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = ">=" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = "<=" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = "<" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g
replace o (oprcom = com.oid, oprnegate = neg.oid)
    from o in pg_operator, com in pg_operator, neg in pg_operator, t in pg_type
    where o.oprname = ">" and o.oprleft = t.oid and o.oprright = t.oid
	and com.oprname = "<" and com.oprleft = t.oid and com.oprright = t.oid
	and neg.oprname = "<=" and neg.oprleft = t.oid and neg.oprright = t.oid
	and t.typname = "char256"\g

/*
 *  Okay, by here, all the code is known.  Now we want to define
 *  operator classes for the btree access method, so that values
 *  of the new types can be indexed.
 */

/* set up opclass for char256 values */
append pg_opclass (opcname = "char256_ops")\g
append pg_amop (amopid = am.oid, amopclaid = oc.oid, amopopr = op.oid,
		amopstrategy = 1, amopselect = "btreesel",
		amopnpages = "btreenpage")
    from am in pg_am, oc in pg_opclass, op in pg_operator, t in pg_type
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and op.oprname = "<" and op.oprleft = t.oid and op.oprright = t.oid
	and t.typname = "char256"\g
append pg_amop (amopid = am.oid, amopclaid = oc.oid, amopopr = op.oid,
		amopstrategy = 2, amopselect = "btreesel",
		amopnpages = "btreenpage")
    from am in pg_am, oc in pg_opclass, op in pg_operator, t in pg_type
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and op.oprname = "<=" and op.oprleft = t.oid and op.oprright = t.oid
	and t.typname = "char256"\g
append pg_amop (amopid = am.oid, amopclaid = oc.oid, amopopr = op.oid,
		amopstrategy = 3, amopselect = "btreesel",
		amopnpages = "btreenpage")
    from am in pg_am, oc in pg_opclass, op in pg_operator, t in pg_type
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and op.oprname = "=" and op.oprleft = t.oid and op.oprright = t.oid
	and t.typname = "char256"\g
append pg_amop (amopid = am.oid, amopclaid = oc.oid, amopopr = op.oid,
		amopstrategy = 4, amopselect = "btreesel",
		amopnpages = "btreenpage")
    from am in pg_am, oc in pg_opclass, op in pg_operator, t in pg_type
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and op.oprname = ">=" and op.oprleft = t.oid and op.oprright = t.oid
	and t.typname = "char256"\g
append pg_amop (amopid = am.oid, amopclaid = oc.oid, amopopr = op.oid,
		amopstrategy = 5, amopselect = "btreesel",
		amopnpages = "btreenpage")
    from am in pg_am, oc in pg_opclass, op in pg_operator, t in pg_type
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and op.oprname = ">" and op.oprleft = t.oid and op.oprright = t.oid
	and t.typname = "char256"\g
append pg_amproc (amid = am.oid, amopclaid = oc.oid, amproc = p.oid,
		  amprocnum = 1)
    from am in pg_am, oc in pg_opclass, p in pg_proc
    where am.amname = "btree" and oc.opcname = "char256_ops"
	and p.proname = "char256cmp"\g

The supporting C functions would look something like this:

#include "tmp/postgres.h"	/* postgres system include file */
#include "utils/log.h"		/* for logging postgres errors */

#include <strings.h>

#include "tmp/postgres.h"

#include "utils/palloc.h"

/*
 *	char256in	- converts "..." to internal reprsentation
 *
 */
char *
char256in(s)
	char	*s;
{
	char	*result;
	int      i;
	if (s == NULL)
		return(NULL);
	result = (char *) palloc(256);
	bzero(result, 256);
	(void) strncpy(result, s, 256);
	return(result);
}

/*
 *	char256out	- converts internal reprsentation to "..."
 */
char *
char256out(s)
	char	*s;
{
	char	*result = (char *) palloc(257);

	bzero(result, 257);
	if (s == NULL) {
		result[0] = '-';
	} else {
		strncpy(result, s, 256);
	}
	return(result);
}

int32
char256eq(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);
    return(strncmp(arg1, arg2, 256) == 0);
}

int32
char256ne(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);
    return(strncmp(arg1, arg2, 256) != 0);
}

int32
char256lt(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);
    return((int32) (strncmp(arg1, arg2, 256) < 0));
}

int32
char256le(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);
    return((int32) (strncmp(arg1, arg2, 256) <= 0));
}

int32
char256gt(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);

    return((int32) (strncmp(arg1, arg2, 256) > 0));
}

int32
char256ge(arg1, arg2)
    char	*arg1, *arg2;
{
    if (arg1 == NULL || arg2 == NULL)
	return((int32) 0);

    return((int32) (strncmp(arg1, arg2, 256) >= 0));
}

Make sure that "char256.o" is in your $PGDATA/base/<dbname> directory.