Re: [SQL] Consecutive row count query

[Leon Stringer]

> I wondered if anyone could answer the following question:

Thanks for your responses, I think I'll just add the extra column in as
Greg suggests. (BTW: I mean "unnecessary *denormalization*" which I hope
is less odd!).

Leon...


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] best way to swap two records (computer details)

[PFC]

My question is what's the best way to swap settings between the two
computer records and swap any software installed?  Ideally I'd like it
in the form of a function where I can pass the two p_id's and return a
boolean reflecting success (true) or fail (false).

I'd say something like that (generic table names) :
If you're confident :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END) WHERE  
owner IN ('A','B')

If you're paranoid :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF owner='B'  
THEN 'A' ELSE owner END) WHERE owner IN ('A','B')

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Trigger with parameters

[lucas]

Hi,
I am building a generic database in postgresql.
And I am having a lot of problem Yes, I am just studing and learning about
postgres.
Lets go:
My database have two schemas:
 1 - The MAIN schema
 2 - The System schema.
I created the system schema that contains the table_fields register for the Main
tables.
Ie:
...# createdb -U postgres test
...# createlang -d test -U postgres plpgsql
...# createlang -d test -U postgres pltcl
...# psql -U postgres test

 CREATE SCHEMA main_system;
 CREATE SCHEMA main;

 CREATE TABLE main_system.products_codes (field varchar(30) primary key, "check"
text);
 INSERT INTO main_system.products_codes VALUES ('internal',$$NOT EXISTS(select *
from main.products_codes where field_name='internal' and value='$value')$$);
 INSERT INTO main_system.products_codes VALUES ('barcode','true');

 CREATE TABLE main.products (id serial primary key, description varchar(100),
field1 integer, fieldx varchar(10));
 INSERT INTO main.products(id,description) VALUES (1,'Computer product');

 CREATE TABLE main.products_codes (id serial primary key, "references" integer
references MAIN.PRODUCTS, field_name varchar(30) references
MAIN_SYSTEM.PRODUCTS_CODES, "value" varchar(50));
 INSERT INTO main.products_codes("references",field_name,"value") values
(1,'internal','COMPUTER-1');
 INSERT INTO main.products_codes("references",field_name,"value") values
(1,'barcode','1234567890123');

In my database I can create how much products codes fields I like just inserting
a record into MAIN_SYSTEM.PRODUCTS_CODES table.
Okay, then I created another function that cat the fields types for the main
table:

 create or replace FUNCTION getfieldvalue(text,text,integer,varchar(30)) returns
text as
 $$
   set schema_name $1;
   set table_name $2;
   set references_value $3;
   set field_value $4;
   set references_name {"references"}
   set field_name {"field_name"}
   set point "."
   spi_exec "select value as getfieldvalue from $schema_name$point$table_name
where $references_name = $references_value and $field_name = '$field_value'";
   return $getfieldvalue;
  $$ language 'pltcl';

  SELECT getfieldvalue('main','products_codes',1,'internal'); --Will return the
Internal product code for product id 1
  SELECT getfieldvalue('main','products_codes',1,'barcode'); --Will return the
Barcode for product id 1

With this function I can view the "main_system fields", If in the future I would
need to create a new code field (ie), its so easy, just type:
 INSERT INTO main_system.products_codes values ('mynewcodefield','true');
And the main.products_codes will references to this new field.

The main_system.products_codes have two colums:
 1-"field" as a field name
 2-"check" as a validate check before insert into the main table references.
 This check (2) will be executed before all "insert or update" (trigger) the
main table references (main.products_codes), for example if I want to UNIQUE
internal fields (inserted to me) I need to type "check" colums like: "NOT
EXISTS(select * from main.products_codes where field_name='internal' and
value='$value')";
 The $value variable will be used into another function (pltcl) called by
Trigger.
 The function:

 --system_getcheckfieldvalue(schema name, table name, field name) will return
