Re: Simple stored procedure examples? - Mailing list pgsql-general

novnov wrote:
> And here is what shows in the SQL window for that proceedure
>
> CREATE FUNCTION "proc_UpdateItemName"(IN "strItemName" "varchar") RETURNS
> void AS
> $BODY$update item set itemname = 'fox';$BODY$
> LANGUAGE 'sql' VOLATILE;
>
> The error is always "ERROR relation "item" does not exist". Not using the
> param, but it doesn't help to remove it.
>
>
>
> 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?
>>
>>
>>
>> Shoaib Mir wrote:
>>
>>> You can use the following:
>>>
>>>
>>>>> #1 updating ItemName for all rows to 'fox'
>>>>>
>>> update tablename set itemname = 'fox';
>>>
>>>
>>>>> #2 updating ItemName for row where ItemID = 2 to 'fox'
>>>>>
>>> update tablename set itemname = 'fox' where itemid = 2;
>>>
>>>
>>>>> #3 updating ItemName for row where ItemID = 3 to a param value passed in
>>>>>
>>> \set var1 3;
>>> update tablename set itemname = 'testing' where itemid = :var1;
>>>
>>> Hope this helps...
>>>
>>> Thanks,
>>> -------
>>> Shoaib Mir
>>> EnterpriseDB (www.enterprisedb.com)
>>>
>>>
>>> On 11/4/06, novnov <novnovice@gmail.com> wrote:
>>>
>>>> I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding
>>>> a
>>>> lot
>>>> of bare bones simple example stored procs that I can learn from. It
>>>> would
>>>> be
>>>> very helpful if someone could show me some simple code.
>>>>
>>>> In the pgAdmin interface I've been picking SQL as the language, that's
>>>> the
>>>> default it offers.
>>>>
>>>> Say I have a table Item, and fields ItemID (int4) and ItemName
>>>> (varchar).
>>>> What would be the code for
>>>>
>>>> #1 updating ItemName for all rows to 'fox'
>>>>
>>>> #2 updating ItemName for row where ItemID = 2 to 'fox'
>>>>
>>>> #3 updating ItemName for row where ItemID = 3 to a param value passed in
>>>>
>>>> That would be so helpful...I've tried and get errors like "ERROR
>>>> relation
>>>> "item" does not exist", and of course the table Item and the column
>>>> ItemName
>>>> do exist, haven't been able to figure out what the error means.
>>>> --
>>>> View this message in context:
>>>> http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7171726
>>>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>>>
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 5: don't forget to increase your free space map settings
>>>>
>>>>
>>>
>>
>
>

Is the table item spelled with some capital letters?  PostgreSQL  treats
If the table is called Item with a capital I then select * from item
will give you this error same with capitalization of column names and
everything else.
Oisin.