Обсуждение: CREATE FUNCTION

Hello all, sorry about beginner question, but I'm sure function has
correct structure, buw PostgreSQL reports error. (This function is
only on approbation.)

CREATE FUNCTION foo(int2)
RETURNS TEXT
AS 'DECLARE ret TEXT;
   begin
      SELECT INTO ret CAST(name AS text)
      FROM shop_goods
      WHERE id = $1;
      return ret;
   end;'
language 'sql';

PostgreSQL still returns:

ERROR:  syntax error at or near "TEXT" at character 13

PostgreSQL version:

jirka@debian:/root$ /usr/local/postgresql/bin/postmaster --version
postmaster (PostgreSQL) 7.4.2

I have tried function from PostgreSQL book but with same results.
Thank you for replies.

--
Jiri Nemec
www.menea.cz - web solutions

On Sun, 8 Aug 2004, Jiri Nemec wrote:

> Hello all, sorry about beginner question, but I'm sure function has
> correct structure, buw PostgreSQL reports error. (This function is
> only on approbation.)
>
> CREATE FUNCTION foo(int2)
> RETURNS TEXT
> AS 'DECLARE ret TEXT;
>    begin
>       SELECT INTO ret CAST(name AS text)
>       FROM shop_goods
>       WHERE id = $1;
>       return ret;
>    end;'
> language 'sql';

I think you meant language 'plpgsql' rather than 'sql' since the above
looks like the former.

[ CC'd to "Jiri Nemec" <konference@menea.cz>]

"Jiri Nemec" <konference@menea.cz> said:


> Hello all, sorry about beginner question, but I'm sure function has
> correct structure, buw PostgreSQL reports error. (This function is
> only on approbation.)
>
> CREATE FUNCTION foo(int2)
> RETURNS TEXT
> AS 'DECLARE ret TEXT;
>    begin
>       SELECT INTO ret CAST(name AS text)
>       FROM shop_goods
>       WHERE id = $1;
>       return ret;
>    end;'
> language 'sql';

should this not be language 'plpgsql' ?

or

CREATE FUNCTION foo(int2)
RETURNS TEXT AS '
  SELECT CAST(name AS text)
       FROM shop_goods
       WHERE id = $1;
' language 'sql';


mind you, i could be wrong.

gnari

Jiri Nemec <konference@menea.cz> writes:
> CREATE FUNCTION foo(int2)
> RETURNS TEXT
> AS 'DECLARE ret TEXT;
>    begin
>       SELECT INTO ret CAST(name AS text)
>       FROM shop_goods
>       WHERE id = $1;
>       return ret;
>    end;'
> language 'sql';

This is a plpgsql function, not a sql function --- you are selecting
the wrong language.

            regards, tom lane