On Fri, 3 Nov 2006, novnov 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
I think something like:
sszabo=# create table "Item"("ItemID" int4, "ItemName" varchar);
CREATE TABLE
sszabo=# create function f1() returns void as $$update "Item" set
"ItemName"='fox';$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f2() returns void as $$update "Item" set
"ItemName"='fox' where "ItemID"=2;$$ language 'sql';
CREATE FUNCTION
sszabo=# create function f3(varchar) returns void as $$update "Item" set
"ItemName"=$1 where "ItemID"=3;$$ language 'sql';
CREATE FUNCTION
sszabo=# insert into "Item" values (1, 'aaa');
INSERT 0 1
sszabo=# insert into "Item" values (2, 'bbb');
INSERT 0 1
sszabo=# insert into "Item" values (3, 'ccc');
INSERT 0 1
sszabo=# select * from "Item";
ItemID | ItemName
--------+----------
1 | aaa
2 | bbb
3 | ccc
(3 rows)
sszabo=# select f2();
f2
----
(1 row)
sszabo=# select * from "Item";
ItemID | ItemName
--------+----------
1 | aaa
3 | ccc
2 | fox
(3 rows)
sszabo=# select f1()
sszabo-# ;
f1
----
(1 row)
sszabo=# select * from "Item";
ItemID | ItemName
--------+----------
1 | fox
3 | fox
2 | fox
(3 rows)
sszabo=# select f3('monkey');
f3
----
(1 row)
sszabo=# select * from "Item";
ItemID | ItemName
--------+----------
1 | fox
2 | fox
3 | monkey
(3 rows)
As a note, you'll probably pretty quickly move into things for which SQL
isn't a good fit, so you might want to look at plpgsql as well.
> 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.
The names were probably doublequoted when created (possibly automatically
by your creating client application) which means you'd need to double
quote them on use as well. Regular (unquoted) identifiers are case-folded,
so "Item" != Item.