Re: BUG #5035: cast 'text' to 'name' doesnt work in plpgsqlfunction

The following bug has been logged online:

Bug reference: 5035
Logged by:
Email address: tkarlik(at)ultimo(dot)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(at)ultimo(dot)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(at)commandprompt(dot)com> writes:
> tkarlik(at)ultimo(dot)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(at)commandprompt(dot)com> 9/4/2009 7:23 PM >>>
tkarlik(at)ultimo(dot)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(dot)Karlik(at)ultimo(dot)pl>:
>
>
>>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 9/4/2009 7:23 PM >>>
> tkarlik(at)ultimo(dot)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

> 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(dot)stehule(at)gmail(dot)com>:
> Hello
>
> 2009/9/7 Tomasz Karlik <Tomasz(dot)Karlik(at)ultimo(dot)pl>:
>>
>>
>>>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 9/4/2009 7:23 PM >>>
>> tkarlik(at)ultimo(dot)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

>
> 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
>
>> 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.
>>
>

>>> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> 9/7/2009 3:47 PM >>>
Hello

2009/9/7 Tomasz Karlik <Tomasz(dot)Karlik(at)ultimo(dot)pl>:
>
>
>>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> 9/4/2009 7:23 PM >>>
> tkarlik(at)ultimo(dot)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?