Thread: Simple stored procedure examples?

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.

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 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.

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
>>
>
>

--
View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7175841
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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
>>>
>>
>>
>
>

--
View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7175869
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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.

  > 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

On Sat, 4 Nov 2006, 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 used an "Item" table as well, and I can't think of anything between 8.1
and 8.2 beta that would make a difference. If the admin tool quoted the
name (which it probably did given the function declaration you showed
next), then you have to both double quote and match the capitalization in
order to reference the table (none of Item, item or "item" will match a
table created as "Item").

If you pull out the create functions and calls from my earlier example, do
you get the same failures as with your attempt?

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.

On Sat, Nov 04, 2006 at 08:35:28AM -0800, novnov wrote:
> So pgSQL is case sensitive and that include keywords like UPDATE and SET.

No it's not. Only identifiers in double quotes (") are case-sensetive.

So, in your example below, because the function was created with double
quotes, you now have to use double quotes and the same case every time
you want to use it. If you create a function/table/column without
double quotes, you never need quotes and it is case-insensetive.

In your case it's possible that pgAdmin is adding the quotes for you,
maybe?

> 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;
>

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hmm well that's interesting. I had posted to the pgAdmin list too re this
issue, thinking that the quotes issues was something with that interface to
pgsql. There I was told

"PostgreSQL does require you to use double quotes in some circumstances
(for example, if you use upper case letters). pgAdmin automatically
quotes identifiers if you use a name that requires them."

Isn't that somewhat of a contradiction of what you wrote? Note, the only
reason the function uses double quotes is because the table def does, or at
least that's how I interpret.

I would really prefer it if simple names like Item and ItemName not be
double quoted. You're saying that postgres itself would only require double
quotes if the table was originally decribed that way (and it is, being
created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin,
why would pgAdmin take the seemingly uneccessary step of double quoting
names like Item?

Any suggestions for a db admin tool that does not introduce this error?



Martijn van Oosterhout wrote:
>
> On Sat, Nov 04, 2006 at 08:35:28AM -0800, novnov wrote:
>> So pgSQL is case sensitive and that include keywords like UPDATE and SET.
>
> No it's not. Only identifiers in double quotes (") are case-sensetive.
>
> So, in your example below, because the function was created with double
> quotes, you now have to use double quotes and the same case every time
> you want to use it. If you create a function/table/column without
> double quotes, you never need quotes and it is case-insensetive.
>
> In your case it's possible that pgAdmin is adding the quotes for you,
> maybe?
>
>> 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;
>>
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
>> From each according to his ability. To each according to his ability to
>> litigate.
>
>
>

--
View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7186929
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Sun, Nov 05, 2006 at 08:51:52AM -0800, novnov wrote:
> I would really prefer it if simple names like Item and ItemName not be
> double quoted. You're saying that postgres itself would only require double
> quotes if the table was originally decribed that way (and it is, being
> created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin,
> why would pgAdmin take the seemingly uneccessary step of double quoting
> names like Item?

Evidently, pgAdmin sees you using uppercase letters and decides to
quote them. It could also not quote them, then it would be case
insensetive.

If you create the table with lowercase, does pgadmin allow you to refer
to them with mixed case? I don't use pgAdmin, so I can't really say
much about this.

> Any suggestions for a db admin tool that does not introduce this error?

I don't use pgAdmin, the only tool I use is psql, and it doesn't
automatically quote anything, ever.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

novnov wrote:

> I would really prefer it if simple names like Item and ItemName not be
> double quoted. You're saying that postgres itself would only require double
> quotes if the table was originally decribed that way (and it is, being
> created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin,
> why would pgAdmin take the seemingly uneccessary step of double quoting
> names like Item?
>
 From the manual -
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html


4.1.1. Identifiers and Key Words

Tokens such as SELECT, UPDATE, or VALUES in the example above are
examples of key words, that is, words that have a fixed meaning in the
SQL language. The tokens MY_TABLE and A are examples of identifiers.
They identify names of tables, columns, or other database objects,
depending on the command they are used in. Therefore they are sometimes
simply called "names".

<snip>

Identifier and key word names are case insensitive. Therefore

UPDATE MY_TABLE SET A = 5;
can equivalently be written as

uPDaTE my_TabLE SeT a = 5;
A convention often used is to write key words in upper case and names in
lower case, e.g.,

UPDATE my_table SET a = 5;

There is a second kind of identifier: the delimited identifier or quoted
identifier. It is formed by enclosing an arbitrary sequence of
characters in double-quotes (").

<snip>

Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers FOO,
foo, and "foo" are considered the same by PostgreSQL, but "Foo" and
"FOO" are different from these three and each other.



Being a GUI tool that generates the underlying SQL statements you will
find that pgAdmin is generating SQL to conform to the above.

But when you enter an SQL statement yourself you will need to use quotes
to maintain any uppercase characters in the names. This may catch you in
pgAdmin as when creating a function you are actually entering an SQL
statement that is used as part of the Function definition.


You can test this by opening the new table dialog and entering the name
as test then look at the SQL tab and you will have

CREATE TABLE test
(
) WITHOUT OIDS;

The change the name to Test and in the SQL tab you will have

CREATE TABLE "Test"
(
) WITHOUT OIDS;


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz