Re: [SQL] Functions with Null Arguments?

[Thomas Swan]

At 10:12 AM 8/15/2000, DalTech - CTE wrote:
I would try using
COALESCE(list) which will return the
first non-NULL in the list.
That worked! Thanks!

The second part is why does postgresql not evaluate the function if one
of the arguments is null?

Re: [SQL] My Indices doesn't work

[Stephan Szabo]

First, make sure you ran vacuum analyze to update
the statistics for the table.

If a large portion of your table is going to be scanned,
Seq Scan is often faster than Index Scan due to possibly 
random seeks within the heap file (the transaction commit
state isn't in the index, so there is still a read from the
heap to check if it's valid).  The optimizer seems to think
333 records match num>2.  Is this reasonable?

Stephan Szabo
[EMAIL PROTECTED]

On Tue, 15 Aug 2000, Martin Dolog wrote:

> Hi *,
> 
> I have pgsql7.0.2 on Linux2.2.16 and table with following indices:
> 
> CREATE TABLE T1 (NUM INT NOT NULL, NAME VARCHAR(10) NOT NULL, POP
> VARCHAR(10) NOT NULL);
> CREATE INDEX T1_I_NUM ON T1(NUM);
> CREATE INDEX T1_I_NAME ON T1(NAME);
> CREATE INDEX T1_I_POP ON T1(POP);
> 
> ... and some data, but I really don't understant how indices work, look
> at that:
> 
> template1=# explain select * from t1 where num=2;
> NOTICE:  QUERY PLAN:
> Index Scan using t1_i_num on t1  (cost=0.00..8.14 rows=10 width=28)
> 
> what is ok, ***BUT***
> 
> template1=# explain select * from t1 where num>2;
> NOTICE:  QUERY PLAN:
> Seq Scan on t1  (cost=0.00..22.50 rows=333 width=28)
> 
> 
> WHY SEQ SCAN ?!!?
> 
> 
> thank you
> 
> -- 
> 
> #md
> 

Re: [SQL] copy from

[Stephan Szabo]

Sort of.  You can give the field a default value of
nextval() which means that if you do not specify
the column in an insert, it automatically gets the default
value which should be the next value in the sequence.
Note, that not putting the column is different from inserting a
NULL into the field.

(Example:
sszabo=# create sequence xyzseq; 
CREATE
sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
int);
CREATE
sszabo=# insert into xyzseqtest (b) values (2);
INSERT 172188 1
sszabo=# insert into xyzseqtest (b) values (3);
INSERT 172189 1
sszabo=# select * from xyzseqtest;
 a | b 
---+---
 1 | 2
 2 | 3
(2 rows)
)

There are issues about this dealing with rules and triggers where another
row may be inserted or the default may be evaluated a second time where
you want to get the value you just inserted back, but in general it works.

On Tue, 15 Aug 2000, Adam Lang wrote:

> Hmmm... well, I don't think I have an "explicit" nextval.  I created the
> table and then I did a create sequence broker_id;
> 
> Are you implying that I can set the field to automatically create a nextval?

Re: [SQL] PL/PGSQL Function problem.

[Stephan Szabo]

Actually, you can't do too much about it in PL/PGSQL,
because IIRC there isn't a way right now to do that.

I don't remember when PL/TCL came into existance (don't
know TCL), but you'd be able to do it in that, and 
you can also do it from SPI in a C function.

And finally, the obligatory upgrade message... Upgrade
to 7.0.2 if you can, it's nice and stays crunchy in 
milk. :)

Stephan Szabo
[EMAIL PROTECTED]

On Tue, 15 Aug 2000, Dirk Elmendorf wrote:

> RedHat 6.2 /Postgres 6.53
> I'm still very new at PL/PGSQL but I have looked thru all the 
> documentation I could find before sending this in.
> 
> 
> I have a function that I would like to be able to apply to multiple 
> tables without having to duplicate the code. I would like to be able 
> to pass in the table name I would like to apply the function to. I 
> cannot seem to get this to work.  Below is what I have so far.  I 
> would like to be able to have
> 
> computers_equal(comp_one,comp_two,comp_table_one,comp_table_two);
> 
> I cannot seem to find a way to pass in a text string to be used in 
> the select into statements.
> 
> Anyone got any ideas on how I fix this other that writing a function 
> for all the permutations (ordered_parts vs used_parts , ordered_parts 
> vs  new_parts, used_parts vs ordered_parts, new_parts vs 
> ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts)
> 
> Any advice would be appreciated.

[SQL] Re: Copwatch database

[Qiron Adhikary]

[EMAIL PROTECTED] wrote:
> 
> Our non-profit organization needs a skilled volunteer to create a
> searchable database of abusive cops.  Any SQL-type database program is
> OK- others may be suitable also.  We've already compiled the
> questions/outline around which the database inputs will be structured.
> 
> Programming credit will be prominently given upon request, and/or
> references will be provided to parties selected by the programmer.
> 
> This is not a left-wing, knee-jerk anti-cop endeavor, but a serious,
> long-term attempt to foster professional accountability within the ranks
> of law enforcement.  We've already been mentioned in the Village Voice
> and the LA Weekly.
> 
> Please forward this query to any acquaintances who may be interested in
> this type of subject.
> 
> Thanks in advance for your assistance.

have you had a suitable response?  i might be able to help.

thanks,

q

Re: [SQL] Use a rule or a transaction

[Antoine Reid]

On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> Hey Antoine,
> 
> I am using 7.0.2, and I would be in your debt if you created a working
> example.  Basically, the structure of the query I would like to do is as
> such (there are six modules in the module table for each quarter:
> a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.).  A student comes to register
> and selects all of the classes that are available in a given module, and
> then hits submit.  Information will be inserted into the modules table as
> follows (in PHP):

[snip]
I am not really familiar with PHP (installing/admining, but not programming 
with it...)..  At any rate, you will want to check the success/failure of 
every insert/update/delete...

