Guyren Howe <guyren@xxxxxxxxx> writes: > I believe a lot of application programmers, particularly but by no means limited to web application developers, have a tragic prejudice against treating their database as anything but a dumb data bucket. > They also often lack awareness of even simple-to-use SQL/Postgres features that would make their lives easier (top of this list would be CTEs and Window Functions). > So I’ve started a project to fix this. I’m initially going to write a series of blog posts demonstrating in principle how a developer can put much/all of their model logic in their database. Cool. This sounds well worth while. > I’m starting with constraints. Using Ruby on Rails as my example, a server-side constraint violation shows up in vanilla Rails as an Exception, that looks like this: > PG::CheckViolation: ERROR: new row for relation "users" violates check constraint "family_name_length" > DETAIL: Failing row contains (11, foo). > What I need to do is turn this into something similar to the equivalent Rails-side constraint failure, which is a nicely formatted error message on the model object. > The obvious thing would be to parse the error message. Well, that really isn't considered good practice, because your code will fail on localized error messages. > It occurs to me that I might instead do something on the server side. I’d like to get back a more structured error. The server already does deliver more-structured error data, although I confess that I have no idea how to get at it in Ruby on Rails. In psql the case looks about like this: regression=# create table users (id int, name text, constraint family_name_length check(length(name) > 4)); CREATE TABLE regression=# insert into users values (11,'foo'); ERROR: new row for relation "users" violates check constraint "family_name_length" DETAIL: Failing row contains (11, foo). regression=# \errverbose ERROR: 23514: new row for relation "users" violates check constraint "family_name_length" DETAIL: Failing row contains (11, foo). SCHEMA NAME: public TABLE NAME: users CONSTRAINT NAME: family_name_length LOCATION: ExecConstraints, execMain.c:1810 The 23514 part is ERRCODE_CHECK_VIOLATION, which evidently corresponds to "PG::CheckViolation" in the Ruby code, so at least that much is exposed to you in a reasonable way. What you want is to get at the error-report fields shown here as "SCHEMA NAME", "TABLE NAME", "CONSTRAINT NAME". I don't know if the Ruby client library makes those accessible. (In the spirit of full disclosure, I should point out that those fields have only been provided since PG 9.3. So older installations may not have the ability to do this in the Right Way. But still, you should be evangelizing for doing it the Right Way, no?) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
