Thread: Where is the error

Hi
  I have the following function:

CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';

When I try to use it:

select int('t'::bool);
ERROR:  syntax error at or near "(" at character 11

I am using PG8.0b1

Thank you.

      Kaloyan

On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:

> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
> select CASE WHEN $1 THEN 1
> ELSE 0
> END
> 'LANGUAGE 'sql';
>

The problem is that you have quoted the function name as "int". You
should only do this when you want to force the exact case of the name.
When you do it this way, you have to use double quotes when you refer
to the identifier:


select "int"('t'::boolean);
  int
-----
    1
(1 row)


So in this case you probably want to call your function int -- without
quotes. By default, PostgreSQL folds the names to lowercase.


Best,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Kaloyan Iliev Iliev napisał(a):
> Hi
>  I have the following function:
>
> select int('t'::bool);
> ERROR:  syntax error at or near "(" at character 11
>
"int" is reserved for integer type, so you should not use it. However if
you add
namespace before function name "public.int(TRUE)" then it should work.

ML

10x John,

You are right.The problem is that the function was dumped with quotes
from pg_dump but I suppose the one who created it used quotes and that's
why it dumps in this way.
I will fix this in the dump.

Thanks again.

     Kaloyan

John DeSoi wrote:

>
> On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:
>
>> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
>> select CASE WHEN $1 THEN 1
>> ELSE 0
>> END
>> 'LANGUAGE 'sql';
>>
>
> The problem is that you have quoted the function name as "int". You
> should only do this when you want to force the exact case of the name.
> When you do it this way, you have to use double quotes when you refer
> to the identifier:
>
>
> select "int"('t'::boolean);
>  int
> -----
>    1
> (1 row)
>
>
> So in this case you probably want to call your function int -- without
> quotes. By default, PostgreSQL folds the names to lowercase.
>
>
> Best,
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>

begin  Kaloyan Iliev Iliev <news1@faith.digsys.bg> wrote:
> Hi
>  I have the following function:

> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'

I'm not sure. But 'int' is reserved, possibly is this the error.

CREATE OR REPLACE FUNCTION my_int (boolean) RETURNS integer AS'
select CASE WHEN $1 THEN 1
ELSE 0
END
'LANGUAGE 'sql';

test_db=# select my_int('t'::bool);
 my_int
--------
      1
(1 Zeile)



end
Regards, Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

Hi again,

When I chage in the dump file

CREATE FUNCTION int (boolean) RETURNS integer AS '
select CASE WHEN $1 THEN 1
ELSE 0
END ' LANGUAGE sql;

ERROR:  syntax error at or near "(" at character 21

 I receive this message. So the question is how to create it without quotes.

Thank in advance:)


Kaloyan


John DeSoi wrote:

>
> On Dec 27, 2004, at 6:42 AM, Kaloyan Iliev Iliev wrote:
>
>> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
>> select CASE WHEN $1 THEN 1
>> ELSE 0
>> END
>> 'LANGUAGE 'sql';
>>
>
> The problem is that you have quoted the function name as "int". You
> should only do this when you want to force the exact case of the name.
> When you do it this way, you have to use double quotes when you refer
> to the identifier:
>
>
> select "int"('t'::boolean);
>  int
> -----
>    1
> (1 row)
>
>
> So in this case you probably want to call your function int -- without
> quotes. By default, PostgreSQL folds the names to lowercase.
>
>
> Best,
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>

Thanks To you all.

The problem was that the int was reserved. When I try

CREATE FUNCTION my_int(boolean) RETURNS integer AS '
select CASE WHEN $1 THEN 1
 ELSE 0
END ' LANGUAGE sql;

it worked.

But now I have to change it on many places. This is not good beacause on
the old version of PG (form where I dumped) everithing was OK:)

Kaloyan

Kaloyan Iliev Iliev wrote:

> Hi
>  I have the following function:
>
> CREATE OR REPLACE FUNCTION "int" (boolean) RETURNS integer AS'
> select CASE WHEN $1 THEN 1
> ELSE 0
> END
> 'LANGUAGE 'sql';
>
> When I try to use it:
>
> select int('t'::bool);
> ERROR:  syntax error at or near "(" at character 11
>
> I am using PG8.0b1
>
> Thank you.
>
>      Kaloyan
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>