Re: [SQL] Tidying values on variable instantiation
Desired Outcome(s): * I would like to have the convenience of declaring a column that obeys a constraint (similar to using a domain), but allows a "tidy-up" as the value is created BEFORE asserting the constraint. This *might* be termed a "domain trigger". (Perhaps even a WORM is possible!). * I would like to able to declare columns as "trimmed_varchar(n)". * I'd like to be able to use the same approach for other "weak domains". I'm not sure these any easier way to do this than with tirggers. If the daunting task would writing a large number of triggers, I would write sql or php to generate all the triggers. Remember that you can introspect the db using the system catalogs. I've had a lot of good results generating triggers and sql using Middlegen and Velocity. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Tidying values on variable instantiation
IMHO: It's not necessarily the job of the RDBMS to be responsible for formatting and cleaning of your data... This is a job better suited for the application layer and the data model... The RDBMS should only be responsible for enforcing constraints on the data... not validating or purifying the data... Data validation and purification should be performed at the application layer -- you should format your data appropriately BEFORE trying any INSERT/UPDATE operations. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bath, David Sent: Thursday, August 25, 2005 8:04 PM To: pgsql-sql@postgresql.org Subject: [SQL] Tidying values on variable instantiation Folks, Preamble: * I can create a check constraint on a column or domain that enforces "no leading or trailing whitespace". Imagine that the domain is called "trimmed_varchar" * I can create plpgsql function/triggers that "tidy" up incoming varchars, trimming the offending whitespaces, on a column by column basis. * When creating a column based on a domain with the check constraint, I cannot "tidy it up" during a pre-insert/pre-update trigger. Fair enough. * I'm only asking about this because I am a long-in-the-tooth Oracle guy, and Pg seems to have many *very* nice features, and there might be an *elegant* way to achieve this that I cannot attempt in Oracle. Desired Outcome(s): * I would like to have the convenience of declaring a column that obeys a constraint (similar to using a domain), but allows a "tidy-up" as the value is created BEFORE asserting the constraint. This *might* be termed a "domain trigger". (Perhaps even a WORM is possible!). * I would like to able to declare columns as "trimmed_varchar(n)". * I'd like to be able to use the same approach for other "weak domains". Question(s): * Am I being realistic, or should I grit my teeth and clone code from trigger to trigger and column to column? * Is this something I should try and do using domains, types and cast functions from "text" or some horrible combination of them all? * Has anybody got a code sample that might do something similar. Apologies if I have missed something obvious in the manual, or if it is a well-known item in the wish-lists, but I am very new to serious Pg work, and have a tight schedule to do deliver a schema. *sigh* Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Tidying values on variable instantiation
On Fri, Aug 26, 2005 at 13:04:10 +1000, > Desired Outcome(s): > * I would like to have the convenience of declaring a column that obeys > a constraint (similar to using a domain), but allows a "tidy-up" as the > value is created BEFORE asserting the constraint. This *might* be > termed a "domain trigger". (Perhaps even a WORM is possible!). > * I would like to able to declare columns as > "trimmed_varchar(n)". > * I'd like to be able to use the same approach for other "weak domains". > > Question(s): > * Am I being realistic, or should I grit my teeth and clone code from > trigger to trigger and column to column? > * Is this something I should try and do using domains, types and > cast functions from "text" or some horrible combination of them all? > * Has anybody got a code sample that might do something similar. I think it is normal to expect the application to pass you clean data. I think you can do what you want by creating a new type. I seem to remember there are issues with creating whatever(n) types (my memory is that varchar(n) is hardwired into the parser), but certainly you could make an alternate version of text whose input function trimmed leading and trailing whitespace. You could also create casts between this new type and text if you needed that ability as well. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Tidying values on variable instantiation
[EMAIL PROTECTED] ("Greg Patnude") writes:
> Data validation and purification should be performed at the
> application layer -- you should format your data appropriately
> BEFORE trying any INSERT/UPDATE operations.
It seems to me that one might create some stored functions that can do
some validation/purification which, by virtue of residing in the
database, have the ability to efficiently access other data in order
to do data-based validation.
I know I have found that to be a useful approach...
--
"cbbrowne","@","ntlug.org"
http://cbbrowne.com/info/linuxdistributions.html
"The day Microsoft makes something that doesn't suck is probably the
day they start making vacuum cleaners" - Ernst Jan Plugge
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Tidying values on variable instantiation
[EMAIL PROTECTED] ("Bath, David") writes:
> Question(s):
> * Am I being realistic, or should I grit my teeth and clone code from
> trigger to trigger and column to column?
> * Is this something I should try and do using domains, types and
> cast functions from "text" or some horrible combination of them all?
> * Has anybody got a code sample that might do something similar.
If what you are looking for is an API that "cleans things up," then
I'd suggest the thought of building a stored function API, and using
*that* to do the work instead of hitting tables directly.
In the 'domain registry' area, I have done this very sort of thing,
mostly oriented towards data conversions.
We have, as major objects, domains, contacts, and nameservers.
I have some functions, create_domain(), create_contact(), and
create_nameserver(), where the stored procs receive a very limited set
of parameters (in comparison, at least, with the total number of
attributes associated with the respective sets of tables).
Relevant to the thread, create_contact() does a lot of "data
cleansing" in order to try to perform the Procrustean task of forcing
telephone numbers into a particular "standard form."
That approach has proved very useful.
If you have some well-defined set of actions that you want to perform
on the objects in your system, defining a stored function for each
action gives a good way of centralizing the "cleanup" parts.
We have separate status tables; I could have defined triggers to try
to manage them; it seemed more sensible to instead handle that in the
stored procs.
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/oses.html
Rules of the Evil Overlord #69. "All midwives will be banned from the
realm.All babies willbe deliveredat state-approved
hospitals. Orphans will be placed in foster-homes, not abandoned in
the woodsto be raisedby creatures ofthe wild."
[SQL] Common patterns for 'store' functions
I find store functions fairly useful; eg for any table 'foo', the function store_foo(v1,v2,...,vn) returns int; will perform an insert (if a unique key is not present) or an update (if the unique key is present) v1,...,vn may be values for columns in the table foo, or perhaps also for tables foo_a, foo_b, c_foo related to foo by foreign keys (in a highly normalised db, it can be convenient to combine these multiple update/inserts into a single function call). These functions can be implemented in an application/middleware layer (eg perl,java,..) or as pgsql functions. Both approaches have their strengths and weaknesses. For my purposes, sometimes pgsql is preferable. For one thing, pgsql store functions will be faster since it involves less client-server I/O. However, coding these can be fairly tedious. There is the additional problem that it's generally desirable to provide multiple signatures for different permutations of v1,...,vn. This can be unwieldy when a store function has lots of possible values. In these cases, the application layer approach has a big advantage, as the arguments to a function call generally don't have to be position dependent. As far as I am aware, there is no equivalent way to do this in plpgsql. Is this a common use case, or do people typically do this in the application/middleware layer? If other people are doing this, are there any common design patterns they would like to share? What about code that helps auto-generate some of these functions? Or even super-clever middleware that can decide whether to do it in the application layer or autogenerate some helper functions...? Cheers Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] booleans and nulls
Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? 2. Is matching on IS NULL or = 0 more efficient? 3. If I ix'd columns w/ null does postgres know whatevers not indexed is null or would their be no point? I currently utilize null fields as 'not a value' has meaning in a program i've been working on as I don't want to put false in every column when i only need a couple with a true/false value. I'm not joining tables on NULLS, just filtering w/ them. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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
[SQL] Numerical variables in pqsql statements
Folks, I'm sure this is dumb, but I'm a little confused about use of numerical variables in pqsql. It was my impression one had to use EXECUTE on a concatenated string including quote_literal() for the variable containing the value. This can be quite a bit of trouble. I just wrote a function that included the statement : CREATE TEMPORARY TABLE author_names AS SELECT ra.ref_auth_key, a.last_name, a.first_name FROM ref_auth ra INNER JOIN authors a ON (ra.author_num = a.author_key) WHERE ra.ref_num = refer_num; where refer_num is integer. This worked (surprisingly, to me). So, what is the rule regarding variables? Would this query work if I concatenated a string together, including quote_literal(refer_num) and then EXECUTEd it? Thanks and sorry to be so stupid.
[SQL] nullif('','') on insert
I need to test whether or not a value is null on
insert.
Example: insert into table (column) values
nullif('',''));
ERROR: column "column" is of type boolean but
expression is of type text.
It works in MSSQL (probably against not standards) but
nonetheless I need to make it work.
I assume it's returning 'NULL' w/ quotes? I don't know
where to look to alter it. I looked into functions but
all I see is how to write "AS queries" or point to
various snippets. I'd rather just alter the nullif
function.
Is there a function for this or could someone give me
an example to insert NULL?
Cheers
Matt
__
Yahoo! Mail for Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
---(end of broadcast)---
TIP 1: 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
[SQL] Unwanted nested dollar-quoted constants
Hello, I recurrently need to migrate a big amount of data from one database type to PostgreSQL 8.0.3. For this, I use INSERT scripts that I run from the pgAdmin III. All the text fields are specified using the dollar-quoted string constant form that. However, sometime, there are records that include some text that look like a nested tagged dollar-quoted constant start (eg. $EN$) without its ending conterpart. In such situation, the whole set of records is refused (without any error telling where and what). I have tried to insert a '\' char in front of each '$' which, of course, did ot work since they are not treated as is. So, what can I do to say that it is not a start of a new nested tagged dollar-quoted constant ? Or is there any way to disable nested tagged dollar-quoted constant ? Best regards _ Obtenez aussi votre adresse electronique gratuite de Quebecemail.com http://www.quebecemail.com, un service gratuit et permanent. ---(end of broadcast)--- TIP 1: 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
[SQL] question
I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating level.
Lastly there is a null option for n/a. We use null for
n/a so it's excluded from the AVG() calculations.
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the "not a value"
accordingly.
Is there a workaround for this so it doesn't break our
rating system? We cannot always enter a value for a
integer column. Is there any other way to accomplish
this? Please help.
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] returning inserted id
this may have been asked a thousand times but i haven't found a standard answer... MSSQL set nocount on insert into (column) values (value) select identityid = @@identity set nocount off POSTGRESQL *cricket cricket* :) How is this done? By a trigger function? Or is it natively supported? Could I get an example on how to do it too? Thanks, Matt Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] returning inserted id
On Sun, Aug 21, 2005 at 12:56:27 -0700, "Matt A." <[EMAIL PROTECTED]> wrote: > this may have been asked a thousand times but i > haven't found a standard answer... > > > MSSQL > set nocount on > insert into (column) values (value) > select identityid = @@identity > set nocount off > > > POSTGRESQL > *cricket cricket* :) > > > How is this done? By a trigger function? Or is it > natively supported? Could I get an example on how to > do it too? Use a serial type for the column and use currval to get the last value assign to the corresponding sequence in the current session. You probably want to read the documentation section on sequences. http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-sequence.html http://candle.pha.pa.us/main/writings/pgsql/sgml/datatype.html#DATATYPE-INT ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] booleans and nulls
Matt L. wrote: Out of curiousity, 1. Does a boolean column occupy 1byte of disk whether or not the value is null or not? I believe so. 2. Is matching on IS NULL or = 0 more efficient? Hmm... = 0 is the same as IS FALSE. Not the same as IS NULL. So I guess it is apples v. oranges 3. If I ix'd columns w/ null does postgres know whatevers not indexed is null or would their be no point? I currently utilize null fields as 'not a value' has meaning in a program i've been working on as I don't want to put false in every column when i only need a couple with a true/false value. I'm not joining tables on NULLS, just filtering w/ them. Sounds like a partial index would be your best bet. Something like: CREATE index ON my_table WHERE my_bool IS NOT NULL Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 1: 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] nullif('','') on insert
Matt L. wrote:
I need to test whether or not a value is null on
insert.
Example: insert into table (column) values
nullif('',''));
ERROR: column "column" is of type boolean but
expression is of type text.
Your problem is that NULL's are typed in PostgreSQL.
Try this:
SELECT NULL;
SELECT NULL::BOOL;
SELECT NULL::BOOL::TEXT;
to see what I mean. This is an exact illustration of your problem.
It works in MSSQL (probably against not standards) but
nonetheless I need to make it work.
I assume it's returning 'NULL' w/ quotes?
Nope. It is returning a text string which is valued at NULL. It cannot
convert a text string to a BOOL (even if the string is a NULL) so it
gives you an error.
I don't know
where to look to alter it. I looked into functions but
all I see is how to write "AS queries" or point to
various snippets. I'd rather just alter the nullif
function.
SELECT NULLIF('' = '', TRUE);
Does this work? You could write a wrapper function if necessary.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
