Re: Simple stored procedure examples?

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.