Thanks again everyone. I thought pgSQL might be case sensitive so I tried all variations (mentioned all of this in prev msgs) Oh...I just figured out what it was. I did created the proc successfully. The pgAdmin III interface saved it, moved it from the procedures hive to the functions hive. Each time the proc vanished on me I figured that pgAdmin had timed out or had a bug or that I'd hit the cancel button in error. So pgSQL is case sensitive and that include keywords like UPDATE and SET. There what worked, for the record: -- Function: "proc_UpdateItemName"() -- DROP FUNCTION "proc_UpdateItemName"(); CREATE OR REPLACE FUNCTION "proc_UpdateItemName"() RETURNS void AS $BODY$UPDATE "Item" SET "ItemName" = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; ALTER FUNCTION "proc_UpdateItemName"() OWNER TO postgres; Mikko Partio wrote: > > > novnov wrote: >>> Thanks to both of you for responding. I should have included the code >>> for >>> my own attempt, at #1 which is just as you suggest: >>> >>> update item set itemname = 'fox'; >>> >>> I've tried single, and double quoting the table and field names; call >>> caps >>> to the UPDATE etc, exactly matching the capitalization of the table and >>> field names (really Item and ItemName). >>> >>> I wonder if "Item" is a reserved word in pgsql? >>> >>> > > I think you haven't quoted the field names correctly. > > > dun=# CREATE TABLE "Item" (id int4, "ItemName" text); > CREATE TABLE > dun=# INSERT INTO "Item" VALUES(1,'aaa'); > INSERT 0 1 > dun=# UPDATE "Item" SET "ItemName" = 'fox'; > UPDATE 1 > dun=# SELECT * FROM "Item"; > id | ItemName > ----+---------- > 1 | fox > (1 row) > > > If you want to have case-sensitive names, you have to have double quotes. > > > MP > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7176256 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