Here is a working example of such tables, including the reference count AND
limit check.  This was tested with 7.0.2 on linux, i386.  Make sure you have
plpgsql installed in that database

> If you could give me a working example of a trigger/function that would
> simplify this, that would be fantastic.

here we go, broken in sections:

# Here, the tables:
# I made those simple for demonstration purposes.

CREATE TABLE "modules" (
"name" character varying(32) NOT NULL,
"class_id" int4 NOT NULL,
PRIMARY KEY ("name")
);

CREATE TABLE "classes" (
"class_id" int4 NOT NULL,
"usage" int4 NOT NULL,
"max" int4 NOT NULL,
PRIMARY KEY ("class_id")
);

# Now, the different plpgsql functions:

# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';

# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';

# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';


# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules"  FOR EACH ROW EXECUTE 
PROCEDURE "increment_classes" ();

CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules"  FOR EACH ROW EXECUTE 
PROCEDURE "decrement_classes" ();

CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes"  FOR EACH ROW EXECUTE 
PROCEDURE "check_limit" ();



So there you go.. please note the following:

- The classes table should have a trigger to make sure you don't insert a row
with a usage != 0.

- There should be a trigger on UPDATE on modules that would decrement the old
class, and increment the new one.  The triggers on classes will already check
that the new one is not already full..

- The trigger on INSERT on modules should return an error if the class_id
does not exist..


These are left as an exercise to the reader... (especially because i'm not sure how to 
do that 
last one... :-> )

> Thanks,
> Kurt

hope this helps
antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

Re: [SQL] Functions with Null Arguments?

[Thomas Swan]

At 01:24 PM 8/15/2000, DalTech - CTE wrote:
Known bug in some versions. 
I believe it was fixed in v7.x, though.
 
Cheers

- Original Message - 
From: Thomas
Swan 
To: DalTech - CTE ;
Thomas Swan 
Cc: Pgsql-sql 
Sent: Tuesday, August 15, 2000 1:43 PM
Subject: Re: [SQL] Functions with Null Arguments?


At 10:12 AM 8/15/2000, DalTech - CTE
wrote:
I would try using COALESCE(list) which
will return the first non-NULL in the list.
That worked! Thanks!


The second part is why does postgresql not evaluate the function if
one of the arguments is null?

I'm using version 7.0.2 (x86 Linux)

Re: [SQL] Use a rule or a transaction

[Antoine Reid]

On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote:
> Hello,
> 
> I am creating a web based (using php) class registration database with three
> tables directly effected by the registration process:

[snip]

> My question is, would it be better (faster) to use rules or to use a
> transaction.

How about using triggers?  I have used pl/pgsql triggers in the past to do
similar things.  I was not checking a maximum but definitely was keeping a
reference count.  What you want is a trigger that increments on insert,
decrement on delete, and on update, IF the id changed, decrement the old
one, increment the new one..  I suspect you could also have another trigger,
on update on class, that would abort the transaction if class.size ever
becomes bigger than maxsize..

I can't really comment on performance though.

> Is there a faster way to do this with rules or a better way to do it
> otherwise.
> 
> TIF,
> Kurt

Hope it helps
Antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

Re: [SQL] Use a rule or a transaction

[Antoine Reid]

On Tue, Aug 15, 2000 at 02:52:24PM -0400, Madel, Kurt wrote:
> Hello Antoine,
> 
> I thought that triggers only worked for one table at a time.  In this case,
> I want the class.size incremented and checked for maxsize when I perform an
> update or insert on the module table.  I was under the impression that a
> trigger would only work if I was performing an insert or update on the same
> table that I wanted the trigger to act on.
> 
> Please let me know if I am way off in regards to triggers.
> 
> thanks,
> Kurt
> 

Hi!

Well, the trigger, basically is a function that will be executed when you act
on one table.

If I understand you correctly, you have a table that is a list of classes.
for each class, you want to have a 'reference count' (current usage) and a 
maximum. Trying to take your own words, 'module' is a list of items that are
to be put member of 'classes'. classes is the table that has the maximum and
current usage numbers.

Assuming this is the case, what you do is following: 
(pseudo code.. not actual sql statements!)

on insert on modules, update classes set current_usage=current_usage + 1 where
the class_id is equal to the id of the row you just added in modules.

on delete on modules, update classes set current_usage=current_usage - 1 ...

on update on modules, IF-and-only-if the class_id changed, increment the new class,
and decrement the old class.


This all takes care of keeping the refcount in classes up to date.

Now, for the maximum.. since your triggers on modules will always do an update
on classes, we can create triggers on update on classes to make sure the maximum
is not over..


create another trigger;

create a trigger AFTER update on classes.  abort the transaction with a proper 
message if class.current_usage > class.maximum.
abort the transaction with proper message if class.current_usage < 0 (this shouldn't
happen!)


I have implemented ref counts in the past, I could probably come up with a working
example really quick, if you want.  I suspect the maximum check shouldn't be too
hard either..

Oh BTW, this should work on 7.0.2, probably on 7.0;  I really don't know about 6.x.x..


hope this helps
antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

[SQL] sql programming

[Michael Wagner]

We need to export an SQL database to Excel.  Is this 
within your scope and what might your cost be?
 
Please respond to Dan Beach
 
[EMAIL PROTECTED]

Re: [SQL] copy from

[Stephan Szabo]

On Tue, 15 Aug 2000, Adam Lang wrote:

> Gotcha. Now, if I do an insert and just don't specify that field at all, it
> will, use the default value, correct?  As long as I don't "touch" the field
> with anything it uses the default.

Right, as long as you don't specify the field in the column list.

There's a special case I should mention.  If you want to insert only
default values into all columns, the correct way is:
"insert into table default values" -- It usually doesn't come up,
but could if you had a table that was only a sequence value;

Re: [SQL] sql programming

[Webb Sprague]

Make it text, then import it.   You will have lots of
tables.  Send the check to :)

