Omnis variable and Postgres data type

Rob Mostyn mostyn at
Wed Mar 30 10:42:39 UTC 2022

Hi Martin,

I am going to suggest a technique that Alex has alluded to.  We have been using this table for decades now.  This may seem overkill for your current situation but it will be used many times in the future if you employ it.  I can’t even think of creating an application and not having this table ini place.  It is included in our “infra”structure library.

Create a table called referencelocal (reference is a reserved word in some databases)
It has several columns.  Ours has the following:
CREATE TABLE infra.referencelocal (
    rfl_seq bigint DEFAULT nextval('infra.sysreferencelocal_seq'::regclass) PRIMARY KEY,
    rfl_class character varying(15) NOT NULL,
    rfl_value character varying(15) NOT NULL,
    rfl_desc character varying(100) NOT NULL,
    rfl_order smallint,
    rfl_active smallint NOT NULL DEFAULT 1,
    rfl_char text,
    rfl_int bigint,
    rfl_number double precision,
    rfl_date date,
    rfl_bin bytea,
    rfl_time time without time zone,
    rfl_effective date,
    rfl_expires date,
    rfl_json jsonb,
    rfl_cwhen timestamp without time zone NOT NULL DEFAULT 'now'::text::timestamp(0) without time zone,
    rfl_cby character varying(15) NOT NULL,
    rfl_mwhen timestamp without time zone NOT NULL DEFAULT 'now'::text::timestamp(0) without time zone,
    rfl_mby character varying(15) NOT NULL,
    rfl_mcount integer NOT NULL DEFAULT 0,
    rfl_unique character varying(50) NOT NULL UNIQUE

The cwhen, cby, mwhen, mby and mcount columns are are standard accounting columns.
rfl_effective and rfl_expires allow for time sensitive values.
rfl_unique is a column to combine other columns that define uniqueness.  rfl_class and rfl_value are a minimum.  You may want to add rfl_effective if you use it.

The usage of the table is driven by rfl_class and rfl_value.
For your title example you may have these rows:

We have a method on the table class called $fetchclass.
Do ilTitles.$definefromsqlclass(’tReferenceLocal’)
Do ilTitles.$fetchclass(’TITLE_PSN’)

The list contains all the rows with rfl_class = ’TITLE_PSN’.

We use this table for all our lists of “fixed” values.  It gives the client the ability to add more entries to the list if they wish.  They can remove an entry by marking rfl_active as 0.  You store the rfl_value in your application tables while displaying rfl_desc in the user interface.
You can use it for other other site controllable parameters that can shape behaviour, which method within code is used… all sorts!  $fetchclass has an optional second parameter where you can provide the VALUE to return a row.

Rob Mostyn
mostyn at

+44 (0)20 3233 0044

As Carl Sagan once said:
One of the great commandments of science is, "Mistrust arguments from authority." ... Too many such arguments have proved too painfully wrong. Authorities must prove their contentions like everybody else.

> On 30 Mar 2022, at 10:13, Martin Obongita via omnisdev-en <omnisdev-en at> wrote:
> Hi all,I have a list of titles; Mr. Mrs. Dr. Prof. Rev.It would be inefficient to store these in a separate table.A column is sufficient.
> Which is the likely postgres data type to store an Omnis variable of type list?
> Rgds,Martin.
> _____________________________________________________________
> Manage your list subscriptions at
> Start a new message -> mailto:omnisdev-en at

More information about the omnisdev-en mailing list