CREATE FUNCTION

[Jiri Nemec]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[gnari]

[ CC'd to "Jiri Nemec" <ko********@men ea.cz>]

"Jiri Nemec" <ko********@men ea.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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Tom Lane]

Jiri Nemec <ko********@men ea.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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)