[SQL] inserting boolean values in dynamic queries
Hi, I'm building a dynamic query as described at http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN : EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue); It works fine, except when I want to include a boolean value: the cast of newvalue from boolean to text causes problem. I can change my code and work with 't' and 'f' values as text, but wondered if there wasa way to use boolean values in a dynamically generated query. Thanks Raphaël -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Doubts about FK
2008/9/12 Scott Marlowe <[EMAIL PROTECTED]> > On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano > <[EMAIL PROTECTED]> wrote: > > Hi there, > > > > In my DB I have a couple of FK, so the change of referenced columns is a > > quite complicated. > > Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE > TRIGGER > > ALL to back them. > > Is there a better way to do that? > > Depends. Are other people connected to the server when you do it? > disable trigger disables the triggers for everybody, not just you if I > remember correctly. If other folks are using the db, then they can > insert bad data during that period. I do it in a transaction, so there's no problem about the other folks...! > > I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know how > to > > use it. > > the constraints have to created as deferrable to do that. then, in a > transaction, you can do something like: > Can I turn my FK into deferrable FK only in that transaction? > > begin; > update in a way that would normally violate an FK > insert in a way that fixes the previous statement's FK relationship > commit; > > and it will work as long as the constraints all make sense by the time > you get to commit. That's what I wanna to do! > > > Note that unique constraints are not deferrable in pgsql. >
Re: [SQL] inserting boolean values in dynamic queries
"Raphael Bauduin" <[EMAIL PROTECTED]> writes: > EXECUTE 'UPDATE tbl SET ' > || quote_ident(colname) > || ' = ' > || quote_literal(newvalue) > || ' WHERE key = ' > || quote_literal(keyvalue); > It works fine, except when I want to include a boolean value: the cast > of newvalue from boolean to text causes problem. What problem? 'true' and 'false' are accepted as input for boolean AFAICS. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] inserting boolean values in dynamic queries
On Thu, Sep 18, 2008 at 2:35 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Raphael Bauduin" <[EMAIL PROTECTED]> writes:
>> EXECUTE 'UPDATE tbl SET '
>> || quote_ident(colname)
>> || ' = '
>> || quote_literal(newvalue)
>> || ' WHERE key = '
>> || quote_literal(keyvalue);
>
>> It works fine, except when I want to include a boolean value: the cast
>> of newvalue from boolean to text causes problem.
>
> What problem? 'true' and 'false' are accepted as input for boolean
> AFAICS.
>
yes, but I have problems to use them to build the query passed to execute.
For example, working on this table
create table test(b_val bool);
I want to create a function that I cal call as
select test_bool(true)
and that will insert an entry in this test table.
Below are several attemps, all unsuccessful. The way I've made it work
it by accepting a char as input, t or f:
create or replace function test_bool(val char(1)) returns void as $$
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val || ')';
END
$$
language plpgsql;
--> ERROR: array value must start with "{" or dimension information
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val::text || ')';
END
$$
language plpgsql;
--> ERROR: cannot cast type boolean to text
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| quote_literal(val) || ')';
END
$$
language plpgsql;
--> ERROR: function quote_literal(boolean) does not exist
I guess I'm missing something
Thanks.
Raphaël
>regards, tom lane
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] inserting boolean values in dynamic queries
"Raphael Bauduin" <[EMAIL PROTECTED]> writes: > --> ERROR: function quote_literal(boolean) does not exist Oh, you must be using some version that didn't have a bool->text cast. I'd suggest making one. Or just make a quote_literal(boolean) function. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Doubts about FK
On Thu, Sep 18, 2008 at 5:49 AM, Rafael Domiciano <[EMAIL PROTECTED]> wrote: > 2008/9/12 Scott Marlowe <[EMAIL PROTECTED]> >> >> On Fri, Sep 12, 2008 at 12:14 PM, Rafael Domiciano >> <[EMAIL PROTECTED]> wrote: >> > Hi there, >> > >> > In my DB I have a couple of FK, so the change of referenced columns is a >> > quite complicated. >> > Today, I DISABLE TRIGGER ALL on the tables envolved and then ENABLE >> > TRIGGER >> > ALL to back them. >> > Is there a better way to do that? >> >> Depends. Are other people connected to the server when you do it? >> disable trigger disables the triggers for everybody, not just you if I >> remember correctly. If other folks are using the db, then they can >> insert bad data during that period. > > I do it in a transaction, so there's no problem about the other folks...! Wow, I just tested this in 8.3 an the other transactions block waiting for the one running the alter table to commit or rollback. Cool... >> > I read something about SET CONSTRAINTS ALL DEFFERED, but I don't know >> > how to >> > use it. >> >> the constraints have to created as deferrable to do that. then, in a >> transaction, you can do something like: > > Can I turn my FK into deferrable FK only in that transaction? You have to drop and recreate your constraints to make them deferrable. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ORDER BY collation order
Hi, I just found that two identical queries on two PG 8.2.7 databases with the same data and same encoding, one running on Debian and the other on FreeBSD, returned rows in a different order, even though both queries had an ORDER BY clause. Essentially, on FreeBSD a varchar starting with a double-quote character came first, ahead of a string starting with a number and a string starting with 'A', whereas on Debian the double-quote came last. Some research led to the following paragraph in the documentation: Character-string data is sorted according to the locale-specific collation order that was established when the database cluster was initialized. I guess that means the encoding of the respective template0 database is what determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian), right? Unfortunately, I'm unable to change the FreeBSD cluster since it's shared with others at our hosting provider. Is there some way to override the cluster setting, or plans to allow for database-specific collation orders? Joe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY collation order
On Thu, Sep 18, 2008 at 6:48 PM, Joe <[EMAIL PROTECTED]> wrote: > Hi, > > I just found that two identical queries on two PG 8.2.7 databases with the > same data and same encoding, one running on Debian and the other on FreeBSD, > returned rows in a different order, even though both queries had an ORDER BY > clause. Essentially, on FreeBSD a varchar starting with a double-quote > character came first, ahead of a string starting with a number and a string > starting with 'A', whereas on Debian the double-quote came last. > > Some research led to the following paragraph in the documentation: > > Character-string data is sorted according to the locale-specific collation > order that was established when the database cluster was initialized. > > I guess that means the encoding of the respective template0 database is what > determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian), right? > Unfortunately, I'm unable to change the FreeBSD cluster since it's shared > with others at our hosting provider. Is there some way to override the > cluster setting, or plans to allow for database-specific collation orders? no, not encoding, locale, such as en_US or C determine sort order. You can use varchar_pattern_ops and ~*~ operator. Search for those in the docs. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY collation order
Hi Scott, Scott Marlowe wrote: no, not encoding, locale, such as en_US or C determine sort order. OK, so I guess you're saying that whatever was in the LC_COLLATE environment variable at the time the template0 database was created determines the collation/sort order? Is that stored and visible somewhere? You can use varchar_pattern_ops and ~*~ operator. Search for those in the docs. What I found (http://www.postgresql.org/docs/8.2/static/indexes-opclass.html), talks about creating an index with varchar_pattern_ops but that presumably won't affect an ORDER BY result. I'm not quite sure where to find the "~*~" operator, although I did find similar ones in 9.7 Pattern Matching. In any case, I'm not sure how an operator helps in changing an ORDER BY result from "quoted" 123 Abc to 123 Abc "quoted" It's even trickier than this simple example, because on Debian which is using the en_US locale, the double quotes are disregarded for ordering purposes, e.g., Medical "Meet" Message Joe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] surrogate vs natural primary keys
At 08:20 AM 9/18/2008, [EMAIL PROTECTED] wrote: Message-ID: <[EMAIL PROTECTED]> Date: Wed, 17 Sep 2008 09:20:44 -0700 From: "Richard Broersma" <[EMAIL PROTECTED]> To: "Scott Marlowe" <[EMAIL PROTECTED]> Subject: Re: surrogate vs natural primary keys In-Reply-To: <[EMAIL PROTECTED]> References: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> X-Archive-Number: 200809/124 X-Sequence-Number: 31576 My opinion is that the database constraints are the last line of defense to ensure business rules and data integrity are not violated. Since I highly value the ability to enforce business rules using ordinary table DDL, I try to use natural keys as often as I can. Hi Richard, I often find your comments insightful and right on the money. This is another one of those cases. Your comments above are a great example of when natural keys make sense: I hadn't looked at it from this perspective! I'm a middleware developer (the bane of DBA's!) -- and so I generally "solve" these sorts business rules constraints in the middleware code, which of course is prone to all kinds of different problems (like lazy developers who code around the OO validation checkers!). Thanks for giving such a great explanation as to the value of natural keys! You haven't won me over, but you did teach me something - which I appreciate. Best, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