--- Michael Wagner <[EMAIL PROTECTED]>
wrote:
> We need to export an SQL database to Excel.  Is this
> within your scope and what might your cost be?
> 
> Please respond to Dan Beach
> 
> [EMAIL PROTECTED]
> 


__
Do You Yahoo!?
Yahoo! Mail – Free email you can access from anywhere!
http://mail.yahoo.com/

Re: [SQL] sql programming

[Prasanth A. Kumar]

"Michael Wagner" <[EMAIL PROTECTED]> writes:

> We need to export an SQL database to Excel.  Is this within your scope and =
> what might your cost be?
> 
> Please respond to Dan Beach


Why not just save it to text CSV format and Excel can then easily
import it.

-- 
Prasanth Kumar
[EMAIL PROTECTED]

Re: [SQL] postgresql and java2

[Peter Mount]

First sorry for the late reply, I only get this 
list from home, and just got email working again.
 
I was working on ARRAY and STRUCT for 7.0, but it a 
snag. I couldn't get a grips with what the spec meant, and couldn't work out how 
the SQL3 ARRAY type relates to our arrays. From the documentation I have (JDBC 
API Tutorial and Reference, Second Edition) it mentions things like CREATE TYPE 
statements, etc. I'm not sure if we could implement them purely within 
JDBC/Java.
 
Peter
 
-- Peter T Mount  [EMAIL PROTECTED], [EMAIL PROTECTED]Homepage: http://www.retep.org.uk Contact details: http://petermount.comPostgreSQL JDBC: http://www.retep.org.uk/postgres/Java 
PDF Generator: http://www.retep.org.uk/pdf/

  - Original Message - 
  From: 
  JavierG 
  To: [EMAIL PROTECTED] ; 
  [EMAIL PROTECTED] 
  Sent: Wednesday, August 02, 2000 9:20 
  PM
  Subject: [SQL] postgresql and java2 

  
  
  Hi:
   
  Does 
  anybody knows if the JDBC driver maps the new SQL 3 types (ARRAY and STRUCT) 
  with the proprietary array and user-defined types of PostgreSQL? To put it 
  another way: when I access an array (for instance) in PostgreSQL through the 
  JDBC driver, does the driver returns a SQL 3 ARRAY (java.sql.Array) or a Java 
  Object (java.lang.Object)?  
  
  I’m 
  sorry if this sounds “newbie”, but this is the first time I use an 
  ORDMS…
   
  Thanks 
  in advance
    
  J.

[SQL] My Indices doesn't work

[Martin Dolog]

Hi *,

I have pgsql7.0.2 on Linux2.2.16 and table with following indices:

CREATE TABLE T1 (NUM INT NOT NULL, NAME VARCHAR(10) NOT NULL, POP
VARCHAR(10) NOT NULL);
CREATE INDEX T1_I_NUM ON T1(NUM);
CREATE INDEX T1_I_NAME ON T1(NAME);
CREATE INDEX T1_I_POP ON T1(POP);

... and some data, but I really don't understant how indices work, look
at that:

template1=# explain select * from t1 where num=2;
NOTICE:  QUERY PLAN:
Index Scan using t1_i_num on t1  (cost=0.00..8.14 rows=10 width=28)

what is ok, ***BUT***

template1=# explain select * from t1 where num>2;
NOTICE:  QUERY PLAN:
Seq Scan on t1  (cost=0.00..22.50 rows=333 width=28)


WHY SEQ SCAN ?!!?


thank you

-- 

#md

[SQL] Language dependent fields

[Volker Krebs]

Hi,

I want the content of a field to be language dependent.

Therfore we have a Translation table which looks like this:

tln_id  tln_lng_id  tln_text
-
1   de  hallo
1   en  hello
2   de  Herr
2   en  Mister

any language dependent fields reference to this table e.g.

table customer

cst_id  cst_sometext_tln_id

10001

I've created a view to get the text:

CREATE VIEW locale_customer AS
SELECT customer.*, tln_text, tln_lng_id FROM customer,translation
WHERE tln_id=cst_sometext_tln_id;

this works fine for tables with only one language dependent field.
But if I have more than one field I've got a problem.

e.g.

table customer2

cst_id  cst_sometext_tln_id cst_sometext2_tln_id

10001   2

how can I create a proper view, to get both tln_text fields ??

CREATE VIEW locale_customer2 AS
SELECT customer2.*, tln_text, tln_lng_id FROM customer,translation
WHERE tln_id=cst_sometext_tln_id OR tln_id=cst_sometext2_tln_id;
gives me all tln_text fields, but I can't distinguish between them.

Any help appriciated.

thanks
Volker

[SQL] 8K Limit, whats the best strategy?

[Keith Wong]

Hi everyone,

Just wondering what strategies people have used to get around the 8K row 
limit in Postgres.
If anyone has been troubled by this limitation before and has a nice 
solution around it, I would love to hear it.

My application is a web-based system in which needs to store large amounts 
of text.
The text stored needs to searchable as well.

Cheers,
Keith.

Re: [SQL] Functions with Null Arguments?

[DalTech - CTE]

I would try using COALESCE(list) which will return the 
first non-NULL in the list.

  - Original Message - 
  From: 
  Thomas Swan 
  
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, August 15, 2000 3:18 
  AM
  Subject: [SQL] Functions with Null 
  Arguments?
  Is there anyway way to get the following to 
  work?table fooid | name1   
  | name2---+-+-- 0 | Abe | 
  NULL 1 | William | Billcreate function 
  prefname(text, text)returns text 
  as'declarename1 
  alias for 
  $1;name2 
  alias for 
  $2;beginif 
  name2 
  isnullthenreturn 
  name1;elsereturn 
  name2;end 
  if;end;'language 'plpgsql'; if I 
  doselect id, name1, name2, prefname(name1, name2) as pref from 
  foo;I wind up with id | 
  name1   | name2 | 
  prefname---+-+---+--- 0 | 
  Abe | NULL  | NULL 1 | William | 
  Bill  | Billinstead of id | name1   | name2 | 
  prefname---+-+---+--- 0 | 
  Abe | NULL  | Abe 1 | William | 
  Bill  | BillI assume this has to do with 
  Postgres not executing the function is one of the arguments is 
  missing...- - Thomas Swan 
    - 
  Graduate Student  - Computer Science- The University of 
  Mississippi- - "People can be categorized into two fundamental - 
  groups, those that divide people into two groups - and those that don't." 

