[SQL] Informing end-user of check constraint rules
I have not used column check constraints before, but I'd like to start using then and so would I'll like to know if there is a direct way to provide feedback to the end user about data validation rules expressed in column check constraints? For instance, say that I wanted to use a RE to check e-mail address format validity and then the data entry clerk typed in invalid data. My understanding is that when the check constraint returns FALSE, the row will not insert, and an ExecAppend: rejected due to CHECK constraint "table_column " exception is raised. That at least tells the column (albeit in language that would scare the computer-phobe), but I like the exception message to tell the end user what the format is supposed to be. Is my only option to have the end-user application (as opposed to the database) inform the end-user what the correct data format is? If THAT is so, then it seems I might as well also perform the data formatting validation in the application, too, so that at least they'ld both be in the same place and not two separate places. What I'd like is to be able to specify some kind of error message telling the user what the correct format should be, and since the proper format is specified in the database, i.e., in the check constraint, it seems that the proper place to raise an exception providing the remedial instructions would also be in the database. ~Berend Tober ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Informing end-user of check constraint rules
> Am Sonntag, 22. Juni 2003 14:45 schrieb [EMAIL PROTECTED]:
>> I have not used column check constraints before, but I'd like to
>> start using then and so would I'll like to know if there is a direct
>> way to provide feedback to the end user about data validation rules
>> expressed in column check constraints?
>>
> you can use a trigger on insert and write your own error handling
> function like below. then you have everything at one place. I think
> postgres should have better ways to report errors but i am not a
> database guru and dont know how other databases do their error
> handling.
>
I came up with what I think is a pretty cool implementation idea, as
follows.
-- 1) Define a table as
CREATE TABLE regular_expression (
description character varying(48) NOT NULL,
regular_expression character varying(128),
user_message text
) WITHOUT OIDS;
-- with sample data:
INSERT INTO regular_expression VALUES ('Social Security Number',
'^\\d{3}-\\d{2}-\\d{4}$', 'Social Security Number must consist of the
pattern: nnn-nn-, where "n" is a digit.');
INSERT INTO regular_expression VALUES ('US Telephone Number',
'^[2-9]\\d{2}-\\d{3}-\\d{4}', 'US Telephone numbers must consist of the
pattern aaa-eee-, optionally followed by extra extension number,
where aaa is the three-digit area code, eee is the three digit exchange
code, and is the four digit number.');
INSERT INTO regular_expression VALUES ('Internet E-Mail Address',
'[a-z0-9_]+([-.][a-z0-9_]+)[EMAIL PROTECTED]([-.][a-z0-9_]+)+',
'Internet E-Mail Addresses are typically of the form [EMAIL PROTECTED],
where, "n" is the user name, "dd" is the Internet domain name,
and "ttt" is the three character top-level domain name.');
INSERT INTO regular_expression VALUES ('US ZIP Code',
'^\\d{5}-\\d{4}|\\d{5}$', 'US Postal ZIP Codes are of the form n or
n-, where "n" is any digit.');
COMMENT ON TABLE regular_expression IS 'This table defines regular
expressions used in the application.';
-- 2) Define the following function to be used as a generic CHECK
constraint:
CREATE FUNCTION public.check_pattern(varchar, varchar) RETURNS bool AS '
DECLARE
l_value ALIAS FOR $1;
l_pattern ALIAS FOR $2;
l_row RECORD;
BEGIN
IF l_value IS NOT NULL THEN
IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN
SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) =
UPPER(l_pattern);
IF NOT (l_value ~ l_row.regular_expression) THEN
RAISE EXCEPTION ''Invalid %. %'', l_pattern, l_row.user_message;
END IF;
END IF;
END IF;
RETURN TRUE;
END;
' LANGUAGE 'plpgsql' VOLATILE;
-- 3) Define any check constraint you want similar to:
CREATE TABLE person (
e_mail_address varchar(128),
social_security_no varchar(11),
--[...other column defs...]
CONSTRAINT person_e_mail_address CHECK (check_pattern(e_mail_address,
'Internet E-Mail Address')),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'))
) WITHOUT OIDS;
The only hitch I've run into so far is that when I want to do an ALTER
TABLE to ADD a CHECK CONSTRAINT this way when there is existing data, I
need to temporarily CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line commented out because for some reason that
particular exception gets raised in the process of trying to add the
constraint, and so the ADD CONSTRAINT command fails. After the constraint
is successfully added with the function's RAISE EXCEPTION call commented
out, then I re-CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line restored.
Seems to work great in preliminary testing.
~Berend Tober
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Postgres - Delphi Application
> Dnia 2003-06-27 12:25, U¿ytkownik [EMAIL PROTECTED] napisa³: > >> I think I looked at zeos at some point and found that it did not >> support md5 authentication, which, at the time, I thought was >> important. >> ~Berend Tober > Zeos has nothing to authentication. It's a matter of libpq library, > which currently supports md5 authentication. If you need more security > - you can even compile libpq with ssl. > Tomasz Not sure I was clear. Using zeos, you get a set of Delphi components that connect the database back end with user-interface controls on your Delphi TForm objects, like TDBEdit, TDBMemo, etc. You use those zeos components instead of the standard "messy" BDE database components TDatabase, TQuery, TTable, etc. While the zeos components would successfully connect to the database back end, they would not allow or require database-enforced md5 authentication at the user-interface end. I didn't want to have to invent my own authentication scheme when the database is capable of doing a good job. ~Berend Tober ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Postgres - Delphi Application
> Dnia 2003-06-19 19:50, U¿ytkownik murali napisa³: > > I' would like to connect my postgres database on a linux server with > Delphi 5 Applications,which are located on Win95/98 Clients, very > helpful would be an example on how to realize a connection of these > things > > http://sourceforge.net/projects/zeoslib > > Nice solution - your executable needs only small libpq.dll file without > any annoying BDE/ODBC installation. I think I looked at zeos at some point and found that it did not support md5 authentication, which, at the time, I thought was important. ~Berend Tober ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Does a the block of code within a stored procedure constitute a transaction?
I need to "manually" keep a sequence for each row of the employee table, i.e., I don't want to use postgres's built-in sequences for this application because the sequence numbers are used to generate expense report numbers on a "per-employee" basis. That is, each employee has a separate sequence counter, starting at one, to number their expense reports. Since employees will come and go, I don't want to keep having to create and delete postgres sequence objects as employees come and go. Instead, I have a column of the employee table store the "last value" of the corresponding expense report sequence counter, and in an ON INSERT trigger to the expense report table, I call the following function to get and increment the new sequence value: CREATE OR REPLACE FUNCTION paid.expense_report_next(int4) RETURNS integer AS ' DECLARE l_employee_pk ALIAS FOR $1; l_expense_report_seq INTEGER; BEGIN SELECT INTO l_expense_report_seq expense_report_seq+1 FROM employee WHERE employee_pk = l_employee_pk; UPDATE employee SET expense_report_seq = l_expense_report_seq WHERE employee_pk = l_employee_pk; RETURN l_expense_report_seq; END;' LANGUAGE 'plpgsql' VOLATILE; What I need to know is whether or not this is multi-user safe, i.e., will the block of code in the procedure execute as a transaction so that if more than one clerk creates an expense report for the same employee simultaneously is it possible or impossible that value of the employee.expense_report_seq gets updated by the second clerk between the SELECT and UPDATE statements invoked by the first clerk? And as a follow-up, should I add the FOR UPDATE clause to the SELECT statement? ~Berend Tober ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Does a the block of code within a stored procedure constitute a transaction?
>> What I need to know is whether or not this is multi-user safe, i.e., >> will the block of code in the procedure execute as a transaction so >> that if more than one clerk creates an expense report for the same >> employee simultaneously is it possible or impossible that value of >> the >> employee.expense_report_seq gets updated by the second clerk between >> the SELECT and UPDATE statements invoked by the first clerk? >> >> And as a follow-up, should I add the FOR UPDATE clause to the SELECT >> statement? > > SELECT .. FOR UPDATE would be appropriate. > > However, you could also do an update + 1 first and the select second to > find what you changed the value to. Yes, and I like the fact that the UPDATE first approach eliminates a local variable declaration as well! ~Berend Tober ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Automated Backup
>
>> Is there a way to automate the backup databases using pg_dump (like
>> in SQL server)?
>
Ha! Why would you want to do ANYTHING "like in SQL server"! ;)
You can do you back-ups very nicely using cron and a bash script:
bash-2.05a$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.22116 installed on Fri Jun 13 10:41:06 2003)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
11 02 * * 1-6 /usr/local/bin/dump paid [EMAIL PROTECTED]
Slightly edited, but illustrates the point:
bash-2.05a$ cat /usr/local/bin/dump
#!/bin/bash
# Script to dump a PostgreSQL database, producing
# compressed tar file containing with pg_dump output.
# Author: Berend M. Tober
# Date: August 25, 2003
if [ "${1}" = "" ]
then
echo "Must specify database name"
exit 0
fi
# setup variables
NAIL=/usr/local/bin/nail
PG_DUMP=/usr/bin/pg_dump
TAR=/bin/tar
DBNAME=${1}
UNAME=postgres
TARGET_EMAIL=${2}
OUTPUT_FILE=${DBNAME}.`date +%Y%m%d`
# create dump file
${PG_DUMP} -Fc -U ${UNAME} ${DBNAME} > ~/${OUTPUT_FILE}.dump
# create compressed archive of dump (and other) files
${TAR} -czf ~/${OUTPUT_FILE}.tar.gz ${OUTPUT_FILE}.dump
# above line uses tar rather than just gzip
# because in reality other files are included in
# my backup archive but which have been omitted
# in this mailing list post for simplicity.
# optionally mail the back-up archive offsite
if [ "${2}" != "" ]
then
echo|${NAIL} -r ${UNAME} -a ~/${OUTPUT_FILE}.tar.gz -s
${OUTPUT_FILE}.tar.gz ${2}
fi
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] auto_increment
> How to Create auto_increment field in PostreSQL. Its called the SERIAL datatype: create table test_table ( field1 serial, constraint test_table_pkey primary key (field1)); > Can I create them using Trigger. Yes, alternatively, but I'm told that is not recommended because the server source code that manages the special sequence table is highly optimized for this specific functionality. ~Berend Tober ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to figure out when was a table created
Can you add two columns to the table creation definition, one with a default current timestamp, the second to be updated with current time in an on update trigger. Then perhaps you could do something like SELECT min(new_insert_timestamp_column), max(new_update_timestamp_column) FROM your_table; to get the table-wide date of first entry and last update. ~Berend Tober > > Well, in certain filesystems you can have the birth time > (like ufs2) stored in the inode struct. > > So you find the file name in your $PGDATA/base directory > using the oid of your table (in pg_class), > and then you open that file with stat (2) or utimes (2) (or > from perl) to read creation data. > > All that apply for FreeBSD, see if creation time is supported > in ext2/3. > > On Thu, 2 Oct 2003, David B wrote: > >> Hi folks, >> >> I posted this question a few days ago and got no response so I guess >> it cannot be done (surprising!) >> So that leaves me with my business problem. >> >> We create a table for each days activity. >> After N days (typically 7 days) we can drop the table. >> The table name is not known so cannot force business to make >> tablename something like mydata_MMDDYY >> >> I'd like to be able to do something like: >> SELECT tablename >> FROM pg_??? >> WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 >> days', >> 'dd/mm/yy' ) >> >> Any suggestions? >> >> --- Prior msg was: >> >> Folks, >> >> I have a list of tables for which I want to get the date they were >> created...and if possible the date last updateded. >> >> I suspect there is a pg_??? table that can answer this question but I >> don't know what it is and I cannot find it mentioned in any docs. >> >> Any suggestions...tia ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
