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!