Re: [SQL] copy from

[Adam Lang]

I get it.  I upload to the temp table, then for the select/insert I just do
the nextval(seq_id) for the primary key field, correct?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, August 14, 2000 5:17 PM
Subject: Re: [SQL] copy from


>
> One way would be to copy the data into a temporary table that doesn't
> have the id field, and then
> insert into table (fields) select * from temptable;
>
> Stephan Szabo
> [EMAIL PROTECTED]
>
> On Mon, 14 Aug 2000, Adam Lang wrote:
>
> > I need additional help involving the copy from command
> >
> > I have a database with several fields: id, name, address, zip
> >
> > id is the primary key and I use nextval to get a value.
> >
> > The file I want to upload is in comma separated value, that does not
have a
> > primry key (or an id) field.
> >
> > How would I use copy from and assign the correct id number?
> >
> > Adam Lang
> > Systems Engineer
> > Rutgers Casualty Insurance Company
> >

[SQL] data integrity

[Sandis]

Hello pgsql-sql,

sorry for my question that is obviously a kind of lame and not
related directly to postges, but i
never dealt with it before & need an idea what to do right now.

there is a db with a few tables that have related (joined) fields.
if record in a one table (auxiliary) gets deleted (along with it's primary key),
reference to it in another table (main) points to nowhere.
then doing a complex query with a join on that tables doesn't return
such corrupted rows that contains references to deleted rows in other
tables.

what's the solution? should i check if this 'project', for example,
have 'contacts' associated with it, and do not allow user to delete it,
than show that 'contacts' list and ask if she wants delete all of them?
or may be dont show a list, but just warn that there is $number
'contacts' related to this 'project', do you want to delete them all?

how about joins in delete query?
DELETE FROM projects, contacts WHERE projects.contact = contacts.id
AND projects.id = '$id';

he, i wrote the answer myself. may be dont send it at all?

can someone suggest some good online resource where such common sql topics
discussed?

thanks
--:)--
Best regards,
 Sandis  mailto:[EMAIL PROTECTED]

Re: [SQL] data integrity

[Jesus Aneiros]

On Tue, 15 Aug 2000, Sandis wrote:

> there is a db with a few tables that have related (joined) fields. if
> record in a one table (auxiliary) gets deleted (along with it's

I would say this is the primary table.

> primary key), reference to it in another table (main) points to

And this is the secondary tab,le which references the other.

> what's the solution? should i check if this 'project', for example,

The solution is called referential integrity. you should use the foreign
key and references clause with cascade.

> can someone suggest some good online resource where such common sql topics
> discussed?

I would like to know such a place.

Jesus.

Re: [SQL] Language dependent fields

[Volker Krebs]

Hi,

thanks, that works ok.
But has anybody some suggestions how this
behaves performance wise, if I have like 10 language dependent fields
and translation t1 till translation t10.

Volker

Renato De Giovanni wrote:
> 
> Maybe something like this:
> 
> CREATE VIEW locale_customer2 AS
> SELECT customer2.*, t1.tln_lng_id, t1.tln_text as text1, t2.tln_text as
> text2
> FROM customer, translation t1, translation t2
> WHERE t1.tln_id=cst_sometext_tln_id
> AND t2.tln_id=cst_sometext2_tln_id
> AND t1.tln_lng_id = t2.tln_lng_id;
> 
> HTH,
> --
> Renato
> Sao Paulo - SP - Brasil
> [EMAIL PROTECTED]
> 
> > Hi,
> >
> > I want the content of a field to be language dependent.
> >
> > Therfore we have a Translation table which looks like this:
> >
> > tln_id  tln_lng_id  tln_text
> > -
> > 1   de  hallo
> > 1   en  hello
> > 2   de  Herr
> > 2   en  Mister
> >
> > any language dependent fields reference to this table e.g.
> >
> > table customer
> >
> > cst_id  cst_sometext_tln_id
> > 
> > 10001
> >
> > I've created a view to get the text:
> >
> > CREATE VIEW locale_customer AS
> > SELECT customer.*, tln_text, tln_lng_id FROM customer,translation
> > WHERE tln_id=cst_sometext_tln_id;
> >
> > this works fine for tables with only one language dependent field.
> > But if I have more than one field I've got a problem.
> >
> > e.g.
> >
> > table customer2
> >
> > cst_id  cst_sometext_tln_id cst_sometext2_tln_id
> > 
> > 10001   2
> >
> > how can I create a proper view, to get both tln_text fields ??
> >
> > CREATE VIEW locale_customer2 AS
> > SELECT customer2.*, tln_text, tln_lng_id FROM customer,translation
> > WHERE tln_id=cst_sometext_tln_id OR tln_id=cst_sometext2_tln_id;
> > gives me all tln_text fields, but I can't distinguish between them.
> >
> > Any help appriciated.
> >
> > thanks
> > Volker

Re[2]: [SQL] data integrity

[Sandis]

Hello Jesus,

Tuesday, August 15, 2000, 4:04:40 PM, you wrote:

JA> The solution is called referential integrity. you should use the foreign
JA> key and references clause with cascade.

>> can someone suggest some good online resource where such common sql topics
>> discussed?

JA> I would like to know such a place.
JA> Jesus.

Thank you. I found what i need about referential integrity here
http://www.postgresql.org/mhonarc/pgsql-general/1999-08/msg00474.html

ye,in Access it needed to check 1 checkbox, but here one should write
some code..
--:)--
Best regards,
 Sandismailto:[EMAIL PROTECTED]

