PostgreSQL - Modify CREATE TABLE syntax in an extension

Question

Is it possible to write an PostgreSQL extension that modifies the DDL syntax?

I'm creating an extension on top of PostgreSQL and PostGIS to support integrity constraints of a specific spatial data model (OMT-G). For that, I want to modify the CREATE TABLE syntax, which accept CONSTRAINTS with this syntax:

CONSTRAINT constraint_name CHECK ( expression )

But I want to create my own syntax, like those in the following example, which will then call functions or triggers I have written already.

CREATE TABLE school_district (
      id integer PRIMARY KEY,
      school_name varchar(120)
      geom geometry,

      SPATIAL_CONSTRAINT PLANAR_SUBDIVISION (geom),
      SPATIAL_CONSTRAINT CONTAINS school (geom)**
);

Is that possible? If so, how?

No, that is not possible. But if you would provide a real example of what you really want to achieve, there might be some alternative solution. — Patrick
– Patrick, Commented Feb 26, 2016 at 19:22
Just use the CHECK syntax. That should accomplish what you want. — Gordon Linoff
– Gordon Linoff, Commented Feb 26, 2016 at 19:34
@Patrick, this is actually what I want to achieve. Add some syntax sugars to make it easy for spatial developers. The second constraint of the snipped above, for example, will attest that for every new school_district added to the table, a school (in table school) inside its borders must exist. — lizardo
– lizardo, Commented Feb 26, 2016 at 19:42
@GordonLinoff But the idea is to create an extension for this spatial data model. So it would be nice to have an syntax like above only for it. And the constraints are a little bit complicated, with many functions, so this would simplify the use if it is possible. — lizardo
– lizardo, Commented Feb 26, 2016 at 19:46
It's still not possible. If you want to extend the syntax you have to modify the source code and write the functionality directly in C. Probably not a very feasible idea... If you have many repetitive constraints and wish to streamline that, look into domains — Patrick
– Patrick, Commented Feb 26, 2016 at 19:52

Jim Nasby · Accepted Answer · 2016-02-26 20:01:35Z

4

As others have commented, it's not possible to alter Postgres grammar via an extension. There's been some discussion related to this on the hacker's mailing list, but no one sees any feasible way to make the Bison grammar extensible.

The other problem with what you're proposing is that CHECK constraints (which it appears is what you're trying to do here) can not safely reference other tables.

It sounds like what you really want here is extensible foreign key support. That's something the community would actually like as well, at least for arrays. The idea is to support something like int[], where each element should be treated as a foreign key reference to another table. What you're describing is similar: instead of a different data type, you want to use a different operator.

For now though, I think the best you could do is to provide your users a function that will put an appropriate column and trigger on the table. The column would be a foreign key to the school table. The trigger would find an appropriate school and populate it's primary key in the new column. (You'd probably want a BEFORE DELETE trigger on the school table to deal with deleting a school too.)

The reason you need the foreign key field is because foreign key triggers operate with different visibility rules than normal queries, so you can't fully simulate them in user space. If you're not quite that paranoid you could just have a single AFTER INSERT trigger that looks for a school and throws an error if it doesn't find one.

answered Feb 26, 2016 at 20:01

Jim Nasby

1,27810 silver badges11 bronze badges

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

1 Comment

lizardo Over a year ago

Tks @Jim. Actually, as the data is spatial, is not a foreign key. The relationship between two tables can be just spatial. (checking if one element is geographically inside the boundaries of another element), without foreign keys. But thanks. I will think another way to make those constraints simpler to use.

Mike T · Accepted Answer · 2016-02-28 23:04:53Z

As far as any pure-DDL solutions, the most promising method is using CREATE TABLE ... CONSTRAINT ... EXCLUDE, which operates GiST on a single table, and has relatively limited operators that work only on the bounding box (e.g. &&):

CREATE TABLE polygons (
    geom geometry(Polygon,4326),
    EXCLUDE USING gist (geom WITH &&)
);
INSERT INTO polygons(geom)
VALUES('SRID=4326;POLYGON ((0 0, 0 1, 1 0, 0 0))');

But then, this conflicts (even though the geometries don't actually overlap):

INSERT INTO polygons(geom)
VALUES('SRID=4326;POLYGON ((1 1, 1 2, 2 2, 2 1, 1 1))');

ERROR: conflicting key value violates exclusion constraint "polygons_geom_excl" DETAIL: Key (geom)=(0103000020E61000000100000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F) conflicts with existing key (geom)=(0103000020E61000000100000004000000000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000).

As mentioned above by @Jim, the best approach to have one table to build a constraint over another table is to make a good trigger function, and use it on both tables. It would normally be written in PL/pgSQL, where you can embed useful messages, such as:

RAISE EXCEPTION 'School % is not located in school district % (it is % away)',
                s.name, d.name, ST_Distance(s.geom, d.geom);

This way, if you edit either the school_district or schools table, the trigger would to the check on UPDATE, INSERT, or DELETE to see if the conditions remain valid.

Collectives™ on Stack Overflow

PostgreSQL - Modify CREATE TABLE syntax in an extension

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related