Re: [GENERAL] case dumbiness in return from functions
Tom Lane ha scritto: Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called REF why should Postgres call it ref in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. As for portability: it may not be a concern when you have at disposal the best DB around :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] case dumbiness in return from functions
Nico Sabbi wrote: Tom Lane ha scritto: Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called REF why should Postgres call it ref in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. Why should it PostgreSQL make the effort ? Tom was _very_ clear in the SQL standard. Oracle and Informix also ignore your capitals, although they behave slightly differently in forcing things to upper or lower case. In Informix: create table FOO (FooId SERIAL PRIMARY KEY); INFO - foo: Columns Indexes Privileges References Status ... Display column names and data types for a table. --- [EMAIL PROTECTED] -- Press CTRL-W for Help Column name TypeNulls fooidserial no Bottom line: well documented in all databases I have seen. Not very important -- if you really care (why on earth would you?) then double quote things like table and column names. I would _far_ rather have developers work on resolving subtle issues, or adding *useful* features than this sort of basura. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] case dumbiness in return from functions
On Fri, 18 Jan 2008 13:14:33 +0100 Nico Sabbi [EMAIL PROTECTED] wrote: yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called REF why should Postgres call it ref in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php There are pro and cons. In my dreams I'd like a good Genius to fix all the issues even the one I can barely understand and have a case preserving pg. In reality I'll take more care with quotations and never post after midnight. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] case dumbiness in return from functions
On Jan 18, 2008, at 1:14 PM, Nico Sabbi wrote: Tom Lane ha scritto: The SQL standard specifies that unquoted identifiers are case- insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called REF why should Postgres call it ref in the output of queries if the standard doesn't specify any obligation to convert the name ? If you want to use case sensitive identifiers, then quote them! It's not that hard. In your example above you're doing just that, so your statement does not even apply ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47909a669491882451502! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] case dumbiness in return from functions
Nico Sabbi wrote: yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. It does -- if you quote the names. If I created a field called REF why should Postgres call it ref in the output of queries if the standard doesn't specify any obligation to convert the name ? Actually I think the standard mandates case-folding (though to upper case rather than lower, i.e. the other way around) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] case dumbiness in return from functions
Alvaro Herrera [EMAIL PROTECTED] writes: Actually I think the standard mandates case-folding (though to upper case rather than lower, i.e. the other way around) That's how I read it too. SQL99 5.2 saith 22) The case-normal form of the identifier body of a regular identifier is used for purposes such as and including determination of identifier equivalence, representation in the Definition and Information Schemas, and representation in diagnostics areas. NOTE 44 - Any lower-case letters for which there are no upper- case equivalents are left in their lower-case form. 23) The identifier body of a regular identifier is equivalent to an identifier body in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. In particular this appears to me to specifically require that column names returned by a SELECT be case-normalized, which is what the OP would like us to not do. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] case dumbiness in return from functions
After discovering that pg_get_serial_sequence behaves in a bit
strange way[1] when it deals to case sensitiveness... I just
discovered that you've the same behaviour for any function... at
least in PHP.
postgresql
Versione: 8.1.11
php:
Versione: 5.2.0
eg.
create or replace function testA(out pIpPo int) as
$$
begin
pIpPo:=7;
return;
end;
$$ language plpgsql;
...
$result=pg_query('select pIpPo from testA()');
$row=pg_fetch_array($result);
print(var_export($row));
array ( 'pippo' = '7', )
that makes
$ImSoSad=$row['pIpPo'];
return null
And 2h went trying to understand where a session went lost :(
Fortunately it can be fixed at a small price with an alias... but
still I find it a pain.
Whan you've identifiers composed of more than 2 words, camel case can
make your code lines much shorter.
Please, please, please... fix this. Minor things like this can make
coding in Postgres MUCH more enjoyable.
[1] this is documented... is this aw bw bwaa behaviour of
functions documented as well?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] case dumbiness in return from functions
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] case dumbiness in return from functions
On Thu, 17 Jan 2008 19:07:59 -0500 Tom Lane [EMAIL PROTECTED] wrote: Ivan Sergio Borgonovo [EMAIL PROTECTED] writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. OK... I did get tricked mixing in the same code base (same file) access to MS SQL and pg and I confused a select PiPpO from table in MS SQL and a select PiPpO from function() in pg And I thought that select from table behave differently from select from functions. Having met the admittedly strange behaviour in pg_get_serial_sequence that is not case-insensitive I got confused. All the other select were in fact aliased. I noticed that the argument of case insensitivity vs. case preservation is recurrent and I'm not going to argue about it. I'd prefer to have case preservation but I'd prefer to be able to fly too. I bet there are good reasons for both even if hope is not going to die. Sorry, it was not meant to be disrespectful of the good work you're doing. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
