Skip site navigation (1) Skip section navigation (2)

function error

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: function error
Date: 2011-11-05 23:19:52
Message-ID: 20111106001952.36e955ee@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
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.

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-11-05 23:31:13
Subject: Re: function error
Previous:From: Russell ChristopherDate: 2011-11-05 17:55:48
Subject: Copy rows returned from a view into a table in a different db

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group