Re: [HACKERS] Regclass and quoted table names
Tom Lane wrote: Emmanuel Cecchet m...@asterdata.com writes: It looks like the behavior of regclass is not consistent when table names are quoted. The name is returned without the quotes if the name is lower case with eventual trailing numbers, otherwise it is returned with quotes. It's intentional that it quotes only when needed. This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? Thanks, Emmanuel -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet m...@asterdata.com wrote: This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? regclass's output format is intended for human readers, not for joining against text columns. Why would you need to be joining between regclass and text anyways? Normally you would be joining regclass to either regclass columns or oid columns where it does an integer comparison. Actually normally you would just be joining oid columns since most catalog columns are declared to be oids rather than regfoo columns :( -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
Greg Stark st...@enterprisedb.com writes: On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet m...@asterdata.com wrote: This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? regclass's output format is intended for human readers, not for joining against text columns. Why would you need to be joining between regclass and text anyways? The quoting behavior is the *least* of your problems if you're trying to do that. Are you aware of how it works vis-a-vis search_path? Have you thought about what will happen when you rename a table? Use plain oids or regclass values, not a text column, if you are trying to store table identities. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
marcin mank wrote: Use plain oids or regclass values, not a text column, if you are trying to store table identities. wouldn`t oids change on dump/reload? I don't know. I'd also be interested to know if there is a difference if we use pg_restore with a binary format or sql dump, or if that does not influence at all the way oids are created. manu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
Use plain oids or regclass values, not a text column, if you are trying to store table identities. wouldn`t oids change on dump/reload? Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Regclass and quoted table names
Hi all, It looks like the behavior of regclass is not consistent when table names are quoted. The name is returned without the quotes if the name is lower case with eventual trailing numbers, otherwise it is returned with quotes. See some examples here: tpch=# CREATE VIEW test AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW test1 AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW 'test2' AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW testcolumnVIEW AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW testcolumnview AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW testcolumnVIEW2 AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW 1test AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW 1test2abc AS SELECT * FROM customer; CREATE VIEW tpch=# CREATE VIEW 1test2 AS SELECT * FROM customer; CREATE VIEW tpch=# select c.oid , c.oid::regclass from pg_class c where c.relname like '%test%'; oid | oid ---+-- 16410 | test 16413 | test1 16416 | 'test2' 16419 | testcolumnVIEW 16422 | testcolumnview 16425 | testcolumnview2 16428 | 1test2abc 16431 | 1test2 16434 | 1test (9 rows) Is this a bug? manu -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
Emmanuel Cecchet m...@asterdata.com writes: It looks like the behavior of regclass is not consistent when table names are quoted. The name is returned without the quotes if the name is lower case with eventual trailing numbers, otherwise it is returned with quotes. It's intentional that it quotes only when needed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers