[SQL] Default on update
Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Thanks for all. --- Lucas Vendramin ---(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] Default on update
[EMAIL PROTECTED] wrote: Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. Sounds like your frontend is broken. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. You could do this by having the application insert to a view with a rule that replaces null bv values before redirecting to the base table. Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN IF NEW.bv IS NULL THEN NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); To make the trigger work you'll have to relax the "NOT NULL" on column "bv" otherwise PG's type-checks will raise an error. Oh, and then make sure the trigger is called before INSERT too. Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Triggers behave exactly as you'd expect. For every row (or statement) the function gets executed. Difficult to say what effect they'll have on performance without testing with your actual setup. -- Richard Huxton Archonet Ltd ---(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] Default on update
Quoting Richard Huxton : [EMAIL PROTECTED] wrote: Hi. Is there a way to create "default" constraint on UPDATE query. It's becouse I have a bool field that may NOT support NULL value, but the Front-End calls null for FALSE values. Sounds like your frontend is broken. Yes, it is. But I have no access to the front-end. I will send it to the programmer. I was thinking something like: create table table1 ( id serial primary key, bv bool default false not null ); I would want to replace "bv" values with FALSE when insert/update NULL value for this field. You could do this by having the application insert to a view with a rule that replaces null bv values before redirecting to the base table. Is more functional to create a Rule instead of a trigger? Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN IF NEW.bv IS NULL THEN What is the difference between nullvalue() and IS NULL??? NEW.bv=FALSE; END IF; END; ' language 'plpgsql'; CREATE TRIGGER TG_table1_check BEFORE UPDATE on table1 for each row execute procedure tg_table1_check(); To make the trigger work you'll have to relax the "NOT NULL" on column "bv" otherwise PG's type-checks will raise an error. Oh, and then make sure the trigger is called before INSERT too. Okay. Other question: I have a lot of triggers in my db system, I have table that has 5/6 triggers, many triggers are simple (like the tg_table1_check), any are complex... Is it a problem??? My tests are with few regs and run fine. Where can I read more about triggers and performance? Triggers behave exactly as you'd expect. For every row (or statement) the function gets executed. Difficult to say what effect they'll have on performance without testing with your actual setup. Ok, I will test with more records. Thank you. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] OUT OF THIS LIST......
quiero darme de baja de la lista.mandenme el vinculo para hacerlo.
Re: [SQL] Default on update
[EMAIL PROTECTED] wrote: I would want to replace "bv" values with FALSE when insert/update NULL value for this field. You could do this by having the application insert to a view with a rule that replaces null bv values before redirecting to the base table. Is more functional to create a Rule instead of a trigger? It's different - you can think of a rule as a kind of macro, rewriting the query the application provides. Make sure you read the manuals carefully to be sure you understand how they work though. Or need I create a TRIGGER that check it and replace the itens??? CREATE or REPLACE function TG_table1_check RETURNS trigger as ' BEGIN IF nullvalue(NEW.bv) THEN IF NEW.bv IS NULL THEN What is the difference between nullvalue() and IS NULL??? "IS NULL" is a standard SQL expression. -- Richard Huxton Archonet Ltd ---(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] what is going on in the PostgreSQL
Hello all ! look template1=> \set a select template1=> \set b 1 template1=> :a :b; column -- 1 hope this is only psql's feathure not the server's one; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] sysid
It seems that in 8.1.0 we can no longer set the SYSID when adding users and groups. i.e template1=# create role hooker sysid 1345; NOTICE: SYSID can no longer be specified I have lots of code that depends on the actual group and user number. Is there a way to set the user and group number? thanks -- Arthur R. Van Hook Mayor The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 578-4215 - City (816) 564-0769 - Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] sysid
On 11/23/05, A. R. Van Hook <[EMAIL PROTECTED]> wrote: > It seems that in 8.1.0 we can no longer set the SYSID when adding users > and groups. > i.e > template1=# create role hooker sysid 1345; > NOTICE: SYSID can no longer be specified > > I have lots of code that depends on the actual group and user number. > Is there a way to set the user and group number? > > thanks > use versions < 8.1 > -- > Arthur R. Van Hook > Mayor > The City of Lake Lotawana > > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > (816) 578-4704 - Home > (816) 578-4215 - City > (816) 564-0769 - Cell > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sysid
A. R. Van Hook wrote: > It seems that in 8.1.0 we can no longer set the SYSID when adding users > and groups. > i.e > template1=# create role hooker sysid 1345; > NOTICE: SYSID can no longer be specified > > I have lots of code that depends on the actual group and user number. > Is there a way to set the user and group number? No -- fix the code. Just curious, how can user code depend on the SYSIDs? I don't see a way. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] unplanned sub-select error?
Kyle Bateman <[EMAIL PROTECTED]> writes:
> Sorry, you're right. I have now confirmed that this only happens when
> updating via a view/rule (as you suspected). Attached is a minimalist
> sql file that demonstrates the same error message from a blank
> database. I'm using 8.1.0. I'm pretty sure this problem did not exist
> on 8.0.3.
Fixed --- attached is the patch if you need it right away. Thanks for
the report!
regards, tom lane
Index: src/backend/rewrite/rewriteHandler.c
===
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v
retrieving revision 1.159
diff -c -r1.159 rewriteHandler.c
*** src/backend/rewrite/rewriteHandler.c22 Nov 2005 18:17:19 -
1.159
--- src/backend/rewrite/rewriteHandler.c23 Nov 2005 17:10:01 -
***
*** 374,379
--- 374,387
sub_action->jointree->fromlist =
list_concat(newjointree,
sub_action->jointree->fromlist);
+
+ /*
+* There could have been some SubLinks in newjointree,
in which
+* case we'd better mark the sub_action correctly.
+*/
+ if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
+ sub_action->hasSubLinks =
+ checkExprHasSubLink((Node *)
newjointree);
}
}
Index: src/backend/rewrite/rewriteManip.c
===
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteManip.c,v
retrieving revision 1.93
diff -c -r1.93 rewriteManip.c
*** src/backend/rewrite/rewriteManip.c 22 Nov 2005 18:17:19 - 1.93
--- src/backend/rewrite/rewriteManip.c 23 Nov 2005 17:10:01 -
***
*** 930,935
--- 930,936
RangeTblEntry *target_rte,
List *targetlist, int event, int update_varno)
{
+ Node *result;
ResolveNew_context context;
context.target_varno = target_varno;
***
*** 944,951
* Must be prepared to start with a Query or a bare expression tree; if
* it's a Query, we don't want to increment sublevels_up.
*/
! return query_or_expression_tree_mutator(node,
!
ResolveNew_mutator,
!
(void *) &context,
!
0);
}
--- 945,965
* Must be prepared to start with a Query or a bare expression tree; if
* it's a Query, we don't want to increment sublevels_up.
*/
! result = query_or_expression_tree_mutator(node,
!
ResolveNew_mutator,
!
(void *) &context,
!
0);
!
! if (context.inserted_sublink)
! {
! if (IsA(result, Query))
! ((Query *) result)->hasSubLinks = true;
! /*
!* Note: if we're called on a non-Query node then it's the
caller's
!* responsibility to update hasSubLinks in the ancestor Query.
!* This is pretty fragile and perhaps should be rethought ...
!*/
! }
!
! return result;
}
---(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] what is going on in the PostgreSQL
On Wed, Nov 23, 2005 at 02:54:27PM +0300, Eugene E. wrote: > template1=> \set a select > template1=> \set b 1 > template1=> :a :b; > > column > -- > 1 > > hope this is only psql's feathure not the server's one; What about this do you find surprising? Have you read the psql documentation regarding \set, variables, and interpolation? http://www.postgresql.org/docs/8.1/interactive/app-psql.html#APP-PSQL-VARIABLES test=> \set a select test=> \set b 1 test=> \set ECHO queries test=> :a :b; select 1; ?column? -- 1 (1 row) -- Michael Fuhr ---(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]
In PL/pgSQL, is there a way to put a *variable* column-name in a dot notation reference to a RECORD column? For example, suppose I want to write a function like the following, which is to be called by a "BEFORE INSERT" trigger: CREATE OR REPLACE FUNCTION foo ( ) RETURNS TRIGGER AS ' DECLARE var VARCHAR; BEGIN var := TG_ARGV[0] NEW. := ''whatever''; RETURN NEW; END; ' LANGUAGE 'plpgsql' ; The aim of this uninteresting function is to assign the value 'whatever' to the table column that is passed in by the calling trigger as TG_ARGV[0], i.e. the first calling argument. What I don't know is what to put into the dot notation in place of "." so that the column of NEW that is addressed by the assignment statement is the one passed in as the first argument. Is there any PL/pgSQL construct that could be substituted in here to achieve this result? If not, can anybody suggest a way to write a trigger-called function that would accomplish the same result? In case it's not obvious, the underlying goal is to write a single trigger-called function that could modify different columns for each trigger that called it, where each trigger specified the target column by a calling argument (or by any other viable mechanism). ~ TIA ~ Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