[SQL] ERROR: unexpected SELECT query in exec_stmt_execsql()

[Branden R. Williams]

Hi.  I am attempting to create a function to do some selects and return
some information.  Pretty straight forward.  No matter what I do, I always
get this error.  Here is my stored function and how I am calling it...

create function sp_list_categories(int4) returns varchar as '
DECLARE
postid ALIAS FOR $1;
parentid int4;
BEGIN

SELECT * FROM category WHERE id = postid;
parentid := SELECT parent FROM category WHERE id = postid;

IF parentid ISNULL THEN
RETURN
ELSE
SELECT sp_list_categories(parentid);
RETURN name;
END IF;

END;
' LANGUAGE 'plpgsql';


jimmydbase=# select sp_list_categories(20);
ERROR:  unexpected SELECT query in exec_stmt_execsql()

Any and all help would be appreciated!

Branden.

Re: [SQL] 8K Limit, whats the best strategy?

[Thomas Swan]

At 06:29 AM 8/15/2000, Keith Wong wrote:
Hi everyone,

Just wondering what strategies people have used to get around the 8K row
limit in Postgres.
If anyone has been troubled by this limitation before and has a nice
solution around it, I would love to hear it.

My application is a web-based system in which needs to store large
amounts of text.
The text stored needs to searchable as well.

You can you the type LZTEXT which is compressed text so your mileage will
vary.

The other option is to go in and recompile for a larger row size. 
Most of the people I have talked to had no problems with this
route.  I move the size limit on an x86 Linux box to 32K (the max)
and have not had any problems.   In the next release, I don't
believe the row size limit will exist (correct me if I'm wrong). 

Let me know the system your on, and I will try to help if I can.

- 
- Thomas Swan
  
- Graduate Student  - Computer Science
- The University of Mississippi
- 
- "People can be categorized into two fundamental 
- groups, those that divide people into two groups 
- and those that don't."

Re: [SQL] 8K Limit, whats the best strategy?

[Adam Lang]

I believe you are correct.  Version 7.1 will supposedly eliminate the row
size.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Thomas Swan" <[EMAIL PROTECTED]>
To: "Keith Wong" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 11:37 AM
Subject: Re: [SQL] 8K Limit, whats the best strategy?


> At 06:29 AM 8/15/2000, Keith Wong wrote:
> >Hi everyone,
> >
> >Just wondering what strategies people have used to get around the 8K row
> >limit in Postgres.
> >If anyone has been troubled by this limitation before and has a nice
> >solution around it, I would love to hear it.
> >
> >My application is a web-based system in which needs to store large
amounts
> >of text.
> >The text stored needs to searchable as well.
>
> You can you the type LZTEXT which is compressed text so your mileage will
vary.
>
> The other option is to go in and recompile for a larger row size.  Most of
> the people I have talked to had no problems with this route.  I move the
> size limit on an x86 Linux box to 32K (the max) and have not had any
> problems.   In the next release, I don't believe the row size limit will
> exist (correct me if I'm wrong).
>
> Let me know the system your on, and I will try to help if I can.
>
> -
> - Thomas Swan
> - Graduate Student  - Computer Science
> - The University of Mississippi
> -
> - "People can be categorized into two fundamental
> - groups, those that divide people into two groups
> - and those that don't."

Re: [SQL] copy from

[Adam Lang]

Hmmm... well, I don't think I have an "explicit" nextval.  I created the
table and then I did a create sequence broker_id;

Are you implying that I can set the field to automatically create a nextval?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 12:34 PM
Subject: Re: [SQL] copy from


> Well, if you define your main table to have a serial, or an explicit
> default nextval(seqid), you won't even need to do that.  Just leave
> the column off the insert list and the default should fill it for you.
>
> Stephan Szabo
> [EMAIL PROTECTED]
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > I get it.  I upload to the temp table, then for the select/insert I just
do
> > the nextval(seq_id) for the primary key field, correct?

[SQL] PL/pgSQL

[Jie Liang]

Hi,

Is anybody know how to declare a explicit cursor in PL/pgSQL (not in
SQL) ???
syntax:
declare
cursor mycur is select statement ?? it won't work!

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

[EMAIL PROTECTED]
www.ipinc.com

[SQL] PL/PGSQL Function problem.

[Dirk Elmendorf]

RedHat 6.2 /Postgres 6.53
I'm still very new at PL/PGSQL but I have looked thru all the 
documentation I could find before sending this in.


I have a function that I would like to be able to apply to multiple 
tables without having to duplicate the code. I would like to be able 
to pass in the table name I would like to apply the function to. I 
cannot seem to get this to work.  Below is what I have so far.  I 
would like to be able to have

computers_equal(comp_one,comp_two,comp_table_one,comp_table_two);

I cannot seem to find a way to pass in a text string to be used in 
the select into statements.

Anyone got any ideas on how I fix this other that writing a function 
for all the permutations (ordered_parts vs used_parts , ordered_parts 
vs  new_parts, used_parts vs ordered_parts, new_parts vs 
ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts)

Any advice would be appreciated.




create table ordered_parts
(
computer_number int4,
part_number int4
);

create table used_parts
(
computer_number int4,
part_number int4
);

create tabel new_parts
(
computer_number int4,
part_number int4
);

insert into ordered_parts values(1,401);
insert into ordered_parts values(1,402);

insert into used_parts values(2,401);
insert into used_parts values(2,402);
insert into used_parts values(3,401);
insert into used_parts values(3,403);

insert into new_parts values(4,401);
insert into new_parts values(4,402);
insert into new_parts values(5,401);
insert into new_parts values(5,403);


CREATE FUNCTION computers_equal(int4 , int4) RETURNS bool AS '
DECLARE
true CONSTANT bool DEFAULT ''t'';
false CONSTANT bool DEFAULT ''f'';
parts_list RECORD;
BEGIN
SELECT INTO parts_list part_number,part_label from 
ordered_parts where computer_number = $1 except select 
part_number,part_label from used_parts where computer_number= $2;
IF NOT FOUND THEN
-- now test the reverse
SELECT INTO parts_list 
part_number,part_label from used_parts  where computer_number = $2 
except select part_number,part_label from ordered_parts where 
computer_number= $1;
IF NOT FOUND THEN
RETURN true;
ELSE
RETURN false;
END IF;

ELSE
RETURN false;
END IF;

END;
' LANGUAGE 'plpgsql';

--This will list all the comptuers that match
select distinct on computer_number computer_number from used_parts 
where computers_equal(1,computer_number) and computer_number!=1;

/*
--this doesn't work
CREATE FUNCTION computers_equal(int4 , int4,text,text) RETURNS bool AS '
DECLARE
true CONSTANT bool DEFAULT ''t'';
false CONSTANT bool DEFAULT ''f'';
parts_list RECORD;
BEGIN
SELECT INTO parts_list part_number,part_label from $3 
where computer_number = $1 except select part_number,part_label from 
$4 where computer_number= $2;
IF NOT FOUND THEN
-- now test the reverse
SELECT INTO parts_list 
part_number,part_label from $4  where computer_number = $2 except 
select part_number,part_label from $3 where computer_number= $1;
IF NOT FOUND THEN
RETURN true;
ELSE
RETURN false;
END IF;

ELSE
RETURN false;
END IF;

END;
' LANGUAGE 'plpgsql';
select distinct on computer_number computer_number from used_parts 
where computers_equal(1,computer_number,ordered_parts,used_parts) and 
computer_number!=1;
*/
-- 
__
Dirk Elmendorf, CTEMain: 210-892-4000
Rackspace Managed Hosting
Weston Center   Fax: 210-892-4329
112 East Pecan, Suite 600Email:[EMAIL PROTECTED]
San Antonio,  TX 78205 

[SQL] Use a rule or a transaction

[Madel, Kurt]

Hello,

I am creating a web based (using php) class registration database with three
tables directly effected by the registration process:

1) class
2) student
3) module

The module table has a field for every module (or class period) for every
student that registers for a given year.  When a student registers for a
given class, the class.id for that class is placed in the respective module
field.

However, if class.size is equal to class.maxsize, then I don't want to allow
that class.id to be inserted into the module table.  If the class.size is
ok, then the class.id should be added to the given module field, module.a_q1
for example AND the class.size needs to be incremented by one (further, the
class.size needs to be decremented if a student drops a class).

My question is, would it be better (faster) to use rules or to use a
transaction.

Also, if I use a transaction, it would look something like this:

>begin work;
>insert into module (a_q1) values (1);
>update class set class.size=class.size+1 where class.id=1 and
class.sizecommit work;

>begin work;
>insert into module (a_q2) values (3);
>update class set class.size=class.size+1 where class.id=1 and
class.sizecommit work;

...and so on
If I did it this way, I would have to do this for 4 quarters by 6 modules
(24 times).
I feel like I have to do it one by one, because a student should only not
get registered for classes that are full.  I don't want to lump all 24
modules into one transaction and then have the student start the
registration process entirely over because one class was full. 

Is there a faster way to do this with rules or a better way to do it
otherwise.

TIF,
Kurt

Re: [SQL] 8K Limit, whats the best strategy?

[Poul L. Christiansen]

I've just split the text up (in 8 pieces), so it fits into 8K rows. But thats
only a viable solution if your text is less than a couple of 100K's.

You could try to be a daredevil and use the Toast code, even if it's beta. But
I don't know how far the Toast project has come.

Keith Wong wrote:

> Hi everyone,
>
> Just wondering what strategies people have used to get around the 8K row
> limit in Postgres.
> If anyone has been troubled by this limitation before and has a nice
> solution around it, I would love to hear it.
>
> My application is a web-based system in which needs to store large amounts
> of text.
> The text stored needs to searchable as well.
>
> Cheers,
> Keith.

Re: [SQL] copy from

[Adam Lang]

Gotcha. Now, if I do an insert and just don't specify that field at all, it
will, use the default value, correct?  As long as I don't "touch" the field
with anything it uses the default.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Adam Lang" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 15, 2000 2:00 PM
Subject: Re: [SQL] copy from


>
> Sort of.  You can give the field a default value of
> nextval() which means that if you do not specify
> the column in an insert, it automatically gets the default
> value which should be the next value in the sequence.
> Note, that not putting the column is different from inserting a
> NULL into the field.
>
> (Example:
> sszabo=# create sequence xyzseq;
> CREATE
> sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
> int);
> CREATE
> sszabo=# insert into xyzseqtest (b) values (2);
> INSERT 172188 1
> sszabo=# insert into xyzseqtest (b) values (3);
> INSERT 172189 1
> sszabo=# select * from xyzseqtest;
>  a | b
> ---+---
>  1 | 2
>  2 | 3
> (2 rows)
> )
>
> There are issues about this dealing with rules and triggers where another
> row may be inserted or the default may be evaluated a second time where
> you want to get the value you just inserted back, but in general it works.
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > Hmmm... well, I don't think I have an "explicit" nextval.  I created the
> > table and then I did a create sequence broker_id;
> >
> > Are you implying that I can set the field to automatically create a
nextval?

RE: [SQL] Use a rule or a transaction

[Madel, Kurt]

Hello Antoine,

I thought that triggers only worked for one table at a time.  In this case,
I want the class.size incremented and checked for maxsize when I perform an
update or insert on the module table.  I was under the impression that a
trigger would only work if I was performing an insert or update on the same
table that I wanted the trigger to act on.

Please let me know if I am way off in regards to triggers.

thanks,
Kurt

-Original Message-
From: Antoine Reid [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 15, 2000 2:43 PM
To: Madel, Kurt
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Use a rule or a transaction


On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote:
> Hello,
> 
> I am creating a web based (using php) class registration database with
three
> tables directly effected by the registration process:

[snip]

> My question is, would it be better (faster) to use rules or to use a
> transaction.

How about using triggers?  I have used pl/pgsql triggers in the past to do
similar things.  I was not checking a maximum but definitely was keeping a
reference count.  What you want is a trigger that increments on insert,
decrement on delete, and on update, IF the id changed, decrement the old
one, increment the new one..  I suspect you could also have another trigger,
on update on class, that would abort the transaction if class.size ever
becomes bigger than maxsize..

I can't really comment on performance though.

> Is there a faster way to do this with rules or a better way to do it
> otherwise.
> 
> TIF,
> Kurt

Hope it helps
Antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

[SQL] % escape

[Jie Liang]

Hi,there,

Is anybody know  how I can escape % or ' (my column contains % or '  )
in my text field.

select mystring from textbook where mystring like .

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

[EMAIL PROTECTED]
www.ipinc.com

RE: [SQL] Use a rule or a transaction

[Madel, Kurt]

Hey Antoine,

I am using 7.0.2, and I would be in your debt if you created a working
example.  Basically, the structure of the query I would like to do is as
such (there are six modules in the module table for each quarter:
a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.).  A student comes to register
and selects all of the classes that are available in a given module, and
then hits submit.  Information will be inserted into the modules table as
follows (in PHP):


If you could give me a working example of a trigger/function that would
simplify this, that would be fantastic.

Thanks,
Kurt
-Original Message-
From: Antoine Reid [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 15, 2000 3:04 PM
To: Madel, Kurt
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Use a rule or a transaction


On Tue, Aug 15, 2000 at 02:52:24PM -0400, Madel, Kurt wrote:
> Hello Antoine,
> 
> I thought that triggers only worked for one table at a time.  In this
case,
> I want the class.size incremented and checked for maxsize when I perform
an
> update or insert on the module table.  I was under the impression that a
> trigger would only work if I was performing an insert or update on the
same
> table that I wanted the trigger to act on.
> 
> Please let me know if I am way off in regards to triggers.
> 
> thanks,
> Kurt
> 

Hi!

Well, the trigger, basically is a function that will be executed when you
act
on one table.

If I understand you correctly, you have a table that is a list of classes.
for each class, you want to have a 'reference count' (current usage) and a 
maximum. Trying to take your own words, 'module' is a list of items that are
to be put member of 'classes'. classes is the table that has the maximum and
current usage numbers.

Assuming this is the case, what you do is following: 
(pseudo code.. not actual sql statements!)

on insert on modules, update classes set current_usage=current_usage + 1
where
the class_id is equal to the id of the row you just added in modules.

on delete on modules, update classes set current_usage=current_usage - 1 ...

on update on modules, IF-and-only-if the class_id changed, increment the new
class,
and decrement the old class.


This all takes care of keeping the refcount in classes up to date.

Now, for the maximum.. since your triggers on modules will always do an
update
on classes, we can create triggers on update on classes to make sure the
maximum
is not over..


create another trigger;

create a trigger AFTER update on classes.  abort the transaction with a
proper 
message if class.current_usage > class.maximum.
abort the transaction with proper message if class.current_usage < 0 (this
shouldn't
happen!)


I have implemented ref counts in the past, I could probably come up with a
working
example really quick, if you want.  I suspect the maximum check shouldn't be
too
hard either..

Oh BTW, this should work on 7.0.2, probably on 7.0;  I really don't know
about 6.x.x..


hope this helps
antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

Re: [SQL] Functions with Null Arguments?

[Jesus Aneiros]

I think you could use a function like the one below and use the following
select:
select id, name1, name2, prefname(foo) as pref from foo;

create function prefname(foo)
returns text as'
declare
foo alias for $1;
begin
if foo.name2 is null
then
return foo.name1;
else
return foo.name2;
end if;
end;'
language 'plpgsql';

--
Jesus Aneiros Sosa
mailto:[EMAIL PROTECTED]
http://jagua.cfg.sld.cu/~aneiros

On Tue, 15 Aug 2000, Thomas Swan wrote:

> 
> Is there anyway way to get the following to work?
> 
> table foo
> id | name1   | name2
> ---+-+--
>   0 | Abe | NULL
>   1 | William | Bill
> 
> 
> create function prefname(text, text)
> returns text as'
> declare
>  name1 alias for $1;
>  name2 alias for $2;
> begin
>  if name2 isnull
>  then
>  return name1;
>  else
>  return name2;
>  end if;
> end;'
> language 'plpgsql';
> 
> if I do
> 
> I wind up with
> 
> id | name1   | name2 | prefname
> ---+-+---+---
>   0 | Abe | NULL  | NULL
>   1 | William | Bill  | Bill
> 
> instead of
> 
> id | name1   | name2 | prefname
> ---+-+---+---
>   0 | Abe | NULL  | Abe
>   1 | William | Bill  | Bill
> 
> I assume this has to do with Postgres not executing the function is one of 
> the arguments is missing...
> 
> 
> -
> - Thomas Swan
> - Graduate Student  - Computer Science
> - The University of Mississippi
> -
> - "People can be categorized into two fundamental
> - groups, those that divide people into two groups
> - and those that don't."

Re: [SQL] % escape

[Jesus Aneiros]

To escape % use \\% as in '50\\%', to escape ' use '' as in 'o''conell',
retrieves o'conell

Regards.

--
Jesus Aneiros Sosa
mailto:[EMAIL PROTECTED]
http://jagua.cfg.sld.cu/~aneiros

On Tue, 15 Aug 2000, Jie Liang wrote:

> Hi,there,
> 
> Is anybody know  how I can escape % or ' (my column contains % or '  )
> in my text field.
> 
> select mystring from textbook where mystring like .
> 
> --
> Jie LIANG
> 
> Internet Products Inc.
> 
> 10350 Science Center Drive
> Suite 100, San Diego, CA 92121
> Office:(858)320-4873
> 
> [EMAIL PROTECTED]
> www.ipinc.com
> 
> 
> 

RE: [SQL] Use a rule or a transaction

[Madel, Kurt]

Hey Antoine,

That is awesome, you just whipped that out, and I think it will work
beautifully. I will let you know.

Also, I think I have the last one licked

- The trigger on INSERT on modules should return an error if the class_id
does not exist..

I created the 'module.class_id' as a foreign key of the class table, so that
won't let a class.id that doesn't exist, to be inserted into the module
table.

Thanks for all your help, I can't wait to get home and test it out,

Kurt

-Original Message-
From: Antoine Reid [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 15, 2000 3:49 PM
To: Madel, Kurt
Cc: 'Antoine Reid'; [EMAIL PROTECTED]
Subject: Re: [SQL] Use a rule or a transaction


On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> Hey Antoine,
> 
> I am using 7.0.2, and I would be in your debt if you created a working
> example.  Basically, the structure of the query I would like to do is as
> such (there are six modules in the module table for each quarter:
> a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.).  A student comes to register
> and selects all of the classes that are available in a given module, and
> then hits submit.  Information will be inserted into the modules table as
> follows (in PHP):

[snip]
I am not really familiar with PHP (installing/admining, but not programming 
with it...)..  At any rate, you will want to check the success/failure of 
every insert/update/delete...

Here is a working example of such tables, including the reference count AND
limit check.  This was tested with 7.0.2 on linux, i386.  Make sure you have
plpgsql installed in that database

> If you could give me a working example of a trigger/function that would
> simplify this, that would be fantastic.

here we go, broken in sections:

# Here, the tables:
# I made those simple for demonstration purposes.

CREATE TABLE "modules" (
"name" character varying(32) NOT NULL,
"class_id" int4 NOT NULL,
PRIMARY KEY ("name")
);

CREATE TABLE "classes" (
"class_id" int4 NOT NULL,
"usage" int4 NOT NULL,
"max" int4 NOT NULL,
PRIMARY KEY ("class_id")
);

# Now, the different plpgsql functions:

# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';

# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';

# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA
now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';


# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules"  FOR EACH ROW
EXECUTE PROCEDURE "increment_classes" ();

CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules"  FOR EACH ROW
EXECUTE PROCEDURE "decrement_classes" ();

CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes"  FOR EACH ROW
EXECUTE PROCEDURE "check_limit" ();



So there you go.. please note the following:

- The classes table should have a trigger to make sure you don't insert a
row
with a usage != 0.

- There should be a trigger on UPDATE on modules that would decrement the
old
class, and increment the new one.  The triggers on classes will already
check
that the new one is not already full..

- The trigger on INSERT on modules should return an error if the class_id
does not exist..


These are left as an exercise to the reader... (especially because i'm not
sure how to do that 
last one... :-> )

> Thanks,
> Kurt

hope this helps
antoine

-- 
 o  Antoine Reid o>Alcohol and calculus   
<|> [EMAIL PROTECTED] <|don't mix. Never drink   |
 >\[EMAIL PROTECTED]  >\ and derive.   /<

[SQL] Accessing field properties

[Campbell, Scott]

Hey,

Is there a way to access the properties of a field through an SQL statement?
 i.e. wether field is a foreign key or if it is primary or varchar etc.

I am connecting to the database through PERL/DBI and need to access the
properties of the fields to build the web page around.

Thanks

Scott Campbell

RE: [SQL] Use a rule or a transaction

[Jesus Aneiros]

Create a trigger on module before update or insert. The philosophy of
postgres is to create a trigger based on a function previously created. 
Take a look at the docs, the part on procedural languages. The book
from Momjian has one or two examples on triggers creation.

--
Jesus Aneiros Sosa
mailto:[EMAIL PROTECTED]
http://jagua.cfg.sld.cu/~aneiros

On Tue, 15 Aug 2000, Madel, Kurt wrote:

> Hello Antoine,
> 
> I thought that triggers only worked for one table at a time.  In this case,
> I want the class.size incremented and checked for maxsize when I perform an
> update or insert on the module table.  I was under the impression that a
> trigger would only work if I was performing an insert or update on the same
> table that I wanted the trigger to act on.
> 
> Please let me know if I am way off in regards to triggers.
> 
> thanks,
> Kurt
> 
> -Original Message-
> From: Antoine Reid [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, August 15, 2000 2:43 PM
> To: Madel, Kurt
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] Use a rule or a transaction
> 
> 
> On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote:
> > Hello,
> > 
> > I am creating a web based (using php) class registration database with
> three
> > tables directly effected by the registration process:
> 
> [snip]
> 
> > My question is, would it be better (faster) to use rules or to use a
> > transaction.
> 
> How about using triggers?  I have used pl/pgsql triggers in the past to do
> similar things.  I was not checking a maximum but definitely was keeping a
> reference count.  What you want is a trigger that increments on insert,
> decrement on delete, and on update, IF the id changed, decrement the old
> one, increment the new one..  I suspect you could also have another trigger,
> on update on class, that would abort the transaction if class.size ever
> becomes bigger than maxsize..
> 
> I can't really comment on performance though.
> 
> > Is there a faster way to do this with rules or a better way to do it
> > otherwise.
> > 
> > TIF,
> > Kurt
> 
> Hope it helps
> Antoine
> 
> -- 
>  o  Antoine Reid o>Alcohol and calculus   
> <|> [EMAIL PROTECTED] <|don't mix. Never drink   |
>  >\[EMAIL PROTECTED]  >\ and derive.   /<
> 

Re: [SQL] copy from

[Stephan Szabo]

Well, if you define your main table to have a serial, or an explicit
default nextval(seqid), you won't even need to do that.  Just leave
the column off the insert list and the default should fill it for you.

Stephan Szabo
[EMAIL PROTECTED]

On Tue, 15 Aug 2000, Adam Lang wrote:

> I get it.  I upload to the temp table, then for the select/insert I just do
> the nextval(seq_id) for the primary key field, correct?