function refused - Mailing list pgsql-novice

Hi list,

I try to put a query that works on command line into a sql function
but I get this:

=# \i /OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql
psql:/OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql:46: ERROR:  return type mismatch in function declared to return
record
DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXT:  SQL function "e_sch_tab_col"
psql:/OTHER/PRJ_COMGEST/DB_PROCS/004_E_SCH_TAB_COL.sql:48: ERROR:  function e_sch_tab_col(text, text, text) does not
exist

********************** Fn:

CREATE OR REPLACE FUNCTION e_sch_tab_col(TEXT,  -- 1: Owner
                                         TEXT,  -- 2: Schema
                                         TEXT)  -- 3: Table
RETURNS SETOF RECORD AS $$
    SELECT C.relname, A.attname FROM pg_class C
        INNER JOIN pg_user U ON C.relowner = U.usesysid
        INNER JOIN pg_attribute A ON A.attrelid = C.relfilenode
        WHERE C.relname !~ '^(pg_|sql_)' AND C.relkind = 'r' AND A.attnum > 0
        AND C.relowner = (SELECT e_usr_oid(''||$1||''))
        AND C.relnamespace = (SELECT schoid FROM (SELECT * FROM e_sch()
                              AS z(schoid OID, schname NAME)
                              WHERE schname = ''||$2||'') AS schemaoid)
        AND C.relname = ''||$3||''
        ORDER BY A.attrelid, A.attnum;
END;
$$ LANGUAGE sql STRICT SECURITY DEFINER STABLE;
--=============================================================================
REVOKE ALL ON FUNCTION e_sch_tab_col(TEXT, TEXT, TEXT) FROM public;

********************** Command Line:

=# SELECT C.relname, A.attname FROM pg_class C
        INNER JOIN pg_user U ON C.relowner = U.usesysid
        INNER JOIN pg_attribute A ON A.attrelid = C.relfilenode
        WHERE C.relname !~ '^(pg_|sql_)' AND C.relkind = 'r' AND A.attnum > 0
        AND C.relowner = (SELECT e_usr_oid('dbowner'))
        AND C.relnamespace = (SELECT schoid FROM (SELECT * FROM e_sch()
                              AS z(schoid OID, schname NAME)
                              WHERE schname = 'common') AS schemaoid)
        AND C.relname = 'town'
        ORDER BY A.attrelid, A.attnum;
 relname |   attname
---------+--------------
 town    | id
 town    | label
 town    | date_cre
 town    | date_mod
 town    | ri_users_cre
 town    | ri_users_mod
(6 rows)

I understand RETURNS SETOF RECORD AS isn't accepted, although function
seems to issue a record (?); and I've almost the same function that works
with this kind of RETURNS.
You know what? SQL is sometimes hard to understand!

JY
--
Big M, Little M, many mumbling mice
Are making midnight music in the moonlight,
Mighty nice!