PostgreSQL: how to define and use "global" constants

Question

I am writing a few stored procs that process some batch upload data. Each input line can be flagged for a variety of application errors. I have nearly 100 different types of errors in all, and over a dozen different file load procedures.

In C/C++ the idiom for error codes is a bunch of #define or const in a project-wide include (class) file and then using the symbolic names in application code. The compilers check for wayward spellings. Java/C# too offer a similar construct. How does one obtain a similar effect in plpgsql? I have toyed with setting up these in postgresql.conf but is that a sound approach? It obviously will not work at compile time. And I don't want to grant write privileges to conf files to application developers. Further, it will require a reload of conf for every application change, possibly a system stability issue. I am sure there are many other drawbacks.

In a like vein, I have also a need for plain "user-defined" types wherein I would like to fix the representation of certain application data types, such as "part_number" to be varchar(20), "currency_code" to be char(3) and so on. Again, in C/C++ one would use typedef or struct as the case might be. So I tried creating a TYPE in PostgreSQL for consistent usage across tables, views, function headers. But with the UDTs I ran into a new set of issues: specifying primary keys, and in CSV input specs where the value must now be given in parentheses. Is there a different way of dealing with such objectives in PostgreSQL?

I am new to PostgreSQL. We are using 9.2 on Linux. I am tempted to use a pre-processor but then it will not be compatible with any design tool I have seen.

How does one obtain a similar effect in plpgsql ? Using an error table? — Clodoaldo Neto
– Clodoaldo Neto, Commented Apr 4, 2013 at 19:12
what is an "error table"? If this is a postgresql feature could you possibly post a link for it? I started with a table for all error codes but realized it requires a join and, worse, this not a compile-time but a run-time check. I am trying to get out of that. thanks. — Dinesh
– Dinesh, Commented Apr 4, 2013 at 22:10

Chris Farmiloe · Accepted Answer · 2013-04-04 20:03:52Z

1

For your first question you could potentially use an ENUM type.

CREATE TYPE flag AS ENUM ('ok', 'bad', 'superbad');

Which would at least allow for sanity checking of your spellings for each of the flag states.

For your second question (and please ask multiple questions in the future - since it keeps things on topic) you might want to look at DOMAINs

answered Apr 4, 2013 at 20:03

Chris Farmiloe

14.3k5 gold badges51 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dinesh Over a year ago

Chris, thanks. I think enum will do until something better came along.

Collectives™ on Stack Overflow

PostgreSQL: how to define and use "global" constants

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related