Обсуждение: PROBLEM WITH FUNCTIONS IN POSTGRES

Hi...

i create a function in postgres to return a data of a table but when try
to create say

PostgreSQL ha dicho: ERROR: parser: parse error at or near "text"
Your query:

CREATE FUNCTION sinomemp(text)
RETURNS text
AS 'DECLARE sinomemp TEXT;
BEGIN
SELECT name
FROM simaemp
WHERE cod = $1;
RETURN sinomemp;
END;'
LANGUAGE 'sql'


please help me

* Mario Alberto Soto Cordones <mario_soto@compuall.cl> [20.03.2003 19:09]:
> Hi...
>
> i create a function in postgres to return a data of a table but when try
> to create say
>
> PostgreSQL ha dicho: ERROR: parser: parse error at or near "text"
> Your query:
>
> CREATE FUNCTION sinomemp(text)
> RETURNS text
> AS 'DECLARE sinomemp TEXT;
> BEGIN
> SELECT name
> FROM simaemp
> WHERE cod = $1;
> RETURN sinomemp;
> END;'
> LANGUAGE 'sql'

Actually, this question is for SQL list, not ADMIN.

You have `return' statement in your code, it is part of plpgsql language,
not sql.
So, changing last line to:

LANGUAGE plpgsql;

will solve your problem.

One note: it seems, that sinomemp variable is not in use.

May be this is what you need:

CREATE or replace FUNCTION sinomemp(text) RETURNS text AS '
SELECT name
FROM simaemp
WHERE cod = $1;'

LANGUAGE sql;

--

Victor Yegorov

* Mario Alberto Soto Cordones <mario_soto@compuall.cl> [20.03.2003 20:15]:
> Victor the sintaxis it`s my error sorry.
> now how i to suscribe the list SQL
> and finaly question
> the function what you return me work very good, but return only one field,
> but i want to return two or many field
> how i do
>
> gracias

Well, you can subscribe to any list you wish here:
<http://www.postgresql.org/lists.html>

Select list you wish to subscribe and follow the instructions.


You'd better read yourself about server-side programming in postgres, you
can find everything you need in PostgreSQL Programmers Guide
<http://www.postgresql.org/docs/>. There you'll see Extending SQL: Functions
paragraph (it's numbered as 9th in docs for postgres 7.3.1). You'll find
everything about functions there.


--

Victor Yegorov