the CHECK necessary to Trigger.
 CREATE or REPLACE FUNCTION system_getcheckfieldvalue(text, text,  text) returns
text as
 $$
  set system_name "_system";
  set schema_name $1;
  set table_name $2;
  set field_name $3;
  set select_query {select "check" AS system_getcheckfieldvalue from };
  set select_filter { where "field"='};
  set close_select_filter "'";
  set point ".";
  spi_exec
$select_query$schema_name$system_name$point$table_name$select_filter$field_name$close_select_filter;
  return $system_getcheckfieldvalue;
 $$ language 'pltcl';
 SELECT system_getcheckfieldvalue('main','products_codes','internal'); --Will
return the query (not exists(select..
 SELECT system_getcheckfieldvalue('main','products_codes','barcode'); --Will
return "TRUE"

 --system_checkfieldvalue (schema name, table name, field name, value
simulation) will return if the "value simulation" is Correct - True/False
 CREATE or REPLACE FUNCTION system_checkfieldvalue (text,  text,  text,  text)
returns bool as
 $$
  set schema_name $1;
  set table_name $2;
  set field_name $3;
  set value $4;
  set result true;
  spi_exec "select
system_getcheckfieldvalue('$schema_name','$table_name','$field_name')";
  set sql_query [subst -nocommands $system_getcheckfieldvalue];
  spi_exec "select $sql_query as result";
  return $result;
 $$ language 'pltcl';
 SELECT system_checkfieldvalue('main','products_codes','internal','COMPUTER-1');
--Will return FALSE (becouse there is a product with internal code COMPUTER-1
 SELECT system_checkfieldvalue('main','products_codes','internal','COMPUTER-2');
--Will return TRUE (becouse there is NOT a product with internal code COMPUTER-2

Okay, but look the problem:
When I try to create a Trigger procedure to ch

Re: [SQL] best way to swap two records (computer details)

[Greg Patnude]

How about a user defined function ???

CREATE OR REPLACE FUNCTION harwareupdate(integer, integer) RETURNS BOOLEAN 
AS '

update pieces set p_name = \'LSALES1\', p_location = \'Mike Haley\',
p_site = \'L\' where p_id = $1;

update pieces set p_name = \'SPARE\', p_location = \'spare\', p_site = 
\'L\'
where p_id = 2;

update pieces set p_owner = $1 where p_owner = $2 and p_type
in (select hwt_id from hw_types where hwt_cat in (

select hwc_id from hw_categories where hwc_hwg_id = 7

)

);

SELECT TRUE;

' LANGUAGE SQL;

Then all you need to do is:

SELECT * FROM updatehardware(724, 305);


"Gary Stainburn" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Hi folks.
>
> I have a table called pieces which contain every piece of hardware and
> software within my company.
>
> Each piece has an owner attribute which points to another piece which -
> funnily enough - owns it.
>
> For example records for CPU, motherboard, HDD, O/S, and applications
> will all be owned by a piece record representing a computer.
>
> I'm currently going through an upgrade process at the moment where I
> build a new PC, install all relevent software and use Documents and
> Settings Transfer Wizard to move a user onto the new PC before wiping
> and disposing the old PC.
>
> My question is what's the best way to swap settings between the two
> computer records and swap any software installed?  Ideally I'd like it
> in the form of a function where I can pass the two p_id's and return a
> boolean reflecting success (true) or fail (false).
>
> Currently I do this manually with:
>
> update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site
> = 'L' where p_id = 724;
> update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L'
> where p_id = 305;
>
> update pieces set p_owner = 724 where p_owner = 305 and p_type in (
> select hwt_id from hw_types where hwt_cat in (
> select hwc_id from hw_categories where hwc_hwg_id = 7));
>
> The hw_types and hw_categories select all O/S and application software.
> This doesn't put any software currently on 305 onto 724 which would be
> nice.
>
> (I'm not after someone to do my work for me, but a good starting point
> would be very much appreciated)
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] best way to swap two records (computer details)

[Gary Stainburn]

Hi folks.

I have a table called pieces which contain every piece of hardware and 
software within my company.

Each piece has an owner attribute which points to another piece which - 
funnily enough - owns it.

For example records for CPU, motherboard, HDD, O/S, and applications 
will all be owned by a piece record representing a computer.

I'm currently going through an upgrade process at the moment where I 
build a new PC, install all relevent software and use Documents and 
Settings Transfer Wizard to move a user onto the new PC before wiping 
and disposing the old PC.

My question is what's the best way to swap settings between the two 
computer records and swap any software installed?  Ideally I'd like it 
in the form of a function where I can pass the two p_id's and return a 
boolean reflecting success (true) or fail (false).

Currently I do this manually with:

update pieces set p_name = 'LSALES1', p_location = 'Mike Haley', p_site 
= 'L' where p_id = 724;
update pieces set p_name = 'SPARE', p_location = 'spare', p_site = 'L' 
where p_id = 305;

update pieces set p_owner = 724 where p_owner = 305 and p_type in (
 select hwt_id from hw_types where hwt_cat in (
 select hwc_id from hw_categories where hwc_hwg_id = 7));

The hw_types and hw_categories select all O/S and application software.
This doesn't put any software currently on 305 onto 724 which would be 
nice.

(I'm not after someone to do my work for me, but a good starting point 
would be very much appreciated)

--
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Query performance problem

[George Weaver]

Only specifying a default value does not prevent a NULL from being entered 
either through accident or ignorance:

jan28-05=# create table test (foo text, foo1 int4 default(0));
CREATE TABLE
jan28-05=# insert into test values('a',1);
INSERT 98685 1
jan28-05=# insert into test values('b',4);
INSERT 98686 1
jan28-05=# insert into test values('c',NULL);
INSERT 98687 1
jan28-05=# insert into test values('d');
INSERT 98688 1
jan28-05=# select * from test;
foo | foo1
-+--
a   |1
b   |4
c   |
d   |0
(4 rows)
George
- Original Message - 
From: "Kenneth Gonsalves" <[EMAIL PROTECTED]>
To: "Richard Huxton" 
Cc: <[EMAIL PROTECTED]>; 
Sent: Thursday, March 17, 2005 11:19 PM
Subject: Re: [SQL] Query performance problem

On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
Not necessarily. NOT NULL here helps to ensure you can add values
together without the risk of a null result. There are plenty of
"amount" columns that should be not-null (total spent, total
ordered etc).
that makes sense - but is it necessary to have a not null constraint
when there is a default value?
--
regards
kg
http://www.livejournal.com/users/lawgon
tally ho! http://avsap.sourceforge.net
àà à!
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Query performance problem

[Stephan Szabo]

On Fri, 18 Mar 2005, Kenneth Gonsalves wrote:

> On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
>
> > Not necessarily. NOT NULL here helps to ensure you can add values
> > together without the risk of a null result. There are plenty of
> > "amount" columns that should be not-null (total spent, total
> > ordered etc).
>
> that makes sense - but is it necessary to have a not null constraint
> when there is a default value?

It's also an added check which prevents you from explicitly setting the
value to NULL in an insert or update, since
"insert into foo(col1) values (NULL);" shouldn't insert the default value
into col1.  This is relatively minor generally, but if you have queries
whose behavior is broken by NULLs (things using IN/NOT IN for example)
it's better to be safe.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Query performance problem

[PFC]

DEFAULT applies to INSERTs, NOT NULL applies to UPDATEs too.
	In MySQL it applies to both (ie. if you UPDATE to an invalid value, it  
sets it to 'something').

	NOT NULL without default is useful when you want to be sure you'll never  
forget to put a value in that column, when there is no meaningful default.  
Also for foreign keys : what would be the default value of a foreign key ?

gnari

---(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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings