Thread: function error

Hi list,

I've made a small function but it is refused:

[blurb snipped]
DROP FUNCTION IF EXISTS get_user_oid(TEXT);
CREATE FUNCTION get_user_oid(TEXT)  -- 1- Owner's name
RETURNS OID AS $$
    SELECT usesysid AS useroid FROM pg_user WHERE usename = $1;
END;
$$ LANGUAGE sql;
REVOKE ALL ON FUNCTION get_user_oid(TEXT) FROM public;

Here's the result:

This one I understand:
=# \i ../DB_PROCS/001_GET_USER_OID_2.sql
psql:../DB_PROCS/001_GET_USER_OID_2.sql:17: NOTICE:  function get_user_oid(text) does not exist, skipping
DROP FUNCTION

This one not:
psql:../DB_PROCS/001_GET_USER_OID_2.sql:30: ERROR:  return type mismatch in function declared to return oid
DÉTAIL : Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXTE : SQL function "get_user_oid"
psql:../DB_PROCS/001_GET_USER_OID_2.sql:34: ERROR:  function get_user_oid(text) does not exist

I succeeded with a former one, but it is plpgsl and needs a more complicated
call: SELECT * FROM get_user_oid('myusr') AS z(ooid oid); I'd prefer to
have a simpler call if possible, such as: SELECT user_oid('myusr');

JY
--
Why do seagulls live near the sea?  'Cause if they lived near the bay,
they'd be called baygulls.

"Jean-Yves F. Barbier" <12ukwn@gmail.com> writes:
> I've made a small function but it is refused:

> CREATE FUNCTION get_user_oid(TEXT)  -- 1- Owner's name
> RETURNS OID AS $$
>     SELECT usesysid AS useroid FROM pg_user WHERE usename = $1;
> END;
> $$ LANGUAGE sql;

You don't want the END; there (that's plpgsql syntax not SQL).

            regards, tom lane