BUG #5035: cast 'text' to 'name' doesnt work in plpgsql function
The following bug has been logged online:
Bug reference: 5035
Logged by:
Email address: tkarlik@ultimo.pl
PostgreSQL version: 8.3.6
Operating system: Linux
Description: cast 'text' to 'name' doesnt work in plpgsql function
Details:
Comparing 'text' to 'name' in plpgsl function needs explicit casting to
name:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
test_db=# select table_exists('test_table');
table_exists
--------------
(1 row)
Time: 0,561 ms
test_db=# select 1 from pg_class where relname = 'test_table';
?column?
----------
(0 rows)
Time: 0,337 ms
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
test_db=# select table_exists('test_table');
table_exists
--------------
(1 row)
Time: 15,022 ms
Database contains more than 20 000 pg_class tuples.
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
If you're looking for a speedy answer, try a SQL function, not plpgsql.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
If you're looking for a speedy answer, try a SQL function, not plpgsql.
He's still going to need the cast to name. It's not a bug, it's just
how things work: the indexes on pg_class support name = name equality
tests, not text = text.
regards, tom lane
Alvaro Herrera <alvherre@commandprompt.com> 9/4/2009 7:23 PM >>>
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
If you're looking for a speedy answer, try a SQL function, not plpgsql.
The same issue when using SQL function... However other casting (for example int4->int8) works properly.
Hello
2009/9/7 Tomasz Karlik <Tomasz.Karlik@ultimo.pl>:
Alvaro Herrera <alvherre@commandprompt.com> 9/4/2009 7:23 PM >>>
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
it some strange. What version do you use?
on 5.4
postgres=# explain select * from pg_class where relname='aaa';
QUERY PLAN
--------------------------------------------------------------------------------
-------------
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=
1 width=185)
Index Cond: (relname = 'aaa'::name)
(2 rows)
the casting is implicit.
regards
Pavel Stehule
Show quoted text
If you're looking for a speedy answer, try a SQL function, not plpgsql.
The same issue when using SQL function... However other casting (for example
int4->int8) works properly.
2009/9/7 Pavel Stehule <pavel.stehule@gmail.com>:
Hello
2009/9/7 Tomasz Karlik <Tomasz.Karlik@ultimo.pl>:
Alvaro Herrera <alvherre@commandprompt.com> 9/4/2009 7:23 PM >>>
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;it some strange. What version do you use?
on 5.4
sorry 8.4
Show quoted text
postgres=# explain select * from pg_class where relname='aaa';
QUERY PLAN--------------------------------------------------------------------------------
-------------
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=
1 width=185)
Index Cond: (relname = 'aaa'::name)
(2 rows)the casting is implicit.
regards
Pavel StehuleIf you're looking for a speedy answer, try a SQL function, not plpgsql.
The same issue when using SQL function... However other casting (for example
int4->int8) works properly.
Pavel Stehule <pavel.stehule@gmail.com> 9/7/2009 3:47 PM >>>
Hello
2009/9/7 Tomasz Karlik <Tomasz.Karlik@ultimo.pl>:
Alvaro Herrera <alvherre@commandprompt.com> 9/4/2009 7:23 PM >>>
tkarlik@ultimo.pl wrote:
Without casting function executes much slower:
CREATE OR REPLACE FUNCTION table_exists(tblname text) RETURNS boolean AS '
DECLARE
exists boolean;
BEGIN
SELECT 1 INTO exists FROM pg_class WHERE relname = name($1);
RETURN exists;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;
it some strange. What version do you use?
on 5.4
postgres=# explain select * from pg_class where relname='aaa';
QUERY PLAN
--------------------------------------------------------------------------------
-------------
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=
1 width=185)
Index Cond: (relname = 'aaa'::name)
(2 rows)
the casting is implicit.
It does'nt work only inside function. Look for execution times in my first post. Maybe the planner treats SQL SELECT query other than procedural SELECT INTO?