A really nice feature of Postgreshttp://www.postgresql.org/ that I’ve been using lately is copying types. When you’re working on a project with a database, sometimes types will change for a column but, because we’re human beings, not all the functions that reference that column will be updated properly. This technique means that it won’t happen to you again (and believe me, it does happen a lot, especially with developers who don’t like databases.)
Instead of this
create table Example ( exampleid int not null ) CREATE OR REPLACE FUNCTION Example_insert_udf( _exampleid int not null ) RETURNS int AS $$ DECLARE _exampleDecid int BEGIN Insert into Example ( exampleid ) VALUES ( _exampleid ) RETURNING exampleid INTO _exampleDecid; RETURN _exampleDecid; END; $$ LANGUAGE plpgsql;
You can do this, which may be a bit more prolix, but it will save you no end of headaches.
CREATE OR REPLACE FUNCTION Example_insert_udf( _exampleid Example.exampleid%TYPE not null ) RETURNS Example.exampleid%TYPE AS $$ DECLARE _exampleDecid Example.exampleid%TYPE BEGIN Insert into contexts ( exampleid ) VALUES ( _exampleid ) RETURNING exampleid INTO _exampleDecid; RETURN _exampleDecid; END; $$ LANGUAGE plpgsql;
So all you have to do is replace an explicit declaration of type with a reference to the column type using the syntax:
wherever you wish to refer to the type. I don’t know if this is done with other databases like SQL Server, I’ve not seen it and certainly it wasn’t there by the 2005 version, but I haven’t worked with 2008. Postgres is chocko full of good ideas, I wish I could always work with it.