Обсуждение: 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 =3D name($1);
>     RETURN exists;
> END;
> ' LANGUAGE 'plpgsql' IMMUTABLE;

If you're looking for a speedy answer, try a SQL function, not plpgsql.
=20


The same issue when using SQL function... However other casting (for exampl=
e 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
>>=C2=A0=C2=A0=C2=A0=C2=A0 SELECT 1 INTO exists FROM pg_class WHERE relname=
 =3D name($1);
>>=C2=A0=C2=A0=C2=A0=C2=A0 RETURN exists;
>> END;
>> ' LANGUAGE 'plpgsql' IMMUTABLE;
>

it some strange. What version do you use?

on 5.4

postgres=3D# explain select * from pg_class where relname=3D'aaa';
                                         QUERY PLAN

---------------------------------------------------------------------------=
-----
-------------
 Index Scan using pg_class_relname_nsp_index on pg_class  (cost=3D0.00..8.2=
7 rows=3D
1 width=3D185)
   Index Cond: (relname =3D '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 exam=
ple
> 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 A=
S '
>>> DECLARE
>>> exists boolean;
>>> BEGIN
>>>=C2=A0=C2=A0=C2=A0=C2=A0 SELECT 1 INTO exists FROM pg_class WHERE relnam=
e =3D name($1);
>>>=C2=A0=C2=A0=C2=A0=C2=A0 RETURN exists;
>>> END;
>>> ' LANGUAGE 'plpgsql' IMMUTABLE;
>>
>
> it some strange. What version do you use?
>
> on 5.4

sorry 8.4


>
> postgres=3D# explain select * from pg_class where relname=3D'aaa';
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PL=
AN
>
> -------------------------------------------------------------------------=
-------
> -------------
> =C2=A0Index Scan using pg_class_relname_nsp_index on pg_class =C2=A0(cost=
=3D0.00..8.27 rows=3D
> 1 width=3D185)
> =C2=A0 Index Cond: (relname =3D '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 exa=
mple
>> 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 =3D name($1);
>>     RETURN exists;
>> END;
>> ' LANGUAGE 'plpgsql' IMMUTABLE;
>

it some strange. What version do you use?

on 5.4

postgres=3D# explain select * from pg_class where relname=3D'aaa';
                                         QUERY PLAN

---------------------------------------------------------------------------=
-----
-------------
Index Scan using pg_class_relname_nsp_index on pg_class  (cost=3D0.00..8.27=
 rows=3D
1 width=3D185)
   Index Cond: (relname =3D 'aaa'::name)
(2 rows)

the casting is implicit.

=20
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 SELEC=
T INTO?