Re: Simple stored procedure examples?

From: novnov <novnovice(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Simple stored procedure examples?
Date: 2006-11-05 16:51:52
Message-ID: 7186929.post@talk.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)svana(dot)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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-11-05 19:19:57 Re: Simple stored procedure examples?
Previous Message Alexander Staubo 2006-11-05 14:38:03 Re: varchar