[SQL] Trying to write a function...

[Wim]

Hello,

I'm trying to write a function that add new rows to a table, but I don't 
succeed.
It's something like this:

CREATE FUNCTION f_addrtr (varchar(16),varchar(32)) RETURNS bool AS '
DECLARE
index int4;
BEGIN
index := 'nextval('s_routerid')';
INSERT INTO t_routers VALUES (index, $1, $2);
RETURN 1;
END;'
LANGUAGE 'plpgsql';

What am I doing wrong?


Cheers!

Wim.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Drop functions

[Wim Audenaert]

Hello,

I want to drop all functions from my data base. Is their an easy way to do 
this? Or did somebody wrote a function, or a sql script that deletes all 
functions?


Thanks a lot for your help.

Best regards,

Wim



---(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] primary key question

[Wim Ceulemans]

Carolyn Lu Wong wrote:
> 
> create table aaa(
> field1   not null,
> field2  ,
> ,
> primary key (field1, field2)
> );
> 
> Based on the above table definition, field2 allows null values. But
> after the table created based on the above script, field2 becomes not
> null. The only conclusion I come up with is setting the field as part of
> the primary key, PostgreSQL automatically sets the field to not null. Or
> is it something else?
> 
> Is this a feature or bug?

This is in sync with the SQL-92 spec as the following explains:

Quote from Tom Lane on pgsql-general yesterday:
> Two nulls are never considered equal, therefore the unique constraint
> does not trigger.
> 
> This is correct behavior according to SQL92 4.10.2:
> 
>  A unique constraint is satisfied if and only if no two rows in
>  a table have the same non-null values in the unique columns. In
>
>  addition, if the unique constraint was defined with PRIMARY KEY,
>  then it requires that none of the values in the specified column or
>  columns be the null value.
> 
> (The second sentence just says that PRIMARY KEY implies NOT NULL as well
> as UNIQUE.)
> 
> Another way to look at it is that the comparison to see whether the two
> NULLs are equal would yield NULL, and a NULL result for a constraint
> condition is not considered to violate the constraint.
> 
> Another way to look at it is that NULL means "I don't know what the
> value is", so if you don't know what the values in two rows really are,
> you don't know whether they're equal either.  I suppose you could make
> a case for either accepting or rejecting the UNIQUE constraint in that
> situation --- but SQL92 chose the "accept" decision, and I think that
> for the majority of practical applications they made the right choice.
> 
> If you don't like that behavior, possibly your column should be defined
> as NOT NULL.
> 

Regards
Wim

Re: [SQL] from not null field to nullable field?

[Wim Ceulemans]

Carolyn Lu Wong wrote:
> 
> I have a field in a table that has been defined 'not null'. Is it
> possible to remove this constraint? I don't see this option under 'alter
> table'.

update pg_attribute set attnotnull = 'f' where oid = oidofnotnullcolumn;
vacuum analyze;

Regards
Wim