Re: Simple stored procedure examples?

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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz