[postgresql] reversed function params

Toad Data Modeler
1

Why reversed params and types are eclosed into double quotes?
for example:
“msgid” “int8”, “reportcontent” “xml”

Then SQL preview shows:
CREATE FUNCTION “some_schema”.“some_function”(“msgid” “int8”, “reportcontent” “xml”)

Even if it is accepted by a server, I think it’s doesn’t match standards.

best regards

2

Hello,

Let me explain.

Names of arguments - they are case sensitive therefore we put them into double quotes (to preserve them as user wrote them).
Types - some types must be in double quotes because they are also case sensitive (user types as domain etc.)
Built in system types - quotes are useless, however database doesn’t mind them.

Regards,

Vladka + Mario

3

*Names of arguments - they are case sensitive therefore we put them into double quotes (to preserve them as user wrote them).
*We are talking about reverse engineering. So there is nothing to preserve of what user wrote. More:

  • if you define function with mixed letter sizes, postgres engine turns it into lowercase.
  • If you quote argument names by doublequotes this variable will be still interpreted as case insensitive.
  • reverse engine may discover if argument is quotet or not

Types - some types must be in double quotes because they are also case sensitive (user types as domain etc.)*

Aren’t postgresql case insensitive? I believe it is.

Built in system types - quotes are useless, however database doesn’t mind them.

But developer does. It makes crazy to remove all those double quotes from reversed code since no one writes “int” or “varchar” daily.

If there is realy serious reason to do things this way, I suggest to add option to disable/enable this feature.

Best regards

4

Hello,

Let me reply.
PostgreSQL is case sensitive (at least it behaves this way on our machines). Therefore the quotation marks are important. For example, these two domains will be created as two different domains - although there is only one different character (lower case versus upper case):

create domain “domainname” as int
/
create domain “Domainname” as int
/

Also, there is a difference whether type “domainname” or type “Domainname” is used in function.

Case sensitivity proves also in names of arguments. There will be two different arguments if you write “arg” or “ARG”. Example:
CREATE FUNCTION mysfunc(“arg” integer, “ARG” integer) RETURNS integer AS $$
BEGIN
RETURN “arg” + “ARG”;
END;
$$ LANGUAGE plpgsql
/

If we reversed this without the quotation marks in argument names, it would be incorrect.

What we agree with is that the quotation marks in built in system types such as “int”, “varchar” etc. are useless. We will look into it and will try to solve it for next version. Thanks for your patience.

Regards,

Vladka + Mario

5

Again, after postgresql manual (http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS)

Identifier and key word names are case insensitive

and

Quoting an identifier (…) makes it case-sensitive

in practice postgres stores SPs’ parameters in way (into pg_proc.proargnames) :

Parameter will be saved as parameter
“Parameter” will be saved as Parameter

During RE it is enough to check if parameter names contain uppercase letters (and/or other characters not allowed without quotes). If positive - add doublequotes. Otherwise don’t.

Your example should be reversed as:

CREATE FUNCTION mysfunc(arg integer, “ARG” integer) RETURNS integer AS$$

function body will remain untouched which is correct.

Such scenario is udedby pgAdmin for example.
I believe this technique may be used in other cases, ie for domains.

PS. I don’t know any dev who forces character case using quotes. Mixing styles of arguments (default and quoted) is wrong technique and may result problems while developing and debuging.

Best regards

6

There went 1 week after my the last post.
no response?

7

Hello Michal,

As I already wrote, we will definitely consider the change in quotes for RE.
Thanks for your patience.

Regards,

Vladka + TDM Team

8

thanx