[SQL] defining and using constants in postgreSQL

[Neil Zanella]

Hello,

I am looking for a way that I can define a constant in PostgreSQL (so that
I end up with a constant similar to one that I could have in Oracle). I have
searched the archives and it seems that the solutions that were suggested in
include creating a table of constants or alternatively creating a function
that returns a constant. However, I would like to have a predefined constant
in the global scope which I can access without the round brackets used to
call a function. That is, I would like to be able to define a constant
named foo that I can then access with the name foo, not foo().

Is this possible at all with PostgreSQL?

I know PostgreSQL has some predefined constants that don't take the round
brackets so in principle it should be possible to define more. Is there a
way to do such thing in PostgreSQL?

Thanks,

Neil

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior

[Neil Zanella]

Hello,

Consider the following code run under PostgreSQL 7.3.4:

CREATE TABLE X (
  A INT,
  B INT,
  PRIMARY KEY (A, B)
);

CREATE TABLE Y (
  A INT,
  B INT,
  C INT,
  PRIMARY KEY (C),
  FOREIGN KEY (B, A) REFERENCES X
);

INSERT INTO X (A, B) VALUES (1, 2);
INSERT INTO Y (A, B, C) VALUES (1, 2, 3);

The second insert causes the database server to report the following
error:
ERROR:  $1 referential integrity violation
- key referenced from y not found in x

Upon examining the interpretation of PostgreSQL's
REFERENCES clause I find the following:

foodb=# \d x
   Table "public.x"
 Column |  Type   | Modifiers
+-+---
 a  | integer | not null
 b  | integer | not null
Indexes: x_pkey primary key btree (a, b)

foodb=# \d y
   Table "public.y"
 Column |  Type   | Modifiers
+-+---
 a  | integer |
 b  | integer |
 c  | integer | not null
Indexes: y_pkey primary key btree (c)
Foreign Key constraints: $1 FOREIGN KEY (b, a)
  REFERENCES x(a, b) ON UPDATE NO ACTION ON DELETE NO ACTION

It is as though PostgreSQL, instead of matching names, associated

field A in table Y with field B in table X
and
field B in table Y with field A in table X

whereas I was expecting the database server to match the names as in:

field A in table Y with field A in table X
and
field B in table Y with field B in table X

I wonder what the SQL standard has to say on this one and how the
REFERENCES clause with no field names on the right hand side
really works in spite of the unexpected results produced by
this very simple example...

Thanks,

Neil

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org