Thread: first try at postgres functions

Hi,
I'm not sure why this does not work.

create or replace function test1() returns int as '
 return 1;
' LANGUAGE 'plpgsql';

select  test1();

--------------------------------------------------------

I also tried this:

create or replace function test1() returns int as '
 select 1;
' LANGUAGE 'plpgsql';

select  test1();

On Wed, Dec 01, 2004 at 11:03:00PM -0500, Kevin B. wrote:

> I'm not sure why this does not work.
>
> create or replace function test1() returns int as '
>  return 1;
> ' LANGUAGE 'plpgsql';
>
> select  test1();

If something doesn't work then it's helpful to describe what you
expect to happen and what actually does happen.  In this case you
were probably expecting the value 1 to be returned, but you got
the following error instead:

ERROR:  syntax error at or near "1"
CONTEXT:  compile of PL/pgSQL function "test1" near line 1

The function doesn't follow the correct PL/pgSQL structure.  See
the PL/pgSQL documentation, especially the "Structure of PL/pgSQL"
section:

http://www.postgresql.org/docs/7.4/static/plpgsql.html

> I also tried this:
>
> create or replace function test1() returns int as '
>  select 1;
> ' LANGUAGE 'plpgsql';

That's the correct syntax for an SQL (LANGUAGE sql) function but
not for PL/pgSQL.

http://www.postgresql.org/docs/7.4/static/xfunc-sql.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

the correct syntax is

CREATE OR REPLACE FUNCTION test1() RETURNS INTEGER AS '
BEGIN
  RETURN 1;
END; '
LANGUAGE 'plpgsql';

you have to be aware of different things.

- the syntax of the language itself is a little bit like pascal.
- sometimes the errorcodes are not very helpfull.
- be beware of the quotes. you must quote the "code block"  'BEGIN ....
END;'
- you have to double quote strings inside the block.

there are usefull examples in the docs


>
>Hi,
>I'm not sure why this does not work.
>
>create or replace function test1() returns int as '
>  return 1;
>' LANGUAGE 'plpgsql';
>
>select  test1();
>
>--------------------------------------------------------
>
>I also tried this:
>
>create or replace function test1() returns int as '
>  select 1;
>' LANGUAGE 'plpgsql';
>
>select  test1();
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

_________________________________________________________________
Flexibilität in Ihrem Hotmail-Konto - mehr Möglichkeiten für Ihre E-Mails!
http://join.msn.com/?pgmarket=de-at&DI=1031&XAPID=1